python - psycopg2 copy_to part of table

Question

there is a copy_to to copy table data into file like:

cursor.copy_to(fp, 'table_name')

but I don't need to copy all table data, just a part of it. So when I do:

cursor.execute(query)
cursor.copy_to(fp, 'table_name')

it still copies the whole table. Is there a way to copy data from the query?

question from:https://stackoverflow.com/questions/65929241/psycopg2-copy-to-part-of-table

Answer 1

Use cursor.copy_expert(), place your query as an argument to COPY command, e.g.:

sql = """
    copy (
        select * 
        from my_table 
        where id < 5 
        order by id
    ) to stdout
"""

with open('/data/my_file.txt', 'w') as fp:
    cursor.copy_expert(sql, fp)
    

Read also about Postgres COPY command.