Populating a PostgreSQL database with Python error -

i'm trying populate database, getting syntax error.

i have multiple lists data, several lists include lists.

injury_act_1 = ('2017-01-16 15:36:38','injury','unsafe act', 'true', 'false', 'while lifting 50 lb item floor onto wokrstation, employee felt sharp pain in lower back.','the employee ran out of room on workstation because takeaway conveyor inoperable') 

this repeats 10 times. there more lists action items injuries, audits, , action items audits.

i have function insert these database.

def populate():     params = config()     # connect postgresql server     conn = psycopg2.connect("dbname = safety")     cur = conn.cursor()      in range(len(injuries)):         incident = (             """             insert 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 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!")      j in range(len(audits)):         audit = (             """             insert 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 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 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 @ or near "15" line 12:        2017-01-16 15:36:38,                             ^ 

take step here , @ how you'e forming query. try avoid using string concatenation query building, , user-supplied input of kind. it's not bug-prone (as have found) a security nightmare.

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

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

so lets psycopg2 take care of escaping , formatting.

you can write out 7 literal %ss, %s, %s, %s, ... if want. prefer above form.

this way if tricks app accepting string in injuries, ');drop table incident;--, won't in such trouble.