Getting error in Importing CSV data into postgreSQL using Python

Question

I am trying to import CSV data I have into postgreSQL using Python. It shows an error, when I run the code.

import csv
import psycopg2
import time
from datetime import datetime, timedelta

yesterday = datetime.strftime(datetime.now() - timedelta(1), '%Y%m%d')
print yesterday

conn = psycopg2.connect(host="172.19.1.228", database="stat", user="radio",
                        password="abcd1234", port="5432")

tem = "copy e_report FROM '/home/ftpuser/Report/Report_E_RadioStat_' & " \
      "'yesterday' & '.csv' With DELIMITER ',' CSV HEADER;"

cursor = conn.cursor()

cursor.execute(tem)

The error shown below:

Traceback (most recent call last):
  File "C:\Users\p4532\Desktop\python\python_test.py", line 22, in <module>
    cursor.execute(tem)
ProgrammingError: syntax error at or near "&"
LINE 1: ...t FROM '/home/ftpuser/Report/Report_E_RadioStat_' & 'yesterd...

Please suggest a way to resolve this error.

Answer 1

The text concatenation operator in Postgresql is ||:

'/home/ftpuser/Report/Report_E_RadioStat_' || 'yesterd...

https://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-SQL

Use Psycopg copy_from in instead:

http://initd.org/psycopg/docs/cursor.html#cursor.copy_from

Answer 2

In addition to the concatenation operator, note that the copy command treats the filename as a path on the server. If you are connecting to a remote database, you need to use the from STDIN form of the command. Also, since you have a header in the file, you should use copy_expert vs copy_from. The latter also accepts a file, but doesn't let you specify that there is a header.

sql = "copy e_report from stdin with delimiter ',' csv header"
with open(filename, 'r') as instream, conn.cursor() as cursor:
    cursor.copy_expert(sql, instream)