wasp wasp - 2 months ago 8
Python Question

Sqlite3 Insertion does not retain records with Python

I am trying to insert multiple records to sqlite database. This is my test script.

import sqlite3
from datetime import datetime
company = 'fghjk'
keyword = 'awesome'
filename = 'test.txt'
date_found = datetime.now()
conn = sqlite3.connect('C:\\sqlite\\test.db')
c = conn.cursor()

for i in range(0,4):
insert_query = "INSERT into records (company,keyword,filename,date_found) values (?,?,?,?)"
c.execute(insert_query, [company,keyword,filename,date_found])

select_query = "SELECT * FROM records"
c.execute(select_query)
res = c.fetchall()
print res
for i in res:
for b in i:
print b


When I execute the script above twice, I am expecting to have 8 records, but it's always giving back 4 rows. Can someone enlighten me?

Output:

1
fghjk
awesome
test.txt
2016-09-23 09:11:19.585000
2
fghjk
awesome
test.txt
2016-09-23 09:11:19.585000
3
fghjk
awesome
test.txt
2016-09-23 09:11:19.585000
4
fghjk
awesome
test.txt
2016-09-23 09:11:19.585000


This is my table's create statement.

conn.execute('''CREATE TABLE records
# (ID INTEGER PRIMARY KEY AUTOINCREMENT,
# company TEXT NOT NULL,
# keyword INT NOT NULL,
# filename CHAR(50),
# date_found DATETIME);''')

cco cco
Answer

The problem looks to be that you're not committing the data to the database.

Each time you open the database, you put in 4 rows, then print them, then let the cursor and connection be destroyed without committing your changes.

Calling conn.commit() after all of your inserts are done will fix this. IIRC, the table creation is automatically committed immediately after it runs, which is why it is saved in the database.

Comments