I need to export some rows from a table in a PostgreSQL database to a .csv file using a Python script:
#!/usr/bin/python # -*- coding: utf-8 -*- import sys, psycopg2 ... conn = psycopg2.connect("dbname=dbname user=user password=password") cur = conn.cursor() sql = "copy (SELECT * FROM table WHERE month=6) TO '/mnt/results/month/table.csv' WITH CSV DELIMITER ';';" cur.execute(sql) cur.close() ...
But when I run the script I get this:
Syntax error at or near «» LINE 1: copy (SELECT * FROM TABLE WHERE month=6) TO '...
Does anyone know what can be wrong or give me a tip about?
Advertisement
Answer
The copy
is not an SQL command, it is a command specific for the Postgres terminal client psql and cannot be used in this context.
Use copy_expert(sql, file, size=8192)
instead, e.g.:
sql = "COPY (SELECT * FROM a_table WHERE month=6) TO STDOUT WITH CSV DELIMITER ';'" with open("/mnt/results/month/table.csv", "w") as file: cur.copy_expert(sql, file)
Read more about the function in the documentation.