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?

Have you tried using quoting, so it would be \\ instead of just one \? See [this posting](https://dba.stackexchange.com/questions/123746/list-of-escape-sequences-for-postgresql-string-data-types) and [this one](https://stackoverflow.com/questions/12316953/insert-text-with-single-quotes-in-postgresql). — Matthias, Apr 02 '18 at 12:13
Remove the `'\'` `\copy` is a psql command, not an sql command. — wildplasser, Apr 02 '18 at 12:20

klin · Accepted Answer · 2023-01-06T01:22:59.583

38

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.

edited Jan 06 '23 at 01:22

answered Apr 02 '18 at 13:51

klin

112,967
15
204
232

score 1 · Answer 2 · answered Apr 02 '18 at 13:43

\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.

score 0 · Answer 3 · edited Aug 02 '21 at 08:15

0

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)

edited Aug 02 '21 at 08:15

MD Mushfirat Mohaimin

1,966
3
10
22

answered Aug 02 '21 at 05:27

aditya Mehta

33
1
10

score 0 · Answer 4 · answered Feb 25 '22 at 10:50

0

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)

answered Feb 25 '22 at 10:50

Wojciech Moszczyński

2,893
21
27

Exporting a PostgreSQL query to a csv file using Python

4 Answers4

Linked

Related