SQLite add multiple columns using for loop

Question

I am trying to create a SQL database which will have many columns (around 4030). Therefore I cannot use

ALTER TABLE table_name ADD column_name

for every column. Now I am trying to do multiple ALTER TABLE operation but I know that, there's no loop in the ADD branch so no repetition is allowed. So I tried another way to implement my table. I tried,

c.execute('''ALTER TABLE table_name ADD (?) ''',k) #k changes in every iteration

The code that I wrote in below.

import scipy.io
import sqlite3
import os
# another irrelevant codes here...

conn  = sqlite3.connect('CANBUS.db') #connection to .db
matFile = scipy.io.loadmat('folder/table.mat') #column names comes from here
c = conn.cursor() #cursor function I do not know that function well

c.execute('''CREATE TABLE IF NOT EXISTS table_name (ID integer PRIMARY KEY)''')

for k in matFile:
            c.execute('''ALTER TABLE table_name ADD (?) ''',k) #k changes in every loop

I encounter an error which is basically a syntax error, I understood that in SQLite not allowed to do ALTER TABLE table_name ADD (?),k command.

The message in the console was this. How can I add multiple columns in a single command?

       c.executemany('''ALTER TABLE table_name ADD (?) ''',k)

OperationalError: near "(": syntax error

Thank you for your advices.

Why are you building your table in this way? Also, are you aware that sqlite defaults to a maximum of 2000 columns? You'll need to update that limit. — roganjosh, Dec 18 '17 at 08:03
But genuinely... 4030 columns? Are you sure that this is a good format for your data? What are you trying to do? — roganjosh, Dec 18 '17 at 08:06
Actually, I have 4030 different signals and each day, value of the signals are changing and new values are appending so I thought it was a best way to create table. — mert hayta, Dec 18 '17 at 08:13

score 2 · Accepted Answer · answered Dec 18 '17 at 08:08

Only your first mention of the ALTER TABLE command is correct: you must not use parentheses.

And it is not possible to use parameters for table/column names. You have to put the column name directly into the SQL command string (and quote it if it is not a valid identifier):

for k in matFile:
    c.execute("ALTER TABLE table_name ADD " + k)

And instead of executing one command for each column, you could just construct the CREATE TABLE command with all columns:

sql = "CREATE TABLE tab (ID INTEGER PRIMARY KEY," + ",".join(matFile) + ")"

And the default limit on the number of columns is 2000, so you will not be able to do this anyway. You should properly normalize your database structure.

Thank you for your solution. Both 2 ways worked well. Now I am trying to reduce the columns for efficiency. — mert hayta, Dec 18 '17 at 08:22

Serge Ballesta · Answer 2 · 2017-12-18T08:28:20.120

SQL has the notion Data Definition Language vs. Data Management Language (ref.). All statements related to tables, indexes or other metadata elements are Data Definition Language instructions, when date insertion, modification, extraction or suppression are DML ones. Parameterized queries can only be used in DML not in DDL. That's the reason why you must build your ALTER TABLE (or CREATE TABLE) command as a string and execute that string, when you have been told many times that you shall always use parameterized queries in SQL. But that rules only applies to DML.

BTW, ALTER TABLE can be an expensive operation and you should not put it in a loop: put all columns in one single CREATE TABLE instruction as shown in CL.'s answer.

But if you need more than 2000 columns you should use a different design for your database. In fact one hundred is already too much and is a sign of a bad design.

A common pattern would be to design the table like that: KEY, TYPE, VALUE, (eventually other columns like DATE, INSERT_DATE...). Here you could have KEY, SIGNAL_ID, VALUE, ...

In SQLite, ALTER TABLE never is expensive (because it cannot do much). — CL., Dec 18 '17 at 11:32

SQLite add multiple columns using for loop

2 Answers2