add multiple columns to an sqlite database in python

Question

I want to create a table with multiple columns, say about 100 columns, in an sqlite database. Is there a better solution than naming each column individually? I am trying the following:

conn = sqlite3.connect('trialDB')
cur = conn.cursor()

listOfVars = ("added0",)
for i in range(1,100):
    newVar = ("added" + str(i),)
    listOfVars = listOfVars + newVar
print listOfVars

for i in listOfVars:
    cur.execute('''ALTER TABLE testTable ADD COLUMN ? TEXT''',(i,))

conn.commit()    
cur.close()
conn.close()

But I get the following error:

OperationalError: near "?": syntax error

Can someone please suggest how I can do this? Thanks!

Just to confirm SilentGhost - placeholders definitely do not work for column or table names. — Gretchen
– Gretchen, Commented Oct 21, 2010 at 14:06

mripard · Accepted Answer · 2010-10-21 13:56:37Z

6

I guess you could do it through string formatting, like this :

for i in listOfVars:
    cur.execute('''ALTER TABLE testTable ADD COLUMN %s TEXT''' % i)

But having 100 columns in a sqlite db is certainly not common, are you sure of having a proper db design ?

answered Oct 21, 2010 at 13:56

mripard

2,3662 gold badges14 silver badges11 bronze badges

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

1 Comment

Curious2learn Over a year ago

I am running some simulations, each of which outputs a data matrix with about 1500 rows and 200 columns. If I output the data to a .csv file it is about 3mb. I was wondering if using an sqlite database will make the whole thing efficient. Thanks for your answer.

Collectives™ on Stack Overflow

add multiple columns to an sqlite database in python

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related