Inserting strings to a table in a loop

Question

Hi I'm trying to create tables in sqlite via sqlite3 in python3. The code I got so far is:

import sqlite3
text="hello"
db = sqlite3.connect('C:/DB.db')
c = db.cursor()
c.execute("CREATE TABLE %s (sida)" % name)
for i in range(10):
    c.execute("INSERT INTO %s VALUES (%s)" % (name, str(text))) # This does not work
db.commit()
db.close()
c.close

Changing the value from str(text) to i works;

c.execute("INSERT INTO %s VALUES (%s)" % (name, i))         # This works

Passing an integer as the value works but not a string. I've been trying to find a solution but I'm stuck.

The error I get is:

c.execute("INSERT INTO %s VALUES (%s)" % (name, str(text))) sqlite3.OperationalError: near "a2": syntax error

Tim Biegeleisen · Accepted Answer · 2019-06-13 08:13:09Z

1

You should be using a prepared statement for your inserts, binding values to any placeholders which exist:

c = db.cursor()
c.execute("CREATE TABLE %s (sida)" % name)
for i in range(10):
    c.execute("INSERT INTO %s VALUES (?)" % name, (str(text),))
db.commit()
db.close()
c.close

Actually, it would also be better to not make the table name itself dynamic.

answered Jun 13, 2019 at 8:13

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

5 Comments

Fred_Alb Over a year ago

Wow that was quick! Thank you Tim! I'll accept your answer when the timeout has passed

Tim Biegeleisen Over a year ago

Just for the record, the exact mode of failure in your query/code is that you have an unquoted string in the VALUES clause. You could fix it by just putting single quotes around name, but using a prepared statement is a much better fix.

Fred_Alb Over a year ago

I'll sneak in another question if you dont mind: c.execute("CREATE TABLE %s (sida)" % str(name) Doesn't accept if name consist of "/" or ":" or similar characters. Is there a way to allow this?

Tim Biegeleisen Over a year ago

Yes, put the table name in double quotes, e.g. CREATE TABLE "blah/stuff". But, I don't recommend using special characters in your table or column names, because you will forever have to escape them in double quotes.

Fred_Alb Over a year ago

Oh allright, the name of the table is dynamic as well (I left that part out in the original question). I'll poke around and see If I find something that fits my needs. Thanks again!

Collectives™ on Stack Overflow

Inserting strings to a table in a loop

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related