2

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.

Community
  • 1
  • 1
Jay Gattuso
  • 3,890
  • 12
  • 37
  • 51

3 Answers3

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),))
enderskill
  • 7,354
  • 3
  • 24
  • 23
  • I did try that, and it returns an empty list - `()` thanks though. – Jay Gattuso Feb 27 '12 at 05:43
  • Try replacing `%s` with the `targetPUID` value in the query and manually execute it with a SQL application to see if the query works. – enderskill Feb 27 '12 at 06:09
  • Yupe, it works fine when manually implemented via this python method, so I know its the variable aspect thats at fault. – Jay Gattuso Feb 27 '12 at 06:19
  • 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. – enderskill Feb 27 '12 at 06:48
1
cur.execute(..., (targetPUID,))
Ignacio Vazquez-Abrams
  • 776,304
  • 153
  • 1,341
  • 1,358
  • 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. – Jay Gattuso Feb 27 '12 at 05:46
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"
Mariusz Jamro
  • 30,615
  • 24
  • 120
  • 162
Jay Gattuso
  • 3,890
  • 12
  • 37
  • 51