Can't insert anything into sqlite3 Database using python

Question

Created this program to insert train number and name into a database. The names and numbers are right(as the commented out print statement proves it) but the db file is empty when I open it with db reader.

Code:

import sqlite3
import re
conn=sqlite3.connect('example.db')
c=conn.cursor()
c.execute('''CREATE TABLE train 
 (number text, name text)''')
f=open("train.htm","r")
html=f.read()
num=re.findall(r"(?<=> )[0-9]+", html) #regex to get train number
name=re.findall(r"(?<=<font>)[A-Za-z]+[ A-Za-z]+",html) #regex to get train name
j=8
for i in range(0,len(num)):
 #print(num[i],name[j]) #this statement proves that the values are right
 c.execute("INSERT INTO train VALUES (?,?)",(num[i],name[j]))
 j=j+3
conn.close()

But when I tried to read this database then its empty.

Code to read db:

import sqlite3
conn=sqlite3.connect('example.db')
c=conn.cursor()
for row in c.execute('SELECT * FROM train'):
 #the program doesn't even enter this block
 print(row)

I tried opening this databse in sqlitebrowser just to make sure, still its empty so there is something wrong with my first program which is unable to insert values. Why so?

Answer 1

You must call

conn.commit()

before

conn.close()

for the insertions to be committed. This is a Python/sqlite gotcha.

• svetaketu: Interesting link. In that code the author doesn't use a cursor object. Can I do away with cursor object and directly call db functions after opening database?
• unutbu: Using conn.execute is part of sqlite3's "nonstandard" API. I wouldn't recommend using it because your code will be more compatible with other database engines if you stick to the DB-API 2.0 interface.
• unutbu: It's also inefficient to use db.execute many times, since each call is creating, using, then throwing away a cursor.