It is easy to assume that a database must be large, powerful, feature-packed, fast under heavy workloads, and capable of handling huge bursts of traffic. For a personal project or a small application, however, that kind of planning can be unnecessary. Sometimes the compact option is the practical one.
That is where SQLite fits especially well. Used together with Python, it provides a lightweight way to collect, store, update, and query data without setting up a separate database server.
Why SQLite works well for small projects
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is one of the most widely deployed SQL database engines in the world, and its source code is not restricted by copyright.
In practical terms, there is no database service to install and configure, no separate server process consuming memory, and no complicated deployment procedure. The database is stored in a file, while the SQL features needed by many personal tools and small applications remain available without a license fee.
Installing SQLite
SQLite is included with almost all versions of Linux. Windows users can download and install it from the official SQLite website.
After installation, open a terminal and run sqlite3. If the command starts the SQLite shell, the installation is working:
➜ ~ sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
The prompt also makes an important distinction: without opening a file, SQLite is connected to a temporary in-memory database. A database file can be opened when persistent storage is needed.
Using sqlite3 from Python
Python includes the sqlite3 module, so basic database programming requires no additional database driver. To see how the pieces fit together, consider a small personal memo application. The example creates a database, defines a table, and then demonstrates the basic create, insert, delete, update, and query operations.
Creating the database
The following code creates memorandum.db in the same directory as the Python file:
import sqlite3#引入模块
import os
from datetime import *
print(os.path.join(os.path.dirname(__file__), 'memorandum.db'))
conn = sqlite3.connect(os.path.join(os.path.dirname(__file__), 'memorandum.db'))#在当目录下创建数据库
print('备忘录数据库创建成功！')
Calling sqlite3.connect() opens the database if it already exists, or creates the file when it does not. The conn object represents the connection used for subsequent database operations.
Creating a table and its fields
A cursor is created with cursor(). In Python database programming, the cursor is used to execute most SQL commands through cursor.execute().
c = conn.cursor()#创建一个cursor对象
# 创建表
c.execute('''create table tab(
id integer not null primary key autoincrement,
title char(50),
content text not null,
c_date text);''')
print('数据表创建成功！')
conn.commit()#提交事务
conn.close()#关闭连接
This creates a table named tab with four fields: an automatically incrementing primary key called id, a title, the memo content, and a text field for the creation date. commit() commits the transaction, and close() releases the database connection.
Creating tables directly in Python is convenient when setting up a small application. If you prefer a graphical interface, DBeaver can be used to write database commands, inspect table structures, and back up data.
Once the table exists, the familiar SQL operations for inserting, deleting, updating, and selecting records are available. SQLite uses SQL syntax that is largely shared with other database systems, so previous SQL experience transfers easily.
Inserting a record
Here is a single-record insert. The current time is formatted as a string before the SQL statement is assembled and executed:
title = '测试标题'
content = '插入数据测试。'
format = "%Y-%m-%d %H:%M:%S"
now = datetime.now().strftime(format)#创建当前时间字符串
sql = "insert into tab (id,title, content, c_date) values (NULL,'{0}','{1}','{2}')".format(title,content,now)
print(sql)
c.execute(sql)
conn.commit()
conn.close()#关闭连接
The id value is set to NULL, allowing SQLite to generate the primary-key value automatically. After execution, commit() saves the change to the database.
Inserting many records
The Python interface also supports ? placeholders. They make it convenient to pass values separately while inserting a large number of records. The example below prepares 10,000 rows:
# 准备10000条数据插入
for i in range(10000):
format = "%Y-%m-%d %H:%M:%S"
now = datetime.now().strftime(format)#创建当前时间字符串
c.execute('insert into tab (id,title, content, c_date) values(?,?,?,?)',(None,str(i),str(i),now))
conn.commit()#提交事务
conn.close()
Each iteration supplies four values for the four placeholders. The changes are committed after the loop finishes.
Deleting a record
A record can be removed by adding a condition to a delete statement:
sql = "delete from tab where id = 2"#拼装sql语句
c.execute(sql)#执行删除
conn.commit()
conn.close()
After this code runs, the row whose id is 2 is deleted.
Updating a record
The update statement changes selected fields in an existing row. This example changes the title of the record with an id of 20004:
sql = "update tab set title='Boy' where id = 20004"
c.execute(sql)
conn.commit()
conn.close()
Querying records
To read data, execute a select statement and iterate over the returned rows:
sql = "select * from tab"
l = c.execute(sql)
for s in l :
print(s)
conn.close
The terminal then displays the rows returned by the query:
Other queries use the same approach; only the SQL statement changes. Conditions, selected columns, sorting, and other requirements can be expressed through SQL as needed.
For data collection and small-scale storage, Python and SQLite3 make a particularly convenient combination. Python handles the application logic, while SQLite provides a compact database that can be created, queried, and moved along with the project as a single file.