CODEWITHSUNDEEP
pythonlistsqlite
John
John

Reputation: 3

SQLite3 - Inserting list items in table

I'm using python 3.5 and am trying to insert list elements into a table.

The lists are already defined:

list1 = [1,2,3...]
list2 = [1,2,3...]
list3 = [1,2,3...]
list4 = [1,2,3...]
list5 = [1,2,3...]

Connecting to the database :

conn = sqlite3.connect('database.db')
c = conn.cursor()

Create a table, Table1, with 5 columns:

def create_table():
     c.execute("CREATE TABLE IF NOT EXISTS Table1(Column1 TEXT, Column2 TEXT, Column3 TEXT,
Column4 TEXT, Column5 TEXT)")

And adding the list elements to the table:

def data_entry():
    Column1 = list1
    Column2 = list2
    Column3 = list3
    Column4 = list4
    Column5 = list5
    c.execute("INSERT INTO Master (Column1, Column2, Column3, Column4, Column5)
VALUES (?, ?, ?, ?, ?)", (Column1, Column2, Column3, Column4, Column5))
    conn.commit()

c.close()
conn.close()

When I run it, I get:

ProgrammingError: Incorrect number of bindings supplied. The current statement uses 5, and there are 150 supplied.

Would a loop be more appropriate to use to insert each item list as a new row in the specified column? If so, how do I create a loop for sqlite3?

It's the first time I'm using sqlite3, so any suggestions are much appreciated. Thank you!

Upvotes: 0

Views: 5764

Answers (2)

rolika
rolika

Reputation: 391

You can use some shortcut methods, it's more efficient:

import sqlite3

list1 = [1, 2, 3]
list2 = [4, 5, 6]
list3 = [7, 8, 9]

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE master(col1, col2, col3)")
con.executemany("INSERT INTO master(col1, col2, col3) VALUES (?, ?, ?)", (list1, list2, list3))
for row in con.execute("SELECT col1, col2, col3 FROM master"):
    print(row)

Upvotes: 1

Nasef Khan
Nasef Khan

Reputation: 232

You'll have to loop over the lists (assuming they are the same length):

for i in range(len(list1)):
    c.execute("INSERT INTO Master (Column1, Column2, Column3, Column4,Column5)"
              " VALUES (?, ?, ?, ?, ?)",
              (list1[i], list2[i], list3[i], list4[i], list5[i]))

Upvotes: 1

Related Questions