How to use a string in sql execute in python

Question

In my Cur4.execute, I am trying to compare the artist name with the one I obtained before from cur3 (name = row[1]), but I got errors:

Traceback (most recent call last):
  File "mp2.py", line 49, in <module>
    cur4.execute('SELECT * FROM artist WHERE artist.name = %s',name)
  File "C:\python27\lib\site-packages\MySQLdb\cursors.py", line 187, in execute
    query = query % tuple([db.literal(item) for item in args])
TypeError: not all arguments converted during string formatting

How should I fix it?

cur3 = db.cursor()
cur3.execute("SELECT image.link, artist.name, detail.detail_id, FROM artist, detail, image WHERE image.artist_id = artist.artist_id AND detail.image_id = image.image_id LIMIT 1")
ans = cur3.fetchall()
for row in ans:
    print row[1:]   
    name = row[1]
db.close()

cur4 = db.cursor()
cur4.execute('SELECT * FROM artist WHERE artist.name = %s',name)

score 0 · Answer 1 · answered Oct 22 '17 at 02:35

I'm no expert, but perhaps try instead of cur4.execute('SELECT * FROM artist WHERE artist.name = %s',name), surround the string format area with parentheses to get this:

cur4.execute(('SELECT * FROM artist WHERE artist.name = %s',name))

EDIT: Actually, I think mshsayem is right (comment of original question)

mshsayem · Answer 2 · 2017-10-22T03:00:18.973

0

Instead of passing a single value, you need to pass a tuple. So just make the name parameter a tuple by using braces and comma like:

cur4.execute('SELECT * FROM artist WHERE name = %s', (name,))

Or you could this (not recommended):

from MySQLdb import escape_string
cur4.execute("SELECT * FROM artist WHERE name = '%s'" % escape_string(name))

The first form takes care of escaping and the second one does not; that's why you need to explicitly escape the name parameter. The first form also utilizes query optimization while the second one does not.

edited Oct 22 '17 at 03:00

answered Oct 22 '17 at 02:39

mshsayem

17,557
11
61
69

I tried the first way, and I got error as follows: Traceback (most recent call last): File "mp2.py", line 49, in cur4.execute('SELECT * FROM artist WHERE artist.name = %s', (name,)) File "C:\python27\lib\site-packages\MySQLdb\cursors.py", line 205, in execute self.errorhandler(self, exc, value) File "C:\python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue _mysql_exceptions.InterfaceError: (0, '') – Soso Wang Oct 22 '17 at 02:47
Oh I see. You have mistakes in the query too. You do not need `artist.`, just `name` will do. Answer is fixed. Try now. – mshsayem Oct 22 '17 at 03:00

score 0 · Answer 3 · answered Oct 22 '17 at 03:15

Simply run one query. Below returns all artist columns resulting from the one-record of first query. Also, below replaces your implicit joins for explicit joins (the 25-year old standard of ANSI-92).

sql = "SELECT artist.*" + \ 
      " FROM artist" + \
      " INNER JOIN detail ON detail.image_id = image.image_id" + \
      " INNER JOIN image ON image.artist_id = artist.artist_id" + \
      " LIMIT 1"

cur4 = db.cursor()
cur4.execute(sql)

How to use a string in sql execute in python

3 Answers3