Inserting row into Postgresql using python

Question

I created a table in postgreqsl using python and would like to fill it with random dataset containing different data types. But I get the error 'not all arguments converted during string formatting'. Anybody an idea what im doing wrong. I have read through other post but I cant find the solution.

creating table

def create_tables():
""" create table in the PostgreSQL database"""
commands = (
 """
 Create TABLE flight_observations(
 time TIMESTAMP,
 numDayofweek INTEGER,
 numHour INTEGER,
 ac_type TEXT,
 adep TEXT,
 ades TEXT,
 curr_sect TEXT,
 lon_t FLOAT(6),
 lat_t FLOAT(6),
 vg_t INTEGER,
 hdot_t FLOAT8,
 bearing FLOAT8,
 WCA FLOAT8,
 ws FLOAT8,
 wd FLOAT8,
 temp INTEGER
 )
 """)
conn = None
try:
 # connection string
 conn_string = "host='localhost' dbname='postgres' user='postgres' password='xxxx'"
 # print connection string to connect
 print "Connecting to database\n ->%s" % (conn_string)
 # read the connection parameters
 params = Config()
 # connect to the PostgreSQL server
 conn = psycopg2.connect(conn_string)
 cur = conn.cursor()
 # create table one by one
 cur.execute(commands)
 # close communication with the PostgreSQL database server
 cur.close()
 # commit the changes
 conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
 print(error)
finally:
 if conn is not None:
 conn.close()

data

flight_obs = ['2016-07-01 16:42:21', 'A319', 'EDDB', 'EGKK', 'EDYYSOLX', 11.071111, 52.366389, 206.5938752827827, 5.55, 268.9576458923286, 5.238123301016344, 29.257257205897805, 234.0554644610864, 221.8523282183259]

filling table

def insert_flight_list(flight_obs):
sql = "INSERT INTO flight_observations(time, ac_type, adep, ades, curr_sect, lon_t, lat_t, vg_t, hdot_t, bearing, wca, ws, wd, temp) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
conn = None
try:
 # connection string
 conn_string = "host='localhost' dbname='postgres' user='postgres' password='xxxx'"
 # print connection string to connect
 print "Connecting to database\n ->%s" % (conn_string)
 # read database configuration
 params = Config()
 # connect to the PostgreSQL database
 conn = psycopg2.connect(conn_string)
 # create a new cursor
 cur = conn.cursor()
 # execute the INSERT statement
 cur.executemany(sql, flight_obs)
 conn.commit()
 cur.close()
except (Exception, psycopg2.DatabaseError) as error:
 print(error)
finally:
 if conn is not None:
 conn.close()
insert_flight_list(flight_obs)

Not sure why the try statements are not indenting in this post. They are indented in the python code

• user8098055: Which line of your code is giving you this error?

Answer 1

I believe the issue is with cur.executemany(sql, flight_obs). The docs say:

executemany(sql, vars_list)

Execute a database operation (query or command) against all parameter tuples or mappings found in the sequence vars_list.

so it's effectively doing the equivalent of:

for i in flight_obs:
 cur.execute(sql, i)

Since flight_obs is a list of strings, not tuples/mappings, you end up with something like:

cur.execute(sql, '2016-07-01 16:42:21')
cur.execute(sql, 'A319')
cur.execute(sql, 'EDDB')

Simple fix - just replace cur.executemany with cur.execute and it should all work.

• mastersom: Yes, True. Thanks @match. I just saw it myself right now.

Answer 2

turn your "flight_obs" into a tuple before passing it to executemany()

cur.executemany(sql, tuple(flight_obs))

BTW, do you want "exeutemany" or just "execute"?