Getting error while adding data to postgresql table using psycopg2

Question

I have a tuple as

final_weather_data = ({'date': '2016-05-11 13:22:58', 
  'place_id': '001D0A00B36E', 'barometer_unit': 'hPa', 
  'weather station name': 'NPCL Hatewa Substation',
  'wind_dir_unit': 'degree', 'temperature': 31.2, 
  'barometer': 1007.9, 'temp_unit': 'C', 'hum_unit': '%', 
  'wind_unit': 'km/h', 'wind_direction': 'NE nbsp 49', 
  'humidity': 60, 'wind_speed': 8.0})

I am trying to push it into a postgres table by

try:

con = psycopg2.connect("dbname='WeatherForecast' user='postgres' host='localhost' password='postgres'")
cur = con.cursor()
cur.mogrify("""INSERT INTO weather_data(temperature,temp_unit,humidity,hum_unit,wind,wind_speed_status,wind_unit,wind_dir,wind_dir_unit,barometer,bar_unit,updated_on,station_id) VALUES (%(temperature)s, %(temp_unit)s, %(humidity)s, %(hum_unit)s, %(wind)s, %(wind_speed_status)s, %(wind_unit)s, %(wind_dir)s, %(wind_dir_unit)s, %(barometer)s, %(bar_unit)s, %(updated_on)s, %(station_id)s);""", 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()

When i run the above code, it is raising an error "TypeError: tuple indices must be integers, not str". Instead , if i try like this I am following this https://wiki.postgresql.org/wiki/Psycopg2_Tutorial

Is your [previous question](http://stackoverflow.com/questions/37135098/value-error-while-importing-data-into-postgres-table-using-psycopg2) solved yet? Please post the full stack trace here. — AKS, May 11 '16 at 13:41
Your tuple contains a dictionary as the first item. Just make final_weather_data a dictionary remove the '(' and ')' — e4c5, May 11 '16 at 13:44
@AKS: cur.mogrify("""INSERT INTO weather_data(temperature,temp_unit,humidity,hum_unit,wind,wind_speed_status,wind_unit,wind_dir,wind_dir_unit,barometer,bar_unit,updated_on,station_id) VALUES (%(temperature)s, %(temp_unit)s, %(humidity)s, %(hum_unit)s, %(wind)s, %(wind_speed_status)s, %(wind_unit)s, %(wind_dir)s, %(wind_dir_unit)s, %(barometer)s, %(bar_unit)s, %(updated_on)s, %(station_id)s);""", final_weather_data) TypeError: tuple indices must be integers, not str. This is the full stack trace. — Harnish, May 11 '16 at 13:49
@e4c5: The above tuple is just a sample. My original one has many dictionary inside the tuple. — Harnish, May 11 '16 at 13:51
@Harnish: This doesn't look like a full stacktrace to me. Please add the full stack trace in the original post itself by editing it. — AKS, May 11 '16 at 14:01
If your real code is so valuable that you don't want to post it here, it's very difficult for anyone to help you — e4c5, May 11 '16 at 14:08

score 2 · Answer 1 · answered May 11 '16 at 22:19

2

In your case final_weather_data is tuple of dicts. But you use text key in query. It is actually a reason of error: "TypeError: tuple indices must be integers, not str".

Please, try:

final_weather_data = {
  'date': '2016-05-11 13:22:58', 
  'place_id': '001D0A00B36E', 'barometer_unit': 'hPa', 
  'weather station name': 'NPCL Hatewa Substation',
  'wind_dir_unit': 'degree', 'temperature': 31.2, 
  'barometer': 1007.9, 'temp_unit': 'C', 'hum_unit': '%', 
  'wind_unit': 'km/h', 'wind_direction': 'NE nbsp 49', 
  'humidity': 60, 'wind_speed': 8.0
}

answered May 11 '16 at 22:19

Ivan Burlutskiy

1,605
1
12
22

Well, I tried. Its raising "Error no results to fetch". And also i tried printing cur.statusmessage. It returns None. – Harnish May 12 '16 at 04:36
@Harnish, Correct. Fetch need to retrieve result of query. Insert have no result (Actually you can setup db to return count of inserted rows or [last insert id](http://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id)). But in this case you need just commit connection before close it. – Ivan Burlutskiy May 12 '16 at 06:16
I tried cur.commit() before closing the connection. But no luck. – Harnish May 12 '16 at 09:35

score 0 · Accepted Answer · answered May 12 '16 at 10:40

This saved me.  
con = psycopg2.connect("dbname='WeatherForecast' user='postgres' host='localhost' password='postgres'")
cur = con.cursor()
fieldnames = ['temperature', 'temp_unit', 'humidity', 'hum_unit', 'wind', 'wind_speed_status', 'wind_unit', 'wind_dir', 'wind_dir_unit', 'barometer', 'bar_unit', 'updated_on', 'station_id']
sql_insert = ('INSERT INTO weather_data (%s) VALUES (%s)' %
              (','.join('%s' % name for name in fieldnames),
               ','.join('%%(%s)s' % name for name in fieldnames)))
cur.executemany(sql_insert, stations_weather_data)

Getting error while adding data to postgresql table using psycopg2

2 Answers2