Inserting DB name into Sqlite3 command

Question

I'm using python 2.7.3 and Sqlite3 to save some data in a small db. I have the following sql command:

thedb = "allpeople"
cursor.execute("INSERT INTO %s VALUES (?,?,?,?,?,?,?,?,?,?,?,?)" % (thedb, data))
conn.commit()

But its throwing the following error:

TypeError: not all arguments converted during string formatting

At the very least, you should put a %r instead of %s, or you're gonna have problems if the table name contains special characters. — michaelmeyer
– michaelmeyer, Commented Jul 15, 2013 at 15:20

Martijn Pieters · Accepted Answer · 2013-07-15 15:07:52Z

1

You are trying to insert the table name (not the database). You appear to be mixing that up with SQL parameters; string templating and providing SQL parameters for your query are two entirely separate operations.

You'd have to use string formatting separately to build the query string:

cursor.execute("INSERT INTO %s VALUES (?,?,?,?,?,?,?,?,?,?,?,?)" % thedb, data)

or perhaps a little clearer to illustrate what is going on:

query = "INSERT INTO %s VALUES (?,?,?,?,?,?,?,?,?,?,?,?)" % thedb
cursor.execute(query, data)

Note that this opens you up to a possible SQL injection vector. Perhaps you should look into a decent SQL library instead; SQLAlchemy lets you build SQL queries from Python calls (among other tasks).

answered Jul 15, 2013 at 15:07

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

Collectives™ on Stack Overflow

Inserting DB name into Sqlite3 command

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related