sql - Exporting a PostgreSQL query to a csv file using Python

Question

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?

Answer 1

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.