Getting Started with Python SQLite3

• SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language.
• The sqlite3 module was written by Gerhard Häring.
• It provides a SQL interface compliant with the DB-API 2.0 specification
• The Python Standard Library includes a module called “sqlite3” intended for working with this database.
• DB Browser for SQLite (DB4S) is a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite. Download from https://sqlitebrowser.org/

Steps to create db file, table and execute queries

• Import sqlite3
• Create database and connect to the database
• Create the cursor object using the connection object
• Execute the statements (create table, insert data, update data, drop table etc.,)
• Commit the transaction

Import, Connect, Cursor Object, Create Table, Insert, Commit

>>> import sqlite3
>>> con = sqlite3.connect('onlinetraining.db')
>>> cur = con.cursor()
>>> cur.execute("create table courses(id integer primary key, name text, fee integer)")
<sqlite3.Cursor object at 0x03788B60>
>>> con.commit()
>>> cur.execute('insert into courses(id,name,fee) values (1,"Python",300)')
<sqlite3.Cursor object at 0x03788B60>
>>> cur.execute('insert into courses(id,name,fee) values (2,"Angular",200)')
<sqlite3.Cursor object at 0x03788B60>
>>> con.commit()
>>> cur.execute('select * from courses')
>>> rows = cur.fetchall()
>>> for row in rows:
...     print(row)
...
(1, 'Python', 300)
(2, 'Angular', 200)

Use DB Browser for SQLite to open the database and see the tables data as shown below

Update and Select statements

To fetch the data from a database execute select statement and then use fetchall() method of the cursor object and assign the values to a variable and then loop through the variable and print all values.

>>> cur = con.cursor()
>>> cur.execute('update courses set name = "Advanced Python" where id = 1')
<sqlite3.Cursor object at 0x03A1E160>
>>> con.commit
<built-in method commit of sqlite3.Connection object at 0x039066A0>
>>> cur.execute('select * from courses')
<sqlite3.Cursor object at 0x03A1E160>
>>> rows = cur.fetchall()
>>> for row in rows:
...     print(row)
...
(1, 'Advanced Python', 300)
(2, 'Angular', 200)

Drop statement

• To list all tables in a SQLite3 database, we can query sqlite_master table which stores all the tables, then use fetchall() to fetch the results from the select statement.
• Use drop table if exists <table name>to drop the table

>>> cur.execute('create table test(id integer primary key, name text)')
<sqlite3.Cursor object at 0x03A1E160>
>>> con.commit()
>>> cur.execute('select name from sqlite_master where type="table"')
<sqlite3.Cursor object at 0x03A1E160>
>>> tables = cur.fetchall()
>>> for tab in tables:
...     print(tab)
...
('courses',)
('test',)
>>> cur.execute('create table test1(id integer primary key, name text)')
<sqlite3.Cursor object at 0x03A1E160>
>>> con.commit()
>>> cur.execute('select name from sqlite_master where type="table"')
<sqlite3.Cursor object at 0x03A1E160>
>>> tables = cur.fetchall()
>>> for tab in tables:
...     print(tab)
...
('courses',)
('test',)
('test1',)
>>> cur.execute('drop table if exists test1')
<sqlite3.Cursor object at 0x03A1E160>
>>> con.commit()
>>> cur.execute('select name from sqlite_master where type="table"')
<sqlite3.Cursor object at 0x03A1E160>
>>> tables = cur.fetchall()
>>> for tab in tables:
...     print(tab)
...
('courses',)
('test',)
>>> con.close()
>>> cur.execute('select * from courses')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.ProgrammingError: Cannot operate on a closed database.

References

• https://docs.python.org/2/library/sqlite3.html#module-sqlite3
• https://wiki.python.org/moin/DatabaseProgramming
• https://docs.python.org/3/library/sqlite3.html

Learn more about Python articles in our upcoming Python Blog Articles.

Happy Learning!