CODEWITHSUNDEEP
pythonpostgresqlpython-3.xpsycopg2
Harnish Kumar
Harnish Kumar

Reputation: 355

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

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?

Upvotes: 1

Views: 623

Answers (1)

systemjack
systemjack

Reputation: 2985

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,))

Upvotes: 1

Related Questions