How do i insert a list into a SQLite database

Question

I am trying to insert a list into my SQLite database but it keeps coming up with sqlite3.OperationalError: near "in": syntax error.

The list generates fine but it just won't insert.

I am trying to get the list to show up in a single column in the table as the smoking fox x 4 the chicken tinga torta x 2

Here's my code for inserting the list

order_list = []

for sandwich in order_details:
    if sandwich[0] == 1:
        order_list.append("The Smoking fox x " + str(sf))
    if sandwich[0] == 2:
        order_list.append("The Chicken Tinga Torta x " + str(ct))
    if sandwich[0] == 3:
        order_list.append("The Italo Disco Club x " + str(id))
    if sandwich[0] == 4:
        order_list.append("The Bomb x " + str(bo))
    if sandwich[0] == 5:
        order_list.append("Falafel burger  x " + str(fa))
    
print(order_list)

sql = """insert in orders (order,) values(?,)"""
cur.execute(sql, (order_list,))
con.commit()

flash("Order submitted")

Does this https://stackoverflow.com/questions/20444155/python-proper-way-to-store-list-of-strings-in-sqlite3-or-mysql help? — Rolf of Saxony, Sep 11 '22 at 07:48
not really, as im quite new to using python with sql and dont really understand the answers but thanks anyways — jakobmja, Sep 11 '22 at 07:55

flyingfox · Answer 1 · 2022-09-11T07:32:27.027

1

Seems a typo

You need to use INSERT INTO not INSERT IN

Change insert in orders (order,) values(?,) into insert into orders (order,) values(?,)

Update: Since order is a key word in sql,you need to escape it,maybe something as below

insert into orders (`order`) values(?)

You can have a try,since I am not very good at sqlite

edited Sep 11 '22 at 07:32

answered Sep 11 '22 at 07:08

flyingfox

13,414
3
24
39

now im getting this error sqlite3.OperationalError: near "order": syntax error – jakobmja Sep 11 '22 at 07:20
@jakobmja `order` is a key word in sql,you need to escape it or use a new column name – flyingfox Sep 11 '22 at 07:23
now im getting sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type. – jakobmja Sep 11 '22 at 07:31

score 0 · Accepted Answer · answered Sep 11 '22 at 17:52

If you look at the link I provided you'll see a very simple answer from Joshua: '|'.join(list)

You can translate that to work for you, with:

order_text = '|'.join(order_list)
sql = """insert into orders (order,) values(?,)"""
cur.execute(sql, (order_text,))

This will insert your list as text in a form like:

'The Smoking fox x 4|The Bomb x 1'

When you extract the order out of the database table you will need to split it on the | character

>>> order.split('|')
['The Smoking fox x 4', 'The Bomb x 1']

You have your list returned from the database.

In essence, convert the list into a delimited text string, insert into the database. To use it, extract from database, convert it back into a list via the split function.

How do i insert a list into a SQLite database

2 Answers2