Using SQLite ​

SQLite is an embedded database, where the database itself is just a file. Since SQLite is written in C and is very small in size, it is often integrated into various applications, including iOS and Android apps.

Python has built-in support for SQLite3, so using SQLite in Python does not require any installation; it can be used directly.

Before using SQLite, we need to understand a few concepts:

A table is a collection that stores relational data in the database. A database usually contains multiple tables, such as a student table, a class table, a school table, etc. Tables are linked together through foreign keys.

To operate a relational database, we first need to connect to the database. A database connection is referred to as a Connection.

After connecting to the database, a cursor needs to be opened, known as a Cursor. The Cursor is used to execute SQL statements and retrieve the results.

Python defines a standard database API interface, so any database can be connected to Python by providing a database driver that conforms to this standard.

Since the SQLite driver is built into Python's standard library, we can directly manipulate the SQLite database.

Let's practice with Python's interactive command line:

# Import SQLite driver:
>>> import sqlite3
# Connect to SQLite database
# The database file is test.db
# If the file does not exist, it will be created in the current directory:
>>> conn = sqlite3.connect('test.db')
# Create a Cursor:
>>> cursor = conn.cursor()
# Execute an SQL statement to create the user table:
>>> cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
<sqlite3.Cursor object at 0x10f8aa260>
# Execute another SQL statement to insert a record:
>>> cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')
<sqlite3.Cursor object at 0x10f8aa260>
# Get the number of inserted rows using rowcount:
>>> cursor.rowcount
1
# Commit the transaction:
>>> conn.commit()
# Close the Cursor:
>>> cursor.close()
# Close the Connection:
>>> conn.close()

Now let's try querying the records:

>>> conn = sqlite3.connect('test.db')
>>> cursor = conn.cursor()
# Execute a query:
>>> cursor.execute('select * from user where id=?', ('1',))
<sqlite3.Cursor object at 0x10f8aa340>
# Retrieve the result set:
>>> values = cursor.fetchall()
>>> values
[('1', 'Michael')]
>>> cursor.close()
>>> conn.close()

When using Python's DB-API, as long as you understand the Connection and Cursor objects, and remember to close them after opening, you can use it with confidence.

When executing insert, update, or delete statements with the Cursor object, the number of affected rows can be obtained through rowcount.

When executing a select statement with the Cursor object, the results can be retrieved using fetchall(). The result set is a list where each element is a tuple corresponding to a row.

If an SQL statement contains parameters, they need to be passed to the execute() method in the correct order. For example:

cursor.execute('select * from user where name=? and pwd=?', ('abc', 'password'))

SQLite supports common standard SQL statements and several common data types. Please refer to the SQLite official documentation for details.

Summary ​

When working with databases in Python, you need to first import the corresponding database driver, and then use Connection and Cursor objects to manipulate the data.

Make sure to properly close both the Connection and Cursor objects to avoid resource leaks.

How can you ensure that the Connection and Cursor objects are closed even when an error occurs? Recall the use of try:...except:...finally:.

Exercise ​

Write a function to find specified names based on score ranges in SQLite:

import os, sqlite3

db_file = os.path.join(os.path.dirname(__file__), 'test.db')
if os.path.isfile(db_file):
    os.remove(db_file)

# Initial data:
conn = sqlite3.connect(db_file)
cursor = conn.cursor()
cursor.execute('create table user(id varchar(20) primary key, name varchar(20), score int)')
cursor.execute(r"insert into user values ('A-001', 'Adam', 95)")
cursor.execute(r"insert into user values ('A-002', 'Bart', 62)")
cursor.execute(r"insert into user values ('A-003', 'Lisa', 78)")
conn.commit()
cursor.close()
conn.close()

def get_score_in(low, high):
    ' Return names within the specified score range, sorted from low to high '
    pass

# Tests:
assert get_score_in(80, 95) == ['Adam'], get_score_in(80, 95)
assert get_score_in(60, 80) == ['Bart', 'Lisa'], get_score_in(60, 80)
assert get_score_in(60, 100) == ['Bart', 'Lisa', 'Adam'], get_score_in(60, 100)

print('Pass')