CODEWITHSUNDEEP
pythonpython-3.x
user3030327
user3030327

Reputation: 451

How to insert data from a list to SQLite3 Database

This is a list below imported the data from qwidgets.

list = ['2020/02/21', 'name', 'location', 'item1', 'item2', 'item3', 'item4', 'item5']

or

list = ['2020/02/21', 'name', 'location', 'item1']

or

list = ['2020/02/21', 'name', 'location', 'item1', 'item2', 'item3']

How to insert this list data in to SQLite3 database. I am using below database structure. 

conn = sqlite3.connect('test.db')
conn.execute("INSERT INTO \
    maintable(date,name,location,item1,item2,item3,item4,item5,item6, item7,item8, item9, item10) \
    VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')

Upvotes: 1

Views: 121

Answers (2)

jignatius
jignatius

Reputation: 6484

It's better and safer to use placeholders (?) wherever you want to use a value to protect against SQL injection attacks.

Since the size of the list can vary you need to build it dynamically. You can do that like this:

l = ['2020/02/21', 'name', 'location', 'item1', 'item2',...]
query = "INSERT INTO maintable(date,name,location"

for i in range(len(l) - 3):
    query += ',item{}'.format(i+1)

query += ") VALUES (?, ?, ?"

for i in range(len(l) - 3):
    query += ', ?'.format(i+1)
query += ')'

conn.execute(query, l)

Make sure you supply all the values for the placeholders and remember to commit and close the connection to save the changes:

conn.commit()
conn.close()

Upvotes: 2

Serge Ballesta
Serge Ballesta

Reputation: 148900

You should never build a request by adding parameter values into the query string. I used to be the vector for the old SQL injection attack that allowed an attacker to pass arbitrary SQL commands by simply forging a parameter value.

The recommended way is to used a parameterized query.

Here you should simply do:

conn.execute("INSERT INTO \
    maintable(date,name,location,item1,item2,item3,item4,item5,item6, item7,item8, item9, item10) \
    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)", lst + [None] * (13 - len(lst)))

BTW I renamed your list variable lst to avoid hiding the built-in list function.

Upvotes: 2

Related Questions