MySQL and Python Select Statement Issues
Your execute statement doesn't seem quite correct. My understanding is that it should follow the pattern cursor.execute( <select statement string>, <tuple>)
and by putting only a single value in the tuple location it is actually just a string. To make the second argument the correct data type you need to put a comma in there, so your statement would look like:
cursor.execute("""SELECT
* FROM hashes
WHERE hash=%s""",
(data, ))
Related videos on Youtube
JoshP
Updated on June 19, 2022Comments
-
JoshP almost 2 years
Thanks for taking the time to read this. It's going to be a long post to explain the problem. I haven't been able to find an answer in all the usual sources.
Problem: I am having an issue with using the select statement with python to recall data from a table in a mysql database.
System and versions:
Linux ubuntu 2.6.38-14-generic #58-Ubuntu SMP Tue Mar 27 20:04:55 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux Python: 2.7.1+ MySql: Server version: 5.1.62-0ubuntu0.11.04.1 (Ubuntu)
Here's the table:
mysql> describe hashes; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | varchar(20) | NO | PRI | NULL | | | hash | varbinary(4) | NO | MUL | NULL | | +-------+--------------+------+-----+---------+-------+
Here are responses that I want via a normal mysql query:
mysql> SELECT id FROM hashes WHERE hash='f'; +------+ | id | +------+ | 0x67 | +------+ mysql> SELECT id FROM hashes WHERE hash='ff'; +--------+ | id | +--------+ | 0x6700 | +--------+
As before, these are the responses that are expected and how I designed the DB.
My code:
import mysql.connector from database import login_info import sys db = mysql.connector.Connect(**login_info) cursor = db.cursor() data = 'ff' cursor.execute("""SELECT * FROM hashes WHERE hash=%s""", (data)) rows = cursor.fetchall() print rows for row in rows: print row[0]
This returns the result I expect:
[(u'0x67', 'f')] 0x67
If I change data to : data = 'ff' I receive the following error:
Traceback (most recent call last): File "test.py", line 11, in <module> (data)) File "/usr/local/lib/python2.7/dist-packages/mysql_connector_python-0.3.2_devel- py2.7.egg/mysql/connector/cursor.py", line 310, in execute "Wrong number of arguments during string formatting") mysql.connector.errors.ProgrammingError: Wrong number of arguments during string formatting
OK. So, I add a string formatting character to my SQL statement as so:
cursor.execute("""SELECT * FROM hashes WHERE hash=%s%s""", (data))
And I get the following response:
[(u'0x665aa6', "f'f")] 0x665aa6
and it should by 0x6700.
I know that I should be passing the data with one %s character. That is how I built my database table, using one %s per variable:
cursor.execute(""" INSERT INTO hashes (id, hash) VALUES (%s, %s)""", (k, hash))
Any ideas how to fix this?
Thanks.