Operational error when writing to database in Raspberry Pi's Python

Question

I'm having an operational error when trying to run IDLE program (Python) in Raspberry Pi and it prompt me an error sentences:

Traceback (most recent call last):
 File "/var/www/Sensor-LED.py", line 34, in <module>
   Blink (int(iterations), float(speed))
 File "/var/www/Sensor-LED.py", line 15, in Blink
   curs.execute("INSERT INTO led values(timestamp('now'), (?)")
OperationalError: no such function: timestamp

I use SQLite 3 in Raspberry Pi to create my table and I only created 1 column which is timestamp only.

import sqlite3
import RPi.GPIO as GPIO
import time
import datetime

GPIO.setmode(GPIO.BOARD) ## Use board pin numbering
GPIO.setup(7, GPIO.OUT) ## Setup GPIO Pin 7 to OUT

dbname='/var/www/ledDB.db'

##Define a function named Blink()
def Blink(numTimes, speed):
    conn=sqlite3.connect(dbname)
    curs=conn.cursor()
    curs.execute("INSERT INTO led values(timestamp('now'), (?))")

    for i in range(numTimes):## Run loop numTimes
        print "Iteration " + str(i + 1)## Print current loop
        GPIO.output(7, True)## Switch on pin 7
        time.sleep(speed)## Wait
        GPIO.output(7, False)## Switch off pin 7
        time.sleep(speed)## Wait
        #commit the changes
        conn.commit()
        conn.close()
    print "Done" ## When loop is complete, print "Done"
    GPIO.cleanup()

## Ask user for total number of blinks and length of each blink
iterations = raw_input("Enter total number of times to blink: ")
speed = raw_input("Enter length of each blink(seconds): ")

## Start Blink() function. Convert user input from strings to numeric data types and
##pass to Blink() as parameters
Blink(int(iterations), float(speed))

Hope you guys could help me out with it as soon as possible.

RPiAwesomeness · Accepted Answer · 2014-07-01T12:28:08.327

You've got the SQL command switched up. The (?) should be within the values([HERE]) and the timestamp('now') should be where the (?) is currently:

Wrong:

##Define a function named Blink()
def Blink(numTimes, speed):
    conn=sqlite3.connect(dbname)
    curs=conn.cursor()
    curs.execute("INSERT INTO led values(timestamp('now'), (?))")

Correct:

##Define a function named Blink()
def Blink(numTimes, speed):
    conn=sqlite3.connect(dbname)
    curs=conn.cursor()
    curs.execute("INSERT INTO led values(?)", (timestamp('now'),))

I believe this is what you need. As of right now, I believe your code is attempting to run the timestamp('now') function within SQLite, and I don't believe SQLite has that.

EDIT: My bad, you probably can't execute a command within a Python SQLite command/connection. This should fix it:

Correct

##Define a function named Blink()
def Blink(numTimes, speed):
    conn=sqlite3.connect(dbname)
    curs=conn.cursor()
    timeNow = time.time()
    curs.execute("INSERT INTO led values(?)", (timeNow,))

I simply added the statement timeNow = time.time() and swapped out timestamp('now') for timeNow.

Operational error when writing to database in Raspberry Pi's Python

1 Answers1