Python insert SQLite

Question

i have the following problem:

i want to insert the temperature of my RPI using SQLite3 and Python. The python script that i want to use:

import subprocess
import os
import sqlite3 as lite
import datetime
import sys
import time

def get_temperature():
    "Returns the temperature in degrees C"
    try:
        s = subprocess.check_output(["cat","/sys/class/thermal/thermal_zone0/temp"])
        return s[:-1]
    except:
        return 0



try:
    con = lite.connect('/www/auslastung.s3db')
    cur = con.cursor()

    temp = int(get_temperature())
    zeit = time.strftime('%Y-%m-%d %H:%M:%S')

    cur.execute("INSERT INTO 'temperatur' ('Wert', 'Zeit') VALUES (%s, %s)", (temp, zeit))

    con.commit()

except lite.Error, e:

    if con:
       con.rollback()
    print "Error %s" % e.args[0]
    sys.exit(1)

finally:

    if con:
       con.close()

Every time i want to run this, i just get the error:

Error near "%": syntax error

What should i do to solve this?

nwk · Accepted Answer · 2015-01-08 20:25:26Z

2

Replace

cur.execute("INSERT INTO 'temperatur' ('Wert', 'Zeit') VALUES (%s, %s)", (temp, zeit))

with

cur.execute("INSERT INTO 'temperatur' ('Wert', 'Zeit') VALUES (?, ?)", (temp, zeit))

There is also a problem with your finally clause. It will fail with the error NameError: name 'con' is not defined if con is never assigned to in the first place (e.g., if the directory /www/ does not exist, so con = lite.connect('/www/auslastung.s3db') fails). You could do the following to avoid this issue:

con = None
try:
    # ...
except lite.Error, e:
    if con is not None:
       con.rollback()
    # ...
finally:
    if con is not None:
       con.close()

edited Jan 8, 2015 at 20:25

answered Jan 8, 2015 at 20:09

nwk

4,0601 gold badge23 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Joran Beasley Over a year ago

its worth noting this is only in the sqlite connector ... the other ones(mysql,postgres,etc) typically do use %s ... also if the db file does not exists the sqlite library creates it (all the same +1)

nwk Over a year ago

@JoranBeasley You're correct; I'll edit my answer. It will fail if, say, the path to the file does not exist or you can't write to it.

cYnd Over a year ago

Thanks for the fast response! As you can imagine i used this script to insert data in mysql but i moved to sqlite. Now i get the NameError even that i use "is not None"

cYnd Over a year ago

saw you updated answer: the python script is in /var/ and the DB is in /www/ and the name is correct. My assumption is to point at the db this way, or is it wrong?

nwk Over a year ago

@cYnd Where do you get the NameError? Do you assign None to con before the try block?

|

Blackbullet4749 · Accepted Answer · 2015-01-08 20:16:23Z

0

You can also replace:

cur.execute("INSERT INTO 'temperatur' ('Wert', 'Zeit') VALUES (%s, %s)", (temp, zeit))

with

cur.execute("INSERT INTO 'temperatur' ('Wert', 'Zeit') VALUES (%s, %s)" % (temp, zeit))

Either @nwk or my answer should work depending on preference.

answered Jan 8, 2015 at 20:16

Blackbullet4749

12 bronze badges

1 Comment

Joran Beasley Over a year ago

this is not the right way to do it ... the other method protects against sql injection (although I guess i will remove my downvote since in this use case there is no untrusted input) ... but in general you shouldnt do that

Collectives™ on Stack Overflow

Python insert SQLite

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related