CODEWITHSUNDEEP
pythonsqlsqlitesql-insert
Skyly83
Skyly83

Reputation: 75

SQLITE3 - Put Data in columns with a loop

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

Upvotes: 2

Views: 4652

Answers (3)

Parfait
Parfait

Reputation: 107587

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)

Upvotes: 0

virtualdvid
virtualdvid

Reputation: 2411

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.

Upvotes: 0

Hai Vu
Hai Vu

Reputation: 40723

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()

Upvotes: 2

Related Questions