CODEWITHSUNDEEP
pythonpostgresqlcsv
Stephen Sander
Stephen Sander

Reputation: 61

Can't use string when importing row data from csv to postgresql

as part of an exercise I have to insert one row from a csv per second into my postgres db. Everything works fine as long as none of my rows contain strings. I'm wondering how I can make only my strings have ' ' when adding the variable to my INSERT statement.

with open(file_path, newline='') as csvfile:
  reader = csv.reader(csvfile)
  next(csvfile)
  for row in reader:
    print(row)
    cursor.execute("INSERT INTO %s (%s) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" % (tbl_name, cols, row[0], row[1], row[2], row[3], row[4],row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15]))
    time.sleep(1)

Thanks in advance

Upvotes: 0

Views: 31

Answers (1)

Serge Ballesta
Serge Ballesta

Reputation: 149085

You should never build a query containing values as a full string(*), but use a parameterized query

cursor.execute("INSERT INTO %s (%s) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
    % (tbl_name, cols), row[:15]))

But some engines do not like ?, in that case, you can use %s:

cursor.execute("INSERT INTO %s (%s) VALUES (%%s,%%s,%%s,%%s,%%s,%%s,%%s,%%s,%%s,%%s,%%s,%%s,%%s,%%s,%%s)"
    % (tbl_name, cols), row[:15]))

This pattern is deprecated for ages because it was the cause of SQL injection attacks...

Upvotes: 2

Related Questions