SQLITE3 - Put Data in columns with a loop

Question

I am using SQLITE3.

In my SQL Table, I have a table of 50 columns, and I would like to put in each column each value of My_List, which has 50 elements.

Is there any way to code a loop in python to put my data in my table ? I tried to find it out but didn't get anything...

My current code for 3 variables instead of 50 is:

import sqlite3
conn = sqlite3.connect("testdatabase.db")
c.execute('''CREATE TABLE mytable (Column1 text, Column2 text, Column3, 
text) ''')
c.execute('''INSERT INTO mytable (Column1, Column2, Column3) VALUES (?, 
?, ?)''', (myliste[0], myliste[1], myliste[2])

conn.commit()

Thank you very much.

Lcs

Would you please show what your input looks like (may be scale down to 2-3 columns) what you want the output to look like and what you have tried so far. People of StackOverflow will be more likely to help if your question is clear and you showed some effort solving your own problem. — Hai Vu, Dec 23 '17 at 21:55

Hai Vu · Accepted Answer · 2017-12-23T23:02:05.627

2

I see what you are trying to do. You almost have it. What you have is writing one row of data. just put that into a loop and you can write the whole table:

import sqlite3

conn = sqlite3.connect("testdatabase.db")
conn.execute("CREATE TABLE mytable (Column1 text, Column2 text, Column3 text)")

mytable = [
    ('a', 'b', 'c'),
    ('d', 'e', 'f'),
]

for myliste in mytable:
    conn.execute("""INSERT INTO
            mytable (Column1, Column2, Column3)
            VALUES (?, ?, ?)""",
        myliste)

conn.commit()

Update

To create 50 columns, if you have a list of columns already, replace the variable columns below with your own:

conn = sqlite3.connect("testdatabase.db")
conn.execute('DROP TABLE IF EXISTS mytable')

# Create ['Column1', 'Column2', ..., 'Column50']
columns = ['Column%d' % n for n in range(1, 51)]

# Create 'Column1 TEXT, ... Column50 TEXT'
columns_declaration = ', '.join('%s TEXT' % c for c in columns)

conn.execute("CREATE TABLE mytable (%s)" % columns_declaration)

conn.commit()

edited Dec 23 '17 at 23:02

answered Dec 23 '17 at 22:13

Hai Vu

37,849
11
66
93

Thank you very much. But how can I do if I have like 50 columns ? Do I have to write ... mytable(Column1, Column2....) and so on until Column50? – Skyly83 Dec 23 '17 at 22:19
Do you have a list of column names somewhere? – Hai Vu Dec 23 '17 at 22:44
Yes, I didn't put the entire code in my previous comment because it would be too long, but I did enter each of my 50 columns just before. Is there a way to refer to the number of the column in my table ? Like for the list in python for instance: mytable[3] to get the third column – Skyly83 Dec 23 '17 at 22:49

score 0 · Answer 2 · answered Dec 24 '17 at 01:20

I answered a similar questions in this post I recommended to create a csv file and then use a bulk insert instead of using insert into because row by row is really slow, and with this method you don't need to worry about the number of columns or rows. I did it for sql server but I am pretty sure it will work in sqlite.

score 0 · Answer 3 · answered Dec 24 '17 at 05:24

In SQL, you can omit the named columns in INSERT INTO assuming every column is being appended and values include data for all columns aligned to same table order.

Then consider dynamically building the placeholders for paramterization:

placeholders = ', '.join(['?'] * 50)

c.execute('''INSERT INTO mytable VALUES ({})'''.format(placeholders), mylist)

SQLITE3 - Put Data in columns with a loop

3 Answers3

Update