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?

unutbu · Accepted Answer · 2014-06-28 19:17:17Z

7

You must call

conn.commit()

before

conn.close()

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

answered Jun 28, 2014 at 19:17

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

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

3 Comments

svetaketu Over a year ago

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 Over a year ago

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 Over a year ago

It's also inefficient to use db.execute many times, since each call is creating, using, then throwing away a cursor.

Collectives™ on Stack Overflow

Can't insert anything into sqlite3 Database using python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related