Create a new SQLite table in python with for-loop

Question

Say I have 100 different integers I want to store like a row with 100 columns.

I am trying it like this:

db = sqlite3.connect("test.db")
c = db.cursor()
c.execute('''
    CREATE TABLE IF NOT EXISTS nums(
        id INTEGER PRIMARY KEY, 
''')
for i in range(100):
    c.execute('''
    ALTER TABLE nums
    ADD ''' + 'column_' + i + '''INTEGER''')

db.commit()

Someone told me that when you are using numbers as column names you could probably do it a better way. But if I for example have a list with strings in python, and I want to loop through them and store every individual string in its own column, the approach would be the same, right?

However, this code runs without errors for me, but no new table is created, how come?

Also you need a space between i and INTEGER in your ATLER statement — IoaTzimas
– IoaTzimas, Commented Jan 2, 2021 at 12:52
"Someone" is absolutely right. If you have a set of names created by a number sequence, you are probably not following best practices. But in your cases the problem sits a little deeper: the wrong approach is in your own requirements already: "I want to […] store every individual string in its own column". — Klaus D.
– Klaus D., Commented Jan 2, 2021 at 12:56

IoaTzimas · Accepted Answer · 2021-01-02 12:56:50Z

1

Your ALTER statement is incorrect as it's missing the COLUMN after ADD. You can use the following:

for i in range(100):
    c.execute(f'ALTER TABLE nums ADD COLUMN column_{i} INTEGER')

answered Jan 2, 2021 at 12:56

IoaTzimas

10.7k2 gold badges15 silver badges32 bronze badges

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

Collectives™ on Stack Overflow

Create a new SQLite table in python with for-loop

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