CODEWITHSUNDEEP
pythonsqlite
doglas
doglas

Reputation: 115

Python + SQLite: How to make table columns using a list?

I want to make a sqlite table with a lot of columns.

The table and sqlite codes look like...

cur.execute('''CREATE TABLE MyTable
          (AAA  INTEGER PRIMARY KEY  AUTOINCREMENT,
           BBB  INTEGER,
           Col_1  TEXT,
           Col_2  TEXT,
           Col_3  TEXT,
           Col_4  TEXT,
           ........There are 1000x columns here......
           Col_1000  TEXT);
           ''')

Because there are so many columns, I use a list to generate the column names. 

SQL_Title_List = []
for i in range(1, 1001, 1):
    Temp = "Col_" + str(i)
    SQL_Title_List.append(Temp)

My question is, how to write the SQL codes professionally? It'll be very painful to write them manually.

Thanks for your help!

Upvotes: 0

Views: 825

Answers (1)

zmbq
zmbq

Reputation: 39023

Please don't do that. This is a very un-SQL thing to do. Instead you should create another table with three rows - main_row_id (a foreign key to the main table), col_number (the number 1 to 1000), value (the text field).

So instead of 1,000 columns per row, you have 1,000 records in the second table per row.

If, on the other hand, you need to store such table because it makes sense, you shouldn't use SQLite but rather something else. I would look at pandas.

Upvotes: 3

Related Questions