Being that string substitution is frowned upon with forming SQL queries, how do you assign the table name dynamically?

Solution 1:

Its not the dynamic string substitution per-se thats the problem. Its dynamic string substitution with an user-supplied string thats the big problem because that opens you to SQL-injection attacks. If you are absolutely 100% sure that the tablename is a safe string that you control then splicing it into the SQL query will be safe.

if some_condition():
   table_name = 'TABLE_A'
else:
   table_name = 'TABLE_B'

cursor.execute('INSERT INTO '+ table_name + 'VALUES (?)', values)

That said, using dynamic SQL like that is certainly a code smell so you should double check to see if you can find a simpler alternative without the dynamically generated SQL strings. Additionally, if you really want dynamic SQL then something like SQLAlchemy might be useful to guarantee that the SQL you generate is well formed.

Solution 2:

Composing SQL statements using string manipulation is odd not only because of security implications, but also because strings are "dumb" objects. Using sqlalchemy core (you don't even need the ORM part) is almost like using strings, but each fragment will be a lot smarter and allow for easier composition. Take a look at the sqlalchemy wiki to get a notion of what I'm talking about.

For example, using sqlsoup your code would look like this:

db = SQLSoup('sqlite://yourdatabase')
table = getattr(db, tablename)
table.insert(fieldname='value', otherfield=123)
db.commit()

Another advantage: code is database independent - want to move to oracle? Change the connection string and you are done.