CODEWITHSUNDEEP
pythonmysql
Tom Fitzsimons
Tom Fitzsimons

Reputation: 15

Python MySQL Query Format

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.

Upvotes: 0

Views: 3317

Answers (1)

Hai Lang
Hai Lang

Reputation: 590

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.

Upvotes: 1

Related Questions