Mysql :Query set is not working in python

Question

I am working with python mysql now

I am having a problem Here is mysql query

query = 'SELECT * FROM callerdetail WHERE screenname="1" AND status="0"AND agent ='idd'

WHERE i am getting idd from external source but whenever i am trying to execute this query

i am getting error

  File "server.py", line 28
    query = 'SELECT * FROM callerdetail WHERE screenname="1" AND status="0"AND agent ='idd'
                                                                                         ^
SyntaxError: invalid syntax

Please help me out what i might doing wrong

Martijn Pieters · Accepted Answer · 2012-09-28T13:53:59.187

2

You'll need to use a query parameter:

query = 'SELECT * FROM callerdetail WHERE screenname="1" AND status="0"AND agent =?'

then pass in the value for idd when executing the query:

cursor.execute(query, (idd,))

In python, you cannot simply just put a variable in between various strings and hope it interpolates.

To do regular string interpolation, use the .format() method:

query = 'SELECT * FROM callerdetail WHERE screenname="1" AND status="0"AND agent = "{0}"'.format(idd)

but then you'll miss out on the database query optimizer preparing your statement. Prepared statements are faster to execute if used repeatedly.

edited Sep 28 '12 at 13:53

answered Sep 28 '12 at 13:45

Martijn Pieters

1,048,767
296
4,058
3,343

So for more complicated queries where you have multiple variables you would simply add the variables to the tuple? – b10hazard Sep 28 '12 at 13:59
@raido: I'd still use multiple `?` query parameters, and use those values in the `.execute(query, tuple_of_parameters)` call. – Martijn Pieters Sep 28 '12 at 14:01
Ah I see, so it takes from the tuple in order? So the first ? would take tuple[0] and the second ? would take tuple[1]? – b10hazard Sep 28 '12 at 14:03
See [How to use variables in SQL statement in Python?](http://stackoverflow.com/q/902408) Note that different database adapters use different parameter styles, see http://www.python.org/dev/peps/pep-0249/ – Martijn Pieters Sep 28 '12 at 14:05
And I linked to the documentation for the `.format()` function, yes, `{0}, {1}, {2}` would refer to positional arguments to the `.format()` function for interpolation. – Martijn Pieters Sep 28 '12 at 14:06
Excellent explaination. Thank you! – b10hazard Sep 28 '12 at 14:12

score 0 · Answer 2 · answered Sep 28 '12 at 13:46

0

You are mixing double and single quotes.

instead of...

query = 'SELECT * FROM callerdetail WHERE screenname="1" AND status="0"AND agent = 'idd'

use...

query = 'SELECT * FROM callerdetail WHERE screenname="1" AND status="0"AND agent = "idd"'

Also, is idd a variable? If it is you need to do...

query = 'SELECT * FROM callerdetail WHERE screenname="1" AND status="0"AND agent = "'+str(idd)+'"'

answered Sep 28 '12 at 13:46

b10hazard

7,399
11
40
53

my answer is actually two answers :D I'm not sure if idd is a variable or the actual SQL values for agent so I proposed a second one the converts idd to a string for concatenation. – b10hazard Sep 28 '12 at 13:55

score 0 · Answer 3 · answered Sep 28 '12 at 13:47

0

It seems you forgot to concat the external variable in making of the query,

query = 'SELECT * FROM callerdetail WHERE screenname="1" AND status="0"AND agent = "'+idd+'"'

answered Sep 28 '12 at 13:47

av1987

493
4
12

Mysql :Query set is not working in python

3 Answers3