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 near (User) when I run the code. Can someone please provide assistance. I am new to Programming so please pardon my stupidity.

import psycopg2
import csv

csv_data = csv.reader(file('SampleData2.csv'))

database = psycopg2.connect (database = "***", user="***", password="***", host="localhost", port="5432")

cursor = database.cursor()
delete = """Drop table if exists Real.SampleDataTwo"""
print (delete)

mydata = cursor.execute(delete)

cursor.execute("""Create Table Real.SampleDataTwo
                (User varchar(55),
                LastUpdate timestamp,
                Week date,
                Builder varchar(55),
                Traffic integer
                );""")

print "Table created successfully"

for row in csv_data:

    cursor.execute("INSERT INTO Real.SampleDataTwo (User, LastUpdate, Week, Builder, Traffic)"\
                "VALUES (%s,%s,%s,%s,%s)",
               row)


cursor.close()
database.commit()
database.close()

print "CSV data imported"

The error it shows is:

Drop table if exists Real.SampleDataTwo

Traceback (most recent call last):
  File "C:/Users/Programming/Data.py", line 20, in <module>
);""")
ProgrammingError: syntax error at or near "User"
LINE 2:                 (User varchar(55),
                         ^

I think you're missing id param: id INT PRIMARY KEY NOT NULL, — Jean-François Fabre
– Jean-François Fabre ♦, Commented Oct 28, 2016 at 20:36

mkj · Accepted Answer · 2016-10-28 20:47:35Z

4

I believe user is not allowed as column name. See https://www.postgresql.org/docs/current/static/sql-keywords-appendix.html#KEYWORDS-TABLE

answered Oct 28, 2016 at 20:47

mkj

2,8515 gold badges25 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jacob_Cortese Over a year ago

Thanks MKJ, you're absolutely right. User can't be used as column name.

frostshoxx · Accepted Answer · 2016-10-28 20:55:48Z

3

Try change User to something else and see if the issue is fixed. If not, then try to run the example given by PostgreSQL Python guide: http://www.postgresqltutorial.com/postgresql-python/create-tables/

answered Oct 28, 2016 at 20:55

frostshoxx

5361 gold badge7 silver badges24 bronze badges

Collectives™ on Stack Overflow

importing CSV data into postgreSQL using Python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related