Insert values of function into SQLite database

Question

Hej guys,

a friend of mine and me are currently working on a small project we want to realize. Until now we've build a small sensor that recognizes the presence of a car in our garage, running an ultrasonic sensor together with an Arduino Nano. The sensor then transmits an integer (1 = car, 0 = no car) via an Xbee to a RaspberryPi running Python in our living room.

We've also made it possible already to see the data on the screen, giving our lines or 0s or 1s, using this code:

import serial

while True:
    ser = serial.Serial(“/dev/ttyAMA0”, 9600, timeout=1)
    response = ser.read()
    print response

After looking into a bit of Python and SQLite3 code, we decided that it would be really cool, if we would be able to see when a car has arrived in our garage and left. So I did some research and found out that this would could be achieved by having a MySQL or SQLite database.

We tried a bit around and watched a lot of tutorials and we got as far as that we are now able to create tables and insert data into them. Unfortunately this data is manually written by us so far and not dynamically inserted into the table by a function.

Our code regarding the database looks as following:

import sqlite3
import time
import datetime
import serial

def Main():

    try:

        con = sqlite3.connect(‘test.db’)
        cur = con.curson()
        cur.execute(‘CREATE TABLE Cars(Id INT, Time TEXT, Status INT)’)
        cur.execute(“INSERT INTO Cars VALUES(1, datettime(), 1)”)
        cur.execute(“INSERT INTO Cars VALUES(2, datettime(), Reading())”)

        con.commit()

        cur.execute(“SELECT * FROM Cars”)

        data = cur.fetchall()

        for row in data
            print row

    except sqlite3.Error, e:
        if con:
            con.rollback()
            print “There’s a problem with your SQLite”

    finally:
        if con:
            con.close()


def Reading():

    ser = serial.Serial(“/dev/ttyAMA0”, 9600, timeout=1)
    response = ser.read()
    return response

if __name__ == ‘__main__’:
    Main()

In short: Every row of our table should show an individual Id, the current time and either a 1 or a 0, depending if there's a car in front of our sensor in the garage. When we try to run this code, we always get "There's a problem with your SQL" as a message.

Maybe one of you guys has an idea what we did wrong so far or how we're able to implement a value of the function Reading() into the table. And even if not, maybe you have an alternative idea on how to visualize the data.

Answer 1

You want the resulting values of the functions to be inserted into the database. SQLite won't understand the function itself. You should instead do:

    cur.execute('INSERT INTO Cars VALUES(1, ?, 1)',
                (datetime.now().strftime('%Y%m%d%H%M%S'),))
    cur.execute('INSERT INTO Cars VALUES(2, ?, ?)',
                (datetime.now().strftime('%Y%m%d%H%M%S'),Reading()))

If instead of a text value for the time you want to store a Python datetime you can look into using adapters as described here: https://docs.python.org/2/library/sqlite3.html#default-adapters-and-converters