Search code examples
python-3.xpostgresql-9.3

Table with "TIME" column could not be accessed from Python

I have a table in Postgres with a column name as TIME(UpperCase). While inserting csv into this table from Postgres itself using SQL command is easy.

COPY american_district FROM 'O:\Python\PostGREsql\district.csv' WITH CSV HEADER DELIMITER AS ',' NULL AS '\N';

but inserting the same csv into the table using python code below gives me an error as

f = open('O:\Python\PostGREsql\district.csv')
cur_DBKPI.copy_from(f, 'american_district', sep=',', null='')

ERROR:

psycopg2.DataError: invalid input syntax for type date: "TIME"
CONTEXT:  COPY american_district, line 1, column time: "TIME"

I found that its best practice to keep the column in lower case but is there any workaround of it?

Solution

  • Got it working by changing the syntax of my query to add header in it using "copy_expert".

    f = open('O:\Python\PostGREsql\district.csv')
sql = "COPY american_district FROM STDIN WITH CSV HEADER DELIMITER AS ',' NULL AS '\\N'"
cur_DBKPI.copy_expert(sql=sql,file=f)