Using parameterized SQL query while reading large table into pandas dataframe using COPY

Question

I am trying to read a large table (10-15M rows) from a database into pandas dataframe and I'm using the following code:

def read_sql_tmpfile(query, db_engine):
    with tempfile.TemporaryFile() as tmpfile:
        copy_sql = "COPY ({query}) TO STDOUT WITH CSV {head}".format(
           query=query, head="HEADER"
        )
        conn = db_engine.raw_connection()
        cur = conn.cursor()
        cur.copy_expert(copy_sql, tmpfile)
        tmpfile.seek(0)
        df = pandas.read_csv(tmpfile)
        return df

I can use this if I have a simple query like this and I pass this into above func:

'''SELECT * from  hourly_data'''

But what if I want to pass some variable into this query i.e.

'''SELECT * from  hourly_data where starttime >= %s '''

Now where do I pass the parameter?

Laurenz Albe · Accepted Answer · 2019-08-23 05:55:15Z

1

You cannot use parameters with COPY. Unfortunately that extends to the query you use inside COPY, even if you could use parameters with the query itself.

You will have to construct a query string including the parameter (beware of SQL injection) and use that with COPY.

answered Aug 23, 2019 at 5:55

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Using parameterized SQL query while reading large table into pandas dataframe using COPY

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