Inserting data from csv to postgres table using psycopg2 and python 3.4

Question

I have a postgres table with below structure

CREATE TABLE edmonton.permit
                            (permit_date text,
                             permit_number text,
                             year numeric,
                             month_number integer,
                             report_permit_date text,
                             job_category text,
                             address text,
                             legal_description text,
                             neighbourhood text,
                             neighbourhood_number text,
                             job_description text,
                             building_type text,
                             work_type text,
                             floor_area numeric,
                             construction_value integer,
                             zoning text,
                             units_added numeric,
                             latitude double precision,
                             longitude double precision,
                             location text,
                             count integer

I am trying to read a csv file from a relative path and import data into postgres table like below

                with open(file) as f:
                reader = csv.reader(f, delimiter=',')
                next(reader, None)
                for row in reader:
                    cur.execute("INSERT INTO edmonton.{}(permit_date, permit_number, year, month_number, report_permit_date, job_category, address, legal_description, neighbourhood, neighbourhood_number, job_description, building_type, work_type, floor_area, construction_value, zoning, units_added, latitude, longitude, location, count) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)".format(permit),row)
                    con.commit()

I am getting the below error

Invalid literals for numeric datatype.

What place holders should be used for numeric,integer,double precision using python3.4?

The placeholders are %s for everything with Psycopg. You need to show the csv. — Clodoaldo Neto
– Clodoaldo Neto, Commented Mar 7, 2017 at 14:46

systemjack · Accepted Answer · 2017-03-07 20:27:30Z

1

This might help to narrow it down. Will attempt to format each field and give error on the one causing an issue.

with open(file) as f:
reader = csv.reader(f, delimiter=',')
next(reader, None)
for row in reader:
    for item in row:
        print cursor.mogrify("%s", (item,))

edited Mar 7, 2017 at 20:27

answered Mar 7, 2017 at 20:20

systemjack

3,0051 gold badge21 silver badges27 bronze badges

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

Collectives™ on Stack Overflow

Inserting data from csv to postgres table using psycopg2 and python 3.4

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related