Dynamically created Dataframe as SQL parameter in Python

Question

I am trying to pull SQL data in python through the read_sql_query().

To pull the data, i am using the below SQL query:

SELECT * FROM table WHERE id IN (:ids);

before these steps, the previous code generates a list of ids in the form of a dataframe. This dataframe can have only 1 id or more than 1000 ids as well.

After searching elsewhere, I can save the df in the form of tuples and then save the query as string with the tuples. I found this solution here: How to pass a data frame as parameter to a SQL query in Python?

But this only works if the df has less than 1000 records.

How to run this so as to include something like 1800 ids.

Barbaros Özhan · Accepted Answer · 2020-09-12 08:44:15Z

0

The read_sql_query() function supports a chunksize argument.

You can use

for chunk in pd.read_sql_query("SELECT * FROM tab WHERE ids IN ({})".format(ids), engine, chunksize=100):
    print(chunk)

default value of chunksize is None. if a spesific value is provided, then pandas will be interaction with the database to receive some more rows for every iteration of the loop depending on the chunksize value.

edited Sep 12, 2020 at 8:44

answered Sep 12, 2020 at 5:11

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

Dynamically created Dataframe as SQL parameter in Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related