4

I have a python script that should insert a list into a sqlite table. It appears my insert statement is not working. 

links = ['a', 'b', 'c']

conn = sqlite3.connect('example.db')

#create a data structure
c = conn.cursor()

#Create table
c.execute('''Create TABLE if not exists server("sites")''')

#Insert links into table
def data_entry():
    sites = links
    c.execute("INSERT INTO server(sites) VALUES(?)", (sites))
    conn.commit()

#query database
c.execute("SELECT * FROM server")
rows = c.fetchall()
for row in rows:
    print(row)

conn.close

I checked the database at command line but the "server" table is empty:

C:\App\sqlite\sqlite_databases>sqlite3
SQLite version 3.17.0 2017-02-13 16:02:40
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .tables
server
sqlite> SELECT * FROM server
...> ;
sqlite>

So it doesn't appear that the list is actually being inserted.

Improve this question
1
  • you never call data_entry() and you are missing () on the last line (sholud be conn.close()); also, rename variable list to something like list_, but don't use reserved words Commented Apr 5, 2017 at 20:06

2 Answers 2

Reset to default
6

Iterate over list_ and execute INSERT for each item. And call data_entry() to actually insert data.

import sqlite3

list_ = ['a', 'b', 'c']

#create a data structure
conn = sqlite3.connect('example.db')
c = conn.cursor()

#Create table
c.execute('''Create TABLE if not exists server("sites")''')

#Insert links into table
def data_entry():
    for item in list_:
        c.execute("INSERT INTO server(sites) VALUES(?)", (item,))
    conn.commit()

data_entry()  # ==> call the function

#query database
c.execute("SELECT * FROM server")
rows = c.fetchall()
for row in rows:
    print(row)

conn.close()
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

(item) should be (item,) to make it a tuple.
4

Make a 2-dimensional list and use executemany().

links = ['a', 'b', 'c']

conn = sqlite3.connect('example.db')

#create a data structure
c = conn.cursor()

#Create table
c.execute('''Create TABLE if not exists server("sites")''')

#Insert links into table
def data_entry(links):
    sites = [(s,) for s in links]
    c.executemany("INSERT INTO server(sites) VALUES(?)", sites)
    conn.commit()

data_entry(links)

#query database
c.execute("SELECT * FROM server")
rows = c.fetchall()
for row in rows:
    print(row)

conn.close
Improve this answer

Comments