1

I'm working on exporting resultset data from remote PostgresQL DB to my local machine using Psycopg2.

I have SQL queries that export the resultset into a CSV file. But those files are going to be created in the remote machine path where the DB is hosted. I'm using psycpog2 to connect to the remote database using python.

As far as I understand we can run command psql to export the CSV file from terminal as mentioned in How to insert CSV data into PostgreSQL database (remote database ).

But how do I do the same thing using Psycopg2 python. Is there any way other than os.system('psql ... .. ..') to export CSV from remote db connection to my local using python.

Underoos
  • 4,708
  • 8
  • 42
  • 85

1 Answers1

3

Use copy_to or copy_expert from the cursor class. http://initd.org/psycopg/docs/cursor.html

import sys
import psycopg2

db = psycopg2.connect("")

with db.cursor() as cursor:
    cursor.copy_expert(
        """copy (select * from pg_timezone_names) to stdout with csv header""",
        file=sys.stdout.buffer,
        size=256*1024
    )

To send data to a file instead of stdout:

import psycopg2

db = psycopg2.connect("")

with open('myfile.csv', 'w') as f:
    with db.cursor() as cursor:
        cursor.copy_expert(
            """copy (select * from pg_timezone_names) to stdout with csv header""",
            file=f,
            size=256*1024
        )
Jeremy
  • 6,313
  • 17
  • 20
  • What would the file name be when it is created on client machine? – Underoos Oct 21 '19 at 16:39
  • This example (thanks @Tometzky) is just sending the data to stdout. You could create any file you like and use that instead of sys.stdout.buffer. I updated the answer with an example. – Jeremy Oct 21 '19 at 18:16
  • 1
    Or just redirect output to a file with `program.py > filename.csv`. – Tometzky Oct 22 '19 at 19:28