declare variable with pd.read_sql_query

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).

Answer

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)

Advertisement

Answer