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:

(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.