Insert data to PostgreSQL

Question

Im inserting data to a PostgreSQL database using the below function.

Im not very happy with the solution for the IF statements to handle the adaptation of Python None to PostgreSQL NULL. Would there be a more elegant solution I would be happy yo know.

Also, I believe I am not being very performant using .execute() method . Where could i start to convert my function to make usage of .executemany() perhaps.

def insert_into_table(data):
 # print json.dumps(data, indent=4)
 conn = psycopg2.connect(database='testdb', user='postgres', password='password', host='localhost')
 cursor = conn.cursor()
 for i in data:
 # print json.dumps(i, indent=4)
 iso_code = i["iso_code"]
 if i["l_postcode"] is None:
 l_postcode = 'NULL'
 else:
 l_postcode = i["l_postcode"]
 if i["r_postcode"] is None:
 r_postcode = 'NULL'
 else:
 r_postcode = i["r_postcode"]
 link_id = i["link_id"]
 geom = i["geometry"]
 # sql = 'INSERT into data_load VALUES (\'{}\', {}, {},{} , st_geomfromgeojson(\'{}\'))'.format(iso_code, l_postcode, r_postcode, link_id, json.dumps(geom))
 sql = """INSERT into data_load (iso_code, l_postcode, r_postcode, link_id, geom) VALUES ('{}',{},{},{},st_geomfromgeojson(\'{}\'));""".format(iso_code, l_postcode, r_postcode, link_id, json.dumps(geom))
 print sql
 cursor.execute(sql, )
 print sql
 cursor.close()
 conn.commit()
 conn.close()

A json fragment example of the load im trying to load , this example has only 3 objects for the sake of simplicity .

[
 {
 "geometry": {
 "type": "LineString", 
 "coordinates": [
 [
 -91.98979, 
 15.644559999999998
 ], 
 [
 -91.98971, 
 15.645249999999999
 ]
 ]
 }, 
 "iso_code": "MEX", 
 "l_postcode": null, 
 "r_postcode": null, 
 "link_id": 1186786776
 }, 
 {
 "geometry": {
 "type": "LineString", 
 "coordinates": [
 [
 -106.77742, 
 28.390159999999998
 ], 
 [
 -106.77806, 
 28.39076
 ]
 ]
 }, 
 "iso_code": "MEX", 
 "l_postcode": null, 
 "r_postcode": null, 
 "link_id": 818231403
 }, 
 {
 "geometry": {
 "type": "LineString", 
 "coordinates": [
 [
 -98.89940999999999, 
 18.90605
 ], 
 [
 -98.89926, 
 18.906689999999998
 ]
 ]
 }, 
 "iso_code": "MEX", 
 "l_postcode": null, 
 "r_postcode": null, 
 "link_id": 1130886811
 }
]

Answer 1

Do not use string formatting to pass parameters to SQL queries.

This way you are not only getting type conversion and quotes balancing/escaping issues, but also making your code vulnerable to SQL injections. Mandatory XKCD:

enter image description here

(source)

Instead, parameterize your query - put placeholders into the query and let your database driver worry about the Python to database type conversions.

In this case, you should be able to pass your data list of dictionaries directly to executemany() specifying dictionary-based query placeholders:

import psycopg2
from psycopg2.extras import Json
def insert_into_table(data):
 # preparing geometry json data for insertion
 for item in data:
 item['geom'] = Json(item['geometry'])
 with psycopg2.connect(database='testdb', user='postgres', password='password', host='localhost') as conn:
 with conn.cursor() as cursor:
 query = """
 INSERT into 
 data_load 
 (iso_code, l_postcode, r_postcode, link_id, geom) 
 VALUES 
 (%(iso_code)s, %(l_postcode)s, %(r_postcode)s, %(link_id)s, st_geomfromgeojson(%(geom)s));
 """
 cursor.executemany(query, data)
 conn.commit()

Also note how I'm using cursor and conn as context managers.

• Jorge Vidinha: Thanks alecxe . really appreciate the lesson. It makes all the sense much more clean and elegant as i was looking for ... unfortunaly there is something missing im getting a key error cursor.executemany(query, data) KeyError: 'geom' ... i suspect this is related to the postgis function beeing use 'st_geomfromgeojson'
• alecxe: @JorgeVidinha right, it should be geometry, fixed the placeholder, please try again. Thanks.
• Jorge Vidinha: tried ... but cursor.executemany(query, data) psycopg2.ProgrammingError: can't adapt type 'dict'
• alecxe: @JorgeVidinha yup, we need to dump the geometry to JSON - updated the code - should work now. Thanks!
• Jorge Vidinha: still not there yet , do i still have credits -- cursor.executemany(query, data) psycopg2.ProgrammingError: syntax error at or near ";" LINE 6: ..., 23.722279999999998], [-103.97212, 23.723139999999997]]}'); ^