Parameters in pyodbc in pandas read_sql_query

Question

Given the following query string,

symbol = "'AAPL'"

query = """SELECT TOP (1000000) 
       [date]
      ,[symbol]
      ,[open]
      ,[high]
      ,[low]
      ,[close]
      ,[volume]
      ,[exch]
      FROM [AMEXEOD].[dbo].[Stocks_eod]
      where 
          symbol = :symbol
      order by 
         date desc"""

When I try to execute it:

df = pd.read_sql_query(query, conn, params={'symbol': symbol})

I get an error:

('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')

I thought the :symbol is the way to mark parameters?

Unlike other DB-APIs, pyodbc does not support named parameters. Use ? placemarker instead and pass list into params argument of pandas' read_sql. — Parfait
– Parfait, Commented Oct 6, 2019 at 18:30

user6235970 · Accepted Answer · 2019-10-08 20:58:37Z

1

What version of python are you using? As you should be able to use string formatting.

Python 2.x

symbol = "'AAPL'"

query = """SELECT TOP (1000000) 
       [date]
      ,[symbol]
      ,[open]
      ,[high]
      ,[low]
      ,[close]
      ,[volume]
      ,[exch]
      FROM [AMEXEOD].[dbo].[Stocks_eod]
      where 
          symbol = {0}
      order by 
         date desc""".format(symbol)

Python 3.x

symbol = "'AAPL'"

query = """SELECT TOP (1000000) 
       [date]
      ,[symbol]
      ,[open]
      ,[high]
      ,[low]
      ,[close]
      ,[volume]
      ,[exch]
      FROM [AMEXEOD].[dbo].[Stocks_eod]
      where 
          symbol = {symbol}
      order by 
         date desc"""

Lots of detail here on this if you're interested.

Add then use this in your query: df = pd.read_sql_query(query, conn)

answered Oct 8, 2019 at 20:58

user6235970

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

1 Comment

Bookamp Over a year ago

this query is not parameterized, and therefore subject to sql injection.

Collectives™ on Stack Overflow

Parameters in pyodbc in pandas read_sql_query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related