23  Python: SQLite DB

23.1 Overview

  • In this chapter, we will manage SQLite database from the Python environment

  • Create an SQLite database, and disconnect the connection

  • Set up a connection to read an SQLite database

  • Explores the tables in the database

  • Create and remove tables from the database

  • Execute DML commands on the records of a table: INSERT, UPDATE, DELETE

23.2 Create SQLite RDBMS

  • We will create an SQLite RDBMS containing four tables: tblClass, tblTeacher, tblStudent, tblMarks

  • Choose one of the following three options to create an SQLite RDBMS.

23.2.1 Using SQLite

  • Go to SQLite website, download and install the program for your system.

  • Create the SQLite database on the SQLite environment

SQLite Website

23.2.2 Using DB Browser

  • A nice user interface to create SQLite database is DB Browser. It has a portable version for Windows (no installation required).

  • Run DB Browser and create the SQLite database

SQLite Website

23.2.3 Using sqlite3

  • The easiest way to create an SQLite database from Python is through sqlite3 library

  • Assuming we have four tables (DataFrame) in the Pyhton environment, we can create an sqlite3 database of these four tables.

  • Create the database and disconnect.

Code
import pandas as pd
from sqlite3 import connect

import duckdb

# Create a database
conn = duckdb.connect('./data/School_DB.sqlite', read_only = False)

# Load tables onto database
tblClass.to_sql(name = 'tblClass', con = conn)
tblTeacher.to_sql(name = 'tblTeacher', con = conn)
tblStudent.to_sql(name = 'tblStudent', con = conn)
tblMarks.to_sql(name = 'tblMarks', con = conn)

# List tables
# dbListTables(conn)

# Disconnect
# dbDisconnect(conn)

# Clean the environment
del tblClass
del tblTeacher
del tblStudent
del tblMarks

del conn

23.3 Connect SQLite DB

Code
# Connect to the SQLite database (it will create the file if it doesn't exist)
conn = duckdb.connect('./data/School_DB.sqlite', read_only = False)

23.4 Explore Tables

Code
# List tables and fields
sSQL = "SELECT * FROM sqlite_master WHERE type='table'"
conn.sql(sSQL)


# Read database

sSQL = 'SELECT * FROM School.tblStudent'
conn.sql(sSQL)

sSQL = 'SELECT * FROM tblStudent;'
conn.sql(sSQL)

sSQL = 'SELECT * FROM tblClass;'
conn.sql(sSQL)

23.5 Add Table

Code
# Write and Remove a table
# Also: CREATE DATABASE, CREATE TABLE & DROP TABLE in SQL

sSQL = 'CREATE TABLE School_DB.tblDummy (ID BIGINT, Name VARCHAR)'
conn.sql(sSQL)

sSQL = "INSERT INTO School_DB.tblDummy VALUES (1, 'A')"
conn.sql(sSQL)

sSQL = 'SELECT * FROM tblDummy'
conn.sql(sSQL)

23.6 Remove Table

Code
# Write and Remove a table
# Also: CREATE DATABASE, CREATE TABLE & DROP TABLE in SQL

sSQL = "SELECT * FROM sqlite_master WHERE type='table'"
conn.sql(sSQL)

sSQL = 'DROP TABLE School_DB.tblDummy'
conn.sql(sSQL)

sSQL = "SELECT * FROM sqlite_master WHERE type='table'"
conn.sql(sSQL)

23.7 Insert Record

Code
# Insert the data in tblClass. This will also output a 1
sSQL = "INSERT INTO tblClass VALUES (7, 'BIO1', 4)"
conn.sql(sSQL)

# Check the modified table
sSQL = 'SELECT * FROM tblClass;'
conn.sql(sSQL)

23.8 Update Record

Code
# UPDATE Record

sSQL = "UPDATE tblClass SET Subject = 'ENG2' WHERE ClassID = 7"
conn.sql(sSQL)

sSQL = 'SELECT * FROM tblClass;'
conn.sql(sSQL)

23.9 Delete Record

Code
# DELETE Record

sSQL = "DELETE FROM tblClass WHERE ClassID = 7"
conn.sql(sSQL)

sSQL = "SELECT * FROM tblClass;"
conn.sql(sSQL)

23.10 Disconnect

Code
# Disconnect

conn.close()