How to pass a data frame as parameter to a SQL query in Python?

Question

I have a dataframe that consists of one column of values and I want to pass it as a parameter to execute the following sql query:

query = "SELECT ValueDate, Value"\
        "FROM Table "\
        "WHERE [ID] in ( ? ) "

So I tried (among so many other things) the following:

      df = pd.read_sql_query(query, conn, params=[ df['ID'].values ])
      df = pd.read_sql_query(query, conn, params=[ df['ID'].tolist ])
      df = pd.read_sql_query(query, conn, params=[ list(df['ID'].values) ])
       ...

What is the correct way to pass the dataframe values ?

NB: I am using Microsoft SQL Server so the query needs to be formatted as I did.

Now that you have specified sql-server, table valued parameters might be the way to go. — Dan Bracuk
– Dan Bracuk, Commented Apr 11, 2017 at 19:18

Max Power · Accepted Answer · 2017-04-11 19:25:53Z

4

Does this get you what you need?

import pandas as pd

your_column = pd.Series([1,2,3,4,5,6,7,8,9])

query = "SELECT ValueDate, Value"\
        "FROM Table "\
        "WHERE [ID] in {}".format(tuple(your_column))

print(query)
# 'SELECT ValueDate, ValueFROM Table WHERE [ID] in (1, 2, 3, 4, 5, 6, 7, 8, 9)'

Then you should be able to query without further parameters.

df = pd.read_sql_query(query, conn)

answered Apr 11, 2017 at 19:25

Max Power

9,13616 gold badges63 silver badges109 bronze badges

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

1 Comment

Parachute Over a year ago

This will work only if your column has more than one rows. If it has only one row, converting it into a tuple will result in SQL error. Query will look like " .... where [ID] in (1,) ". As you are converting the result into a tuple.

user2722968 · Accepted Answer · 2017-04-11 19:13:40Z

0

params = tuple(df['ID'].values)
sql = "SELECT COUNT(*) FROM foobar WHERE id IN (%s)" % (",".join(["?"]*len(params)),)
cursor.execute(sql, params)

answered Apr 11, 2017 at 19:13

user2722968

17.3k5 gold badges58 silver badges77 bronze badges

2 Comments

astudentofmaths Over a year ago

Thank you but I forgot to say that I am using Microsoft SQL Server, so the query needs to be formatted as in my question.

BusyProgrammer Over a year ago

Consider adding a description of how the code you've posted works, it will be helpful to users that visit this post in the future.

Collectives™ on Stack Overflow

How to pass a data frame as parameter to a SQL query in Python?

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related