How to export Postgres resultset as CSV from remote db connection to local machine?

Question

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.

Jeremy · Accepted Answer · 2019-10-22 12:32:31Z

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
        )

edited Oct 22, 2019 at 12:32

answered Oct 20, 2019 at 8:51

Jeremy

6,77322 silver badges21 bronze badges

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

3 Comments

Underoos Over a year ago

What would the file name be when it is created on client machine?

Jeremy Over a year ago

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.

Tometzky Over a year ago

Or just redirect output to a file with program.py > filename.csv.

Collectives™ on Stack Overflow

How to export Postgres resultset as CSV from remote db connection to local machine?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related