How To Export Postgres Resultset As CSV From Remote Db Connection To Local Machine?

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.

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
        )