How to save CSV file from query in psycopg2

Question

I'm trying to save in a local .csv the results of query performed in python towards a PostgreSQL database (using psycopg2).

I'm able to print the result of the query in the console, but fail to export it to a csv file.

I've tried using the copy_to function, but even with the documentation I can't figure it out:

    # Retrieve the records from the database with query
    cursor.execute("SELECT col1 FROM myDB.myTable WHERE col1 > 2")
    records = cursor.fetchall()

    # Save to csv with copy_to
    io = open('copy_to.csv', 'w')
    cursor.copy_to(io, 'records', ',')
    print("Copied records from query into file object using sep = ,")
    io.close()

This provokes the error "psycopg2.ProgrammingError: relation "records" does not exist".

Is there a better way to store the query's result in a local table which could be passed in copy_to? Thanks for any tips!

copy_to is really meant to work on entire tables, not queries. Consider using python's csv library or pandas to write the csv file. — Michael
– Michael, Commented Jan 26, 2017 at 22:55

ScotterMonkey · Accepted Answer · 2019-10-31 17:30:40Z

5

I did some more research and here is another solution that might be more likely to work:

``` python
import psycopg2

#note the lack of trailing semi-colon in the query string, as per the Postgres documentation
s = "'SELECT col1 FROM myDB.myTable WHERE col1 > 2'"

conn = psycopg2.connect...
db_cursor = conn.cursor()

SQL_for_file_output = "COPY ({0}) TO STDOUT WITH CSV HEADER".format(s)

WITH Open(filepath/name, 'w') as f_output:
    cur.copy_expert(SQL_for_file_output, f_output)

conn.close()
```

answered Oct 31, 2019 at 17:30

ScotterMonkey

1,05414 silver badges30 bronze badges

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

Comments

ScotterMonkey · Accepted Answer · 2019-10-31 17:12:29Z

2

Hopefully this helps:

cursor.copy_to(COPY table TO file syntax)

So in your case:

cursor.copy_to(COPY records TO io)

answered Oct 31, 2019 at 17:12

ScotterMonkey

1,05414 silver badges30 bronze badges

Comments

Alper Tellioğlu · Accepted Answer · 2021-07-28 11:02:57Z

0

This does work for me:

csv_file = open('myFile.csv', 'w')

cursor = finbot.conn.cursor()
cursor.copy_to(csv_file, 'table_name', sep=",")

answered Jul 28, 2021 at 11:02

Alper Tellioğlu

3031 gold badge2 silver badges5 bronze badges

Collectives™ on Stack Overflow

How to save CSV file from query in psycopg2

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related