JTP709
JTP709

Reputation: 130

Populating a PostgreSQL database with Python error

I'm trying to populate a database, but am getting a syntax error.

I have multiple lists with data, with several lists that include those lists.

injury_act_1 = ('2017-01-16 15:36:38','Injury','Unsafe Act', 'TRUE', 'FALSE', 'While lifting a 50 lb item from the floor onto their wokrstation, the employee felt a sharp pain in their lower back.','The employee ran out of room on their workstation because the takeaway conveyor was inoperable')

This repeats for 10 times. There are more lists for action items for those injuries, audits, and action items for those audits.

I have a function to insert these into my database.

def populate():
    params = config()
    # connect to the PostgreSQL server
    conn = psycopg2.connect("dbname = safety")
    cur = conn.cursor()

    for i in range(len(injuries)):
        incident = (
            """
            INSERT INTO incident (
                            date_time,
                            incident_type,
                            incident_cat,
                            injury,
                            property_damage,
                            description,
                            root_cause
                            )
                VALUES (
                    """+ injuries[i][0] +""",
                    """+ injuries[i][1] +""",
                    """+ injuries[i][2] +""",
                    """+ injuries[i][3] +""",
                    """+ injuries[i][4] +""",
                    """+ injuries[i][5] +""",
                    """+ injuries[i][6] +"""
            """)

        cur.execute(incident)
        print("Injury case added!")

        action_items = (
            """
            INSERT INTO action_items (
                            case_id,
                            finding,
                            corrective_action
                            )
                VALUES (
                    """+ (i+1) +""",
                    """+ injuries[i][4] +". "+ injuries[i][5] +""",
                    """+ actions[i] +""",
                    )

            """
            )

        cur.execute(action_items)
        print("Action item added!")

    for j in range(len(audits)):
        audit = (
            """
            INSERT INTO audit (
                            date_time,
                            type,
                            que_1,
                            que_2,
                            que_3,
                            ans_1,
                            ans_2,
                            ans_3,
                            )
                VALUES (
                    """+ str(audits[i][0]) +""",
                    """+ audits[i][1] +""",
                    """+ audits[i][2] +""",
                    """+ audits[i][3] +""",
                    """+ audits[i][4] +""",
                    """+ audits[i][5] +""",
                    """+ audits[i][6] +""",
                    """+ audits[i][7] +"""
            """
            )

        cur.execute(audit)
        print("Audit added!")

        action_items_a = (
            """
            INSERT INTO action_items (
                            audit_id,
                            finding,
                            corrective_action
                            )
                VALUES (
                    """+ (i+1) +""",
                    'Audit deficiency',
                    """+ actions_a[i] +""",
                    )
            """
            )

        cur.execute(action_items_a)
        print("Action item added!")

    cur.close()
    conn.commit()

populate()

I keep getting this error:

Traceback (most recent call last):
    File "database_populator.py", line 204, in <module>
        populate()
    File "database_populator.py", line 137, in populate
        cur.execute(incident)
psycopg2.ProgrammingError: syntax error at or near "15"
Line 12:        2017-01-16 15:36:38,
                            ^

Upvotes: 0

Views: 96

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324325

Take a step back here and look at how you'e forming the query. Try to avoid using string concatenation for query building, and especially for user-supplied input of any kind. It's not only bug-prone (as you have found) but a security nightmare.

Your code should use psycopg2's bind parameter support, looking more like:

incident = (
            """
            INSERT INTO incident (
                            date_time,
                            incident_type,
                            incident_cat,
                            injury,
                            property_damage,
                            description,
                            root_cause
                            )
                VALUES (""" + (["%s"] * 7).join(", ") + ")"
    cur.execute(incident, injuries)

so that it lets psycopg2 take care of the escaping and formatting.

You can write out seven literal %ss, like %s, %s, %s, ... if you want. I just prefer the above form.

This way if someone tricks your app into accepting a string in injuries, like say ');DROP TABLE incident;--, you won't be in such trouble.

Upvotes: 1

DYZ
DYZ

Reputation: 57033

If you still want to construct the query string yourself (which is a bad idea), enclose dates in quotation marks:

"""
    ....
    VALUES (
                '"""+ injuries[i][0] +"""',
                '"""+ injuries[i][1] +"""',
                '"""+ injuries[i][2] +"""',
                '"""+ injuries[i][3] +"""',
                '"""+ injuries[i][4] +"""',
                '"""+ injuries[i][5] +"""',
                '"""+ injuries[i][6] +"""'
        """)

Still better, construct the query with a list comprehension:

"""
    ....     
    VALUES(""" + ",".join("'{}'".format(injury) for injury in injuries[i]) + ")"

Upvotes: 1

Related Questions