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?
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)