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.
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 )
- → What are the pluses/minuses of different ways to configure GPIOs on the Beaglebone Black?
- → Django, code inside <script> tag doesn't work in a template
- → React - Django webpack config with dynamic 'output'
- → GAE Python app - Does URL matter for SEO?
- → Put a Rendered Django Template in Json along with some other items
- → session disappears when request is sent from fetch
- → Python Shopify API output formatted datetime string in django template
- → Shopify app: adding a new shipping address via webhook
- → Shopify + Python library: how to create new shipping address
- → shopify python api: how do add new assets to published theme?
- → Access 'HTTP_X_SHOPIFY_SHOP_API_CALL_LIMIT' with Python Shopify Module