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

Question

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!

There is something seriously wrong with your database design if you have a table with 1000 columns. — Daniel Roseman
– Daniel Roseman, Commented Dec 28, 2016 at 22:16
Are the column names predefined or you are reading from some file? — Mohammad Yusuf
– Mohammad Yusuf, Commented Dec 28, 2016 at 22:16
@MYGz The columns are new. The data should be imported from a text file. — doglas
– doglas, Commented Dec 28, 2016 at 22:22
@doglas: I have to concur with @DanielRoseman, this means your database design is seriously wrong. ESPECIALLY if they are all named Col_ + some number! — G. Sliepen
– G. Sliepen, Commented Dec 28, 2016 at 22:25

zmbq · Accepted Answer · 2016-12-28 22:22:37Z

3

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.

answered Dec 28, 2016 at 22:22

zmbq

39.1k15 gold badges109 silver badges188 bronze badges

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

2 Comments

zmbq Over a year ago

Not really. SELECTing thousands of records while JOINing tables is usually a problem.

doglas Over a year ago

Thanks! Pandas sounds good. I actually thought sqlite could do this task easily.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related