Inserting a python variable into a query

Question

I'm trying to pull back a fairly involved query from mysql - I've got the connection working OK, and can successfully complete the query when I hard code the values.

I'd like to make one of the values a python variable - I am following the method I found here: How to use variables in SQL statement in Python? as the listed usecase seems pretty much the same as mine.

The mysql execute function I am using is different to the solved problem, and I can't figure out where to place the python variable into the execute call.

My code:

targetPUID = "fmt/123"

cur.execute("""
(
SELECT `PUID`,`DROID_V`,`SIG_V`,`SPEED`,
COUNT(distinct IF(sourcelist.hasExtension=1,NAME,NULL)) as Ext,
COUNT(distinct IF(sourcelist.hasExtension=0,NAME,NULL)) as NoExt,
COUNT(distinct NAME) as `All`
FROM sourcelist, main_small
WHERE sourcelist.SourcePUID ="%s" AND main_small.NAME = sourcelist.SourceFileName
GROUP BY `PUID`,`DROID_V`,`SIG_V`,`SPEED` ORDER BY `DROID_V` ASC, `SIG_V`
 )
""")

I know I need to place a reference to my variable targetPUIDto link up with the %s in the query.

___________________FIXED___________________

OK, got it working:

 cur.execute("""
 (
 SELECT `PUID`,`DROID_V`,`SIG_V`,`SPEED`,
 COUNT(distinct IF(sourcelist.hasExtension=1,NAME,NULL)) as Ext,
 COUNT(distinct IF(sourcelist.hasExtension=0,NAME,NULL)) as NoExt,
 COUNT(distinct NAME) as `All`
 FROM sourcelist, main_small
 WHERE sourcelist.SourcePUID =%s AND main_small.NAME = sourcelist.SourceFileName
 GROUP BY `PUID`,`DROID_V`,`SIG_V`,`SPEED` ORDER BY `DROID_V` ASC, `SIG_V`
 )
""",targetPUID)

and

 targetPUID = "x-fmt/409"

I don't have enough rep to add the fix yet. Thanks for reading.

enderskill · Accepted Answer · 2012-02-27 06:46:21Z

2

Try:

cur.execute("""
(
SELECT `PUID`,`DROID_V`,`SIG_V`,`SPEED`,
COUNT(distinct IF(sourcelist.hasExtension=1,NAME,NULL)) as Ext,
COUNT(distinct IF(sourcelist.hasExtension=0,NAME,NULL)) as NoExt,
COUNT(distinct NAME) as `All`
FROM sourcelist, main_small
WHERE sourcelist.SourcePUID = %s AND main_small.NAME = sourcelist.SourceFileName
GROUP BY `PUID`,`DROID_V`,`SIG_V`,`SPEED` ORDER BY `DROID_V` ASC, `SIG_V`
 )
""",(int(targetPUID),))

edited Feb 27, 2012 at 6:46

answered Feb 27, 2012 at 5:15

enderskill

7,7243 gold badges26 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jay Gattuso Over a year ago

I did try that, and it returns an empty list - () thanks though.

enderskill Over a year ago

Try replacing %s with the targetPUID value in the query and manually execute it with a SQL application to see if the query works.

Jay Gattuso Over a year ago

Yupe, it works fine when manually implemented via this python method, so I know its the variable aspect thats at fault.

enderskill Over a year ago

If targetPUID is an integer, try converting the variable to an integer when passing it to the execute function or try my revised code again.

Ignacio Vazquez-Abrams · Accepted Answer · 2012-02-27 05:15:15Z

1

cur.execute(..., (targetPUID,))

answered Feb 27, 2012 at 5:15

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

1 Comment

Jay Gattuso Over a year ago

I gave that a go, I just and an empty list back () if I substitute the %s for the sting inside the variable I get full list as expected. Thanks though.

Mariusz Jamro · Accepted Answer · 2012-02-27 16:50:49Z

1

OK, got it working:

 cur.execute("""
 (
 SELECT `PUID`,`DROID_V`,`SIG_V`,`SPEED`,
 COUNT(distinct IF(sourcelist.hasExtension=1,NAME,NULL)) as Ext,
 COUNT(distinct IF(sourcelist.hasExtension=0,NAME,NULL)) as NoExt,
 COUNT(distinct NAME) as `All`
 FROM sourcelist, main_small
 WHERE sourcelist.SourcePUID =%s AND main_small.NAME = sourcelist.SourceFileName
 GROUP BY `PUID`,`DROID_V`,`SIG_V`,`SPEED` ORDER BY `DROID_V` ASC, `SIG_V`
 )
""",targetPUID)

and

targetPUID = "x-fmt/409"

edited Feb 27, 2012 at 16:50

Mariusz Jamro

31.9k25 gold badges129 silver badges170 bronze badges

answered Feb 27, 2012 at 16:34

Jay Gattuso

4,14812 gold badges40 silver badges51 bronze badges

1 Comment

Jay Gattuso Over a year ago

@Secator I will do as soon as I am allowed (another 23 hours - sorry) - I don't have enough rep yet.

Collectives™ on Stack Overflow

Inserting a python variable into a query

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related