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

Question

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

Serge Ballesta · Accepted Answer · 2021-03-19 09:36:34Z

2

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

edited Mar 19, 2021 at 9:36

answered Mar 19, 2021 at 8:52

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stephen Sander Over a year ago

Thanks I tried but got syntax error at the first comma after ?.

Stephen Sander Over a year ago

Thanks! That syntax works if all the values in the row are integers, but now I have the same problem, where if an entry is a string, it brings it into the INSERT statement as a string without quotations. the cols variable is passing the appropriate data type (string if the entry is a string, int if it's an int) at table creation so that isn't the issue.

Stephen Sander Over a year ago

Nvm it works just had to change [:15] to [:16] thanks for taking the time to look at it!

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related