0

I'm using SQLite3 in Python to build a database and tables. I want to create a table according to the value of a variable (name: A). I found this (answer) and it says "Unfortunately, tables can't be the target of parameter substitution". And the full table name should be like "table_"+str(A). I tried several methods:

new_field = 'my_1st_column'
field_type = 'INTEGER'
tablename = "table_"+str(A)
databasecur.execute('CREATE TABLE IF NOT EXISTS {tn} ({nf} {ft})' .format(tn=tablename, nf=new_field, ft=field_type))

or

databasecur.prepare("CREATE TABLE IF NOT EXISTS" + tablename + "(col1, col2)")

All of them are not working. So right now the way I can figure out is like:

A=1140
tablelist = [1140, 1150, 1160, 1170,....]
tablenum = tablelist.index(A)
con = sqlite3.connect('test.db')
cur = con.cursor()
if tablenum == 0:
    cur.execute("create table if not exists table_1140 (col1, col2)")
elif tablenum == 1:
    cur.execute("create table if not exists table_1150 (col1, col2)")
......

con.close()

Does anyone have more effective way to do this? Thank you very much.

Community
  • 1
  • 1
Zhentao
  • 140
  • 1
  • 10
  • What do you mean when you say "not working"? Do you get an error? Does anything happen? – asongtoruin May 12 '17 at 09:12
  • Err... is `A` in your first example actually just `1140` or `table_1140`? (and your 2nd one won't work as there's no space between EXISTS and the table name itself anyway...) – Jon Clements May 12 '17 at 09:15
  • 1
    Also - why would you want multiple tables here? Is there any reason you can't have one table, with a column that's one of your (1140, 1150, 1160, 1170) values with an index on it? – Jon Clements May 12 '17 at 09:16
  • Actually, variable A is a part of table name. The full name should be "table_"+str(A). That's why I want multiple tables. – Zhentao May 12 '17 at 09:45
  • I change the code which may be more clear. – Zhentao May 12 '17 at 09:49
  • @Zhentao and how does it not work? – Jon Clements May 12 '17 at 09:50
  • @Jon Clements I test those codes again. If I use "databasecur.prepare(...)", the error is "AttributeError: 'sqlite3.Cursor' object has no attribute 'prepare' " And if I use the first method, it works now. I don't know why it did work before. – Zhentao May 12 '17 at 14:48

0 Answers0