Python MySQL Query Format

Question

I'm sure I'm making a simple mistake, but I be darned if I can figure it out. I am making a temperature/humidity monitor using a RPi and a DHT22. Code is in python. Works great. I'd like to dump the variable data collected into a MySQL db, but my insert query keeps failing. I can insert straight strings, but can't seem to figure out how to reference the variables. Here is my code

import time
time.sleep(2)
import MySQLdb
temperature = 60.0
humidity = 30.0
IP_Add = "123.456.78.9"
location = "basement"
name = "home"

while True:
    humidity = humidity
    temperature = temperature
    fTemperature = (temperature * 9 / 5) + 32
    name = 'home'
    if humidity is not None and temperature is not None:
        print('Temp={1:0.1f}  Humidity={0:0.1f}%'.format(temperature, humidity))
    else:
        print('Whoops')
    myDB = MySQLdb.connect(host="localhost", port = 3306, user = "root", passwd = "********", db = "PLAYTHING")
    cur = myDB.cursor()
    try:
        #query1 = "INSERT INTO `testdata`(`Name`) VALUES (name)"
        #query2 = "INSERT INTO `testdata`(`Name`, `Location`) VALUES (name, location)"
        #query3 = "INSERT INTO `testdata`(`Name`, `Location`, `T-Reading`)VALUES (%s, %s, %s)", ('No_ID2', 'basement',  30.5)
        query4 = "INSERT INTO `testdata`(`Name`, `Location`, `T-Reading`)VALUES {0} {1} {2}".format ('No_ID2', 'basement',  30.5)
        #query5 = "INSERT INTO testdata(`Name`, `Location`, `T-Reading`, `H-Reading`) VALUES ('Friday3', 'location', 72.5, 29.6)"


    cur.execute(query)
    myDB.commit()
    print ("Commit Sucessful")
except (MySQLdb.Error, MySQLdb.Warning) as e:
    print(e)
cur.close()
myDB.close()

time.sleep(10)

I have checked the MySQLdb docs at https://mysqlclient.readthedocs.io/user_guide.html#functions-and-attributes which offers this as a guide

"""INSERT INTO breakfast (name, spam, eggs, sausage, price)
  VALUES (%s, %s, %s, %s, %s)""",
  [
  ("Spam and Sausage Lover's Plate", 5, 1, 8, 7.95 ),
  ("Not So Much Spam Plate", 3, 2, 0, 3.95 ),
  ("Don't Wany ANY SPAM! Plate", 0, 4, 3, 5.95 )
  ] )

but that seems not to work for me.

Query 1 and 2 execute but enter no data, col's 3 and 4 are NULL. Query 3 gives me this message "TypeError: query() argument 1 must be string or read-only buffer, not tuple" Query 4 enters no data and gives me this: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'No_ID2 basement 30.5' at line 1") Query 5 is successful, but doesn't solve the problem of getting the variables from the program and inserting them into the db.

If someone would point out my error I would appreciate it.

What do you mean exactly by #5 didn't solve the problem? It looks correct, you shouldn't have the quotes on the table name. I've worked with the DHT22 and an RPI doing the same as you, but I used SQLite. — jrel
– jrel, Commented Mar 17, 2018 at 4:16
Your "query3" is correct, except that you have to pass the query string and arguments separately to execute(), for example execute("select * from foo where bar = %s", ("baz, )). "query5" should work as well for static data. "query4" is the way to SQL injection. — Ilja Everilä
– Ilja Everilä, Commented Mar 17, 2018 at 6:02
The guide you've checked is for executemany(), which is for inserting multiple rows at a time. It's a different beast, so the example is not valid for simple execute(). — Ilja Everilä
– Ilja Everilä, Commented Mar 17, 2018 at 6:09
Query 5 does work but as noted by llja , for static data not to retrieve and substitute a value (i.e: temperature == X, so INSERT X). I understand that execute many is a different beast, I was trying to use it as a formatting guide. — Tom Fitzsimons
– Tom Fitzsimons, Commented Mar 18, 2018 at 2:26

Hai Lang · Accepted Answer · 2018-03-18 01:57:21Z

1

Issues with the queries:

#1 and #2: name and location in VALUES (name, location) are considered as column names in database, thus no data.

#3: As Ilja pointed out, the tuple should be in execute() call. This should be the way to go.

        query3 = ("INSERT INTO `testdata`(`Name`, `Location`, `T-Reading`)"
                 + " VALUES (%s, %s, %s)")
        cur.execute(query3, ('No_ID2', 'basement',  30.5))

#4: To put value directly into VALUES, string must be quoted. Below is the right format. Note: This is for experimental only as it pose SQL Injection security risk.

        query4 = ("INSERT INTO `testdata`(`Name`, `Location`, `T-Reading`)"
                 + " VALUES ('{0}', '{1}', {2})".format (
                     'No_ID2', 'basement',  30.5
                 ))

#5: All values in SQL statement are constant.

edited Mar 18, 2018 at 1:57

answered Mar 17, 2018 at 5:50

Hai Lang

5902 silver badges6 bronze badges

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

1 Comment

Ilja Everilä Over a year ago

Never use string formatting or concatenation for passing values to SQL queries. Use your DB-API driver's placeholders. For MySQL drivers it's usually %s, which is not to be mixed with old style string formatting. Pass the values as a sequence in the 2nd argument of execute().

Collectives™ on Stack Overflow

Python MySQL Query Format

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related