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?

Debra

Have you tried using quoting, so it would be \\ instead of just one \? See this posting and this one. – Mordent

Remove the '\' \copy is a psql command, not an sql command. – Basicity

check: gis.stackexchange.com/a/72476 – Studious

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.

Buffy

it doesn't store header though. To save with header sql = "COPY (SELECT * FROM tablename) TO STDOUT WITH DELIMITER ',' CSV HEADER;" – Neighborly

Answer 2

\COPY is a psql command (client based) and therefore not a valid SQL command. Try the same simply using COPY (without the backslash\).

If you want the output file directly at the client, you might wanna use the STDOUT of COPY, otherwise the it will be created at the database server, which is a luxury not everyone has.

Ramillies

Answer 3

Yes, all statements are correct related to \COPY as SQL command. If you want to try the same as you stated in the question then you can try it as a shell command in python script.

Eg:

import os
      
cmd1 = " Your SQL to csv Command"           
os.system(cmd1)

Rigamarole

Answer 4

You can use this syntax:

query = """ select * from instalacje WHERE date > '2022-02-20'"""

outputquery = "COPY ({0}) TO STDOUT WITH CSV HEADER".format(query)

with open("C:/1/Wojciech_CCC.csv", "w") as f:
    cur.copy_expert(outputquery, f)

Deter