declare variable with pd.read_sql_query

Question

Can someone explain why do I get an error when executing the following simple query with pandas:

import pyodbc
import pandas as pd

connstr = 'Driver={SQL Server}; Server=sr1; Database=db'
conn = pyodbc.connect(connstr)
query = """DECLARE @t AS DATETIME;
            SET @t = \'12-01-2020\';
            
            select top 10 AccountNumber
        FROM db.tb
          where ForecastDate >= @t"""


rt = pd.read_sql_query(conn, query)
conn.close()

ArgumentError: Could not parse rfc1738 URL from string 'DECLARE @t AS DATETIME;
            SET @t = '12-01-2020';

I understand that I can pass the variable t as a parameter to pandas and it will work, but I want to understand in principle what is causing the error, what are the restrictions on queries that can be passed to pandas. The query seems to work fine with cursor.execute(query).

Why do you need to declare? Simply pass date into where clause via iso format: 'YYYY-MM-DD'. Or pass as param from Python. — Parfait
– Parfait, Commented Dec 12, 2020 at 17:54
Yes, I can. I just want to understand what is the problem with the declaration in principle. — guyguyguy12345
– guyguyguy12345, Commented Dec 12, 2020 at 18:05
First argument of read_sql is SQL query string not connection object. — Parfait
– Parfait, Commented Dec 12, 2020 at 19:11

Gord Thompson · Accepted Answer · 2020-12-12 20:33:04Z

4

You passed the arguments to .read_sql_query() in the wrong order. You used

rt = pd.read_sql_query(conn, query)

when it should be

rt = pd.read_sql_query(query, conn)

answered Dec 12, 2020 at 20:33

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

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

Collectives™ on Stack Overflow

declare variable with pd.read_sql_query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related