Exporting a PostgreSQL query to a csv file using Python

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.

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