I am using python to get data from an xbee radio. In the payload is the MY address. I want to SELECT from my MySql database, the row which has that MY address. I am having trouble using the string variable from python in the mysql statement. Apparently this is different from normal bash or php.
My code:
...
sa = response['source_addr'].encode("hex")
...
cursor = connection.cursor ()
cursor.execute("""SELECT label from sensors WHERE radio_address= $sa""",)
row = cursor.fetchone()
print row[0]
...
The problem is the MySql syntax where I am using the $sa variable. I have tried several combinations including previous ones that work, but this is the first python use. Even if I put the variable in quotes, no love.
UPDATE: changing the line to
cursor.execute("""SELECT label from sensors WHERE radio_address='%s'""", (sa,))
results in the following response:
Traceback (most recent call last):
File "listen.py", line 34, in <module>
cursor.execute("""SELECT label from sensors WHERE radio_address='%s'""", (sa,))
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.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '7810''' at line 1")
where "7810" is the resolution of the variable and is correct, however there appears to be a python syntax error. I suspect with the versions.
ANSWER:
cursor.execute('SELECT label from sensors WHERE radio_address = %s', (sa,))
Thanks to IVAN for the question relation. The answers for that previous post are the ones I was looking for.