How to Use SQLite in Python

Posted on Views: Word count in article: 285 Reading time ≈ 1 mins.

SQLite is a C library that provides a lightweight disk-based database. More information can be found here: https://www.sqlite.org/index.html.

We can use the sqlite3 library in Python. More information about this library: https://docs.python.org/3/library/sqlite3.html.

Here are some sample codes for the commonly used functions of this library:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
import sqlite3

# Create a database in the current working directory
conn = sqlite3.connect('my_db.db')

# Create a database cursor
cursor = conn.cursor()

# Create a table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
)
''')

# Commit changes
conn.commit()

# Insert a single record
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("A", 30))

# Insert multiple records
new_users = [("B", 20), ("C", 10)]
cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", new_users)
conn.commit()

# Query all users
cursor.execute("SELECT * FROM users")

print('All users:')
user_info_1 = cursor.fetchall()
print(user_info_1)

# Update a record
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (100, "A"))
conn.commit()

print('After update:')
cursor.execute("SELECT * FROM users")
# fetchall() does not automatically refresh the data, it only reads the results of the last execute()
user_info_2 = cursor.fetchall()
print(user_info_2)

#  Delete a record
cursor.execute("DELETE FROM users WHERE name = ?", "A")
conn.commit()

print('After delete a record:')
cursor.execute("SELECT * FROM users")
user_info_3 = cursor.fetchall()
print(user_info_3)

# Delete all records
cursor.execute("DELETE FROM users")
conn.commit()

print('After delete all records:')
cursor.execute("SELECT * FROM users")
user_info_4 = cursor.fetchall()
print(user_info_4)

# Close the database connection
conn.close()

The output is as follows:

1
2
3
4
5
6
7
8
All users:
[(1, 'A', 30), (2, 'B', 20), (3, 'C', 10)]
After update:
[(1, 'A', 100), (2, 'B', 20), (3, 'C', 10)]
After delete a record:
[(2, 'B', 20), (3, 'C', 10)]
After delete all records:
[]