CODEWITHSUNDEEP
pythonpostgresqlcsvpsycopg2
sc28
sc28

Reputation: 1213

How to save CSV file from query in psycopg2

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!

Upvotes: 4

Views: 8309

Answers (3)

Alper Tellioğlu
Alper Tellioğlu

Reputation: 193

This does work for me:

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

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

Upvotes: 0

ScotterMonkey
ScotterMonkey

Reputation: 1044

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()
```

Upvotes: 5

ScotterMonkey
ScotterMonkey

Reputation: 1044

Hopefully this helps:

cursor.copy_to(COPY table TO file syntax)

So in your case:

cursor.copy_to(COPY records TO io)

Upvotes: 2

Related Questions