5
\$\begingroup\$

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
    }
]
\$\endgroup\$
7
\$\begingroup\$

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.

|improve this answer|||||
\$\endgroup\$
  • 1
    \$\begingroup\$ 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' \$\endgroup\$ – Jorge Vidinha Jul 13 '17 at 15:39
  • \$\begingroup\$ @JorgeVidinha right, it should be geometry, fixed the placeholder, please try again. Thanks. \$\endgroup\$ – alecxe Jul 13 '17 at 15:46
  • \$\begingroup\$ tried ... but cursor.executemany(query, data) psycopg2.ProgrammingError: can't adapt type 'dict' \$\endgroup\$ – Jorge Vidinha Jul 13 '17 at 15:47
  • \$\begingroup\$ @JorgeVidinha yup, we need to dump the geometry to JSON - updated the code - should work now. Thanks! \$\endgroup\$ – alecxe Jul 13 '17 at 15:51
  • \$\begingroup\$ 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]]}'); ^ \$\endgroup\$ – Jorge Vidinha Jul 13 '17 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.