Python globals not generating into code in read_sql execution

Question

I have to import data into a query where I want to fetch global variables that I declare. It works when I pass through one variable but not when I assign the multiple variables into the query. It is running but the result is not giving me back any data. Any thoughts on why my query isn't resulting in any data?

#Declare variables for queries
globals()['old_store1'] = 313
globals()['new_store1'] = 3157
globals()['old_store2'] = 126
globals()['new_store2'] = 3196
globals()['datefrom'] = '2/15/2019'
globals()['dateto'] = '3/22/2019'
globals()['yearadd'] = '+2'
globals()['dayadd'] = '+4'

#Assign ITC Query
ITCQuery = """SELECT 
    CAST( Store as VARCHAR)  + 'þ' as Store,
    CONVERT( VARCHAR, Tran_Dt2, 101 ) + 'þ' as Tran_Dt,
    CONVERT(char(5), Start_Time, 108) + 'þ' as Start_Time, 
    [Count]  
 FROM
 (
  SELECT 
    CASE 
    WHEN [Store] = {old_store1} THEN {new_store1}
    WHEN [Store] = {old_store2} THEN {new_store2}
    END AS Store
      , DATEADD (YEAR, {yearadd}, DATEADD(DAY, {dayadd}, Tran_Dt2)) as Tran_Dt2
      ,[Start_Time]
      ,[Count]
      ,Store as Sister_Store
 FROM 
(
SELECT 
Store,
CONVERT(datetime, Tran_Dt) as Tran_Dt2,
Start_Time,
Count 
FROM [VolumeDrivers].[dbo].[SALES_DRIVERS_ITC_Signup_65wks]
WHERE CONVERT(datetime, Tran_Dt)  between CONVERT(datetime,{datefrom}) and CONVERT(datetime,{dateto})
AND 
Store IN ({old_store1}, {old_store2}) 
--Single Store: Store = Store #
) AS A
) AS B
ORDER BY Tran_Dt2, Store
            """

#print(ITCQuery)

#execute query
Sales_Drivers_ITCSignup = pd.read_sql(ITCQuery.format(**globals()), con = conn)
Sales_Drivers_ITCSignup.head()

Where did you get the idea to write such strange (and quite horrible, I must say) things such as `globals()['old_store1'] = ...`? Why not `old_store1 = ...` ? — Thierry Lathuille, Apr 01 '21 at 18:49
why are you modifying `globals()` rather than creating a regular dict? — Chris_Rands, Apr 01 '21 at 18:49
The query seems to generate fine when I run your code. The replacements are all happening from what I can tell. You should compare the actual output of the `format()` command with what you expect, and see if you can tell why it's returning no data in that case versus if you don't replace anything in the string. — Random Davis, Apr 01 '21 at 18:51
I also would like join the chorus of buffled questions, and suggest *NOT* to use direct insertion parameters into a string to form *any* SQL query. This way you open your code to various SQL injection and other types of attacks. Pass parameters to a chosen SQL library instead, the library will take care of proper data formatting and quoting! — user3159253, Apr 01 '21 at 18:54
I originally had the variables declared like old_store1 =... but then it started giving me errors so I switched to how the online format was telling me which was the globals() way. So if I were to create a dictionary, how would I pass that into the query at the end? — adura826, Apr 01 '21 at 18:55
Yes, use parameters instead of trying to build the whole SQL yourself. As written above, you'll need to quote the dates yourself. — , Apr 01 '21 at 18:55
"how the online format was telling me" -- stop going to that site then. They're giving you bad advice. — , Apr 01 '21 at 18:56
Anyone have an abbreviated example of what this would look like? — adura826, Apr 01 '21 at 18:58
`pd.read_sql(...)` has a [params](https://pandas.pydata.org/docs/reference/api/pandas.read_sql.html#pandas-read-sql) argument. Use that. — , Apr 01 '21 at 18:58
I've got something like this but it says pyodbc.connection has no attribute 'format' `pd.read_sql(""" SELECT Top(10) Store, CONVERT(datetime, Tran_Dt) as Tran_Dt2, Start_Time, Count FROM [VolumeDrivers].[dbo].[SALES_DRIVERS_ITC_Signup_65wks] WHERE Store = {old_store1}""",con = conn.format(old_store1=3))` — adura826, Apr 01 '21 at 19:18
@adura826 For pysqlite use `:` form to specify a parameter placeholder and use `params` argument for `pandas.read_sql(....)` function. — user3159253, Apr 01 '21 at 19:26
something like this: `pd.read_sql("SELECT FROM table WHERE name=:name", con, params={"name": "John"})` — user3159253, Apr 01 '21 at 19:32
This gave me the error: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000') `pd.read_sql(""" SELECT Top(10) Store, CONVERT(datetime, Tran_Dt) as Tran_Dt2, Start_Time, Count FROM [VolumeDrivers].[dbo].[SALES_DRIVERS_ITC_Signup_65wks] WHERE Store = :store""",con = conn, params={"store":3})` — adura826, Apr 01 '21 at 19:35
It looks like you (or panda) use an old version of pysqlite, which doesn't support named parameters. Try to replace every parameter with '?' and use an array of parameters instead of a dictionary: `pd.read_sql("SELECT * FROM table WHERE column=?", con, params=("value",))`. However I do understand that statements like `SELECT * FROM table WHERE column=?` are less expressive than `SELECT * FROM table WHERE column=:value`. Maybe you can update pysqlite library. Version 2.6.0 (comes with python 3.9) do support named arguments for queries, I've just checked this. — user3159253, Apr 01 '21 at 20:20

Python globals not generating into code in read_sql execution

0 Answers0