How do i insert a list into a SQLite database-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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.