Value Error while importing data into postgres table using psycopg2

Question

I have a tuple as below

data = ({'weather station name': 'Substation', 'wind': '6 km/h', 'barometer': '1010.3hPa', 'humidity': '42%', 'temperature': '34.5 C', 'place_id': '001D0A00B36E', 'date': '2016-05-10 09:48:58'})

I am trying to push the values from the above tuple to the postgres table using the code below:

try:                                                                                                                                                                                                         
    con = psycopg2.connect("dbname='WeatherForecast' user='postgres' host='localhost' password='****'")                                                                                                         
    cur = con.cursor()                                                                                                                                                                                                  
    cur.executemany("""INSERT INTO weather_data(temperature,humidity,wind,barometer,updated_on,place_id) VALUES (%(temperature)f, %(humidity)f, %(wind)f, %(barometer)f, %(date)s, %(place_id)d)""", final_weather_data)
    ver = cur.fetchone()                                                                                                                                                                                                
    print(ver)                                                                                                                                                                                                          

except psycopg2.DatabaseError as e:                                                                                                                                                                                     
    print('Error {}'.format(e))                                                                                                                                                                                         
    sys.exit(1)

finally:                                                                                                                                                                                                                

  if con:                                                                                                                                                                                                             
     con.close()

Where datatype of each field in the DB is as below: id serial NOT NULL,

temperature double precision NOT NULL,
humidity double precision NOT NULL,
wind double precision NOT NULL,
barometer double precision NOT NULL,
updated_on timestamp with time zone NOT NULL,
place_id integer NOT NULL,

When i run the code to push the data into postgres table using psycopg2, it is raising an error "ValueError: unsupported format character 'f'"

I hope the issue is in formatting. Am using Python3.4

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

Have a look at the documentation:

The variables placeholder must always be a %s, even if a different placeholder (such as a %d for integers or %f for floats) may look more appropriate:
>>> cur.execute("INSERT INTO numbers VALUES (%d)", (42,)) # WRONG
>>> cur.execute("INSERT INTO numbers VALUES (%s)", (42,)) # correct

While, your SQL query contains all type of placeholders:

"""INSERT INTO weather_data(temperature,humidity,wind,barometer,updated_on,place_id) 
   VALUES (%(temperature)f, %(humidity)f, %(wind)f, %(barometer)f, %(date)s, %(place_id)d)"""

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 10, 2016 at 11:06

AKS

20k3 gold badges47 silver badges55 bronze badges

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

3 Comments

Harnish Over a year ago

I tried changing all the placeholder to %s. But am receiving TypeError: tuple indices must be integers, not str.

AKS Over a year ago

All the data you have in the dict is in string format e.g. 'wind': '6 km/h' while you expect wind double precision NOT NULL which is a double value. You first need to write a converter which will explicitly format the data that you have into what should be entered in the database and then come to postgres for the insertion bit.

Harnish Over a year ago

Well, i changed the field datatype to be character varying. Now i tried changing all the placeholders to %s. Still the error "TypeError: tuple indices must be integers, not str." remains same.

Collectives™ on Stack Overflow

Value Error while importing data into postgres table using psycopg2

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related