Anthonyfromtheuk
Posts: 17
Joined: Sat Mar 29, 2014 3:28 pm
Location: UK

Temperature sensor and MySQL

Sun Jun 15, 2014 3:57 pm

I have a bit of python code that prints on screen the current temperature detected by a ds18b20 sensor.

Here is the end of the code detecting the temperature from the sensor, that bit works and it prints the current temperature on the screen, but i get an error when trying to insert in to MySQL.

Code: Select all

outputmain= temp[1][-6:-4]
outputdecimel= temp[1][-3]
finaloutput= outputmain+"."+outputdecimel

print finaloutput

db = MySQLdb.connect("host", "username", "password", "tablename")
curs=db.cursor()
curs.execute ("""INSERT INTO tablename values(CURRENT_DATE(), NOW(), 'Place',finaloutput)""")
db.commit()
print "Database Updated."
db.close()
I would like to be able to use the same "variable" finaloutput and insert it in to a mysql database.
But instead it prints the temperature on screen and then i get the error:

Code: Select all

Traceback (most recent call last):
  File "test.py", line 39, in <module>
    curs.execute ("""INSERT INTO tablename values(CURRENT_DATE(), NOW(), 'Place',finaloutput)""")
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1054, "Unknown column 'finaloutput' in 'field list'")
What am i doing wrong here please?


here is the table i am trying to insert in to.

Code: Select all

+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| tdate       | date          | YES  |     | NULL    |       |
| ttime       | time          | YES  |     | NULL    |       |
| zone        | text          | YES  |     | NULL    |       |
| temperature | decimal(10,0) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
Hope that makes sense, Thank you

User avatar
rpdom
Posts: 15212
Joined: Sun May 06, 2012 5:17 am
Location: Chelmsford, Essex, UK

Re: Temperature sensor and MySQL

Sun Jun 15, 2014 4:45 pm

I don't know Python, or how it interfaces to MySQL, but it looks like you are running the MySQL command

Code: Select all

INSERT INTO  tablename values(CURRENT_DATE(), NOW(), 'Place',finaloutput);
where you really wanted to do something like

Code: Select all

INSERT INTO  tablename values(CURRENT_DATE(), NOW(), 'Place',23.3);
You are using the literal "finaloutput" and MySQL is assuming that is the name of a column in the table, which it isn't.

You need to send the contents of the Python variable "finaloutput".

It's probably one of these two lines

Code: Select all

curs.execute ("""INSERT INTO tablename values(CURRENT_DATE(), NOW(), 'Place'," + finaloutput + ")""")
curs.execute ("""INSERT INTO tablename values(CURRENT_DATE(), NOW(), 'Place'," . finaloutput . ")""")
depending on the way python does concatenation.

Anthonyfromtheuk
Posts: 17
Joined: Sat Mar 29, 2014 3:28 pm
Location: UK

Re: Temperature sensor and MySQL

Sun Jun 15, 2014 4:58 pm

I got it working with this:

Code: Select all

curs.execute ("""INSERT INTO tablename values(CURRENT_DATE(), NOW(), 'Place',"%s")""",finaloutput)
I say working, its sort of working it puts the data in the column with Single quotations around it for example '21.6'
--------------
Edit removing the quotations around %s gives me just the temperature 21.6 in the databases column

IanH2
Posts: 79
Joined: Tue Dec 18, 2012 10:17 am

Re: Temperature sensor and MySQL

Mon Jun 16, 2014 12:45 pm

According to the MySQLdb user guide, you need to make the items into a tuple:

Code: Select all

c=db.cursor()
max_price=5
c.execute("""SELECT spam, eggs, sausage FROM breakfast
          WHERE price < %s""", (max_price,))
In your example, something like this:

Code: Select all

curs.execute ("""INSERT INTO tablename values(CURRENT_DATE(), NOW(), 'Place',%s)""",(finaloutput,))
HOWEVER!!!
Please don't ever use Python string interpolation (i.e. "some-string" % (variables) ) to insert parameters into SQL statements. That way lies SQL injection, probably the biggest single cause of web site security failure.

There's some discussion of using MySQLdb and Python variables here: http://stackoverflow.com/questions/7752 ... -variables
-----
https://github.com/IanHarvey

Return to “Python”