How can I insert data from this loop in sqlite

Question

I am trying to insert data from a loop that is similar to this.

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

words = ["apple", "banana", "cherry"]
for word in words:
     c.execute("insert into Words (word), values (?)",(word))
     print(word) 
     conn.commit

c.close()
conn.close()

Expected result is similar to this:

enter image description here

I am getting an error. But I am not sure how to correctly format this code right.

The error:

Traceback (most recent call last):
  File "file.py", line 7, in <module>
    c.execute("insert into Words (word), values (?)",(word))
sqlite3.OperationalError: near ",": syntax error

Nj Nafir · Accepted Answer · 2019-11-17 13:24:57Z

2

wrong list : commit() not commit because it is a method "insert into Words (word) values (?)",(word,) not "insert into Words (word), values (?)",(word)

right code is:

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

words = ["apple", "banana", "cherry"]
for word in words:
    c.execute("insert into Words (word) values (?)",(word,))
    print(word) 
conn.commit()
conn.close()

Don't worry, happy codding

answered Nov 17, 2019 at 13:24

Nj Nafir

5683 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ofer Sadan · Accepted Answer · 2019-11-17 13:26:13Z

0

I'm guessing you're getting an error because you can't place a single item in parenthesis to make it a tuple - so (word) isn't the way to go. It should be one of these (whichever one is more readable to you):

c.execute("insert into Words (word) values (?)",(word,))

Notice the comma inserted after word to make it a tuple. Or:

c.execute("insert into Words (word) values (?)",[word])

Which would make word the only element in a list

You can see for yourself in the console that doing something like ('hello') isn't equal to ('hello',). The first one remains a string, the second one is a tuple (which is what's needed in your command).

Edit: Also, you had a comma in that command that shouldn't be there

edited Nov 17, 2019 at 13:26

answered Nov 17, 2019 at 13:22

Ofer Sadan

12k6 gold badges42 silver badges66 bronze badges

2 Comments

ned Over a year ago

Still getting same error on both c.execute("insert into Words (word), values (?)",(word,)) sqlite3.OperationalError: near ",": syntax error

Ofer Sadan Over a year ago

@ned sorry i edited that, that comma isn't needed, try again with the updated code

Prayson W. Daniel · Accepted Answer · 2019-11-17 13:32:31Z

0

The easiest way is to use f-string:

c.execute(f"INSERT INTO Words (word), values ({word})")

This is much easier to read :) but requires python 3.6+

import sqlite3

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

    words = ["apple", "banana", "cherry"]
    for word in words:
         c.execute(f"INSERT INTO Words (word), values ({word})")
         print(word) 
         conn.commit()

edited Nov 17, 2019 at 13:32

answered Nov 17, 2019 at 13:29

Prayson W. Daniel

15.8k6 gold badges57 silver badges62 bronze badges

2 Comments

Ofer Sadan Over a year ago

this will work, but in my experience it's a really bad idea to use f-stings on sqlite commands (not to mention is will be really difficult for large numbers of values)

Prayson W. Daniel Over a year ago

Not in what I have tried as it leaves SQL code looking SQL. All the ? ? ? are easy to mix up.

Collectives™ on Stack Overflow

How can I insert data from this loop in sqlite

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related