Python数据库：用sqlite3轻松管理你的数据！
今天分享一个特别实用的技能 —— 使用Python的sqlite3库操作数据库。SQLite就像是一个随身携带的"小型数据仓库"，不需要安装复杂的数据库软件，就能轻松管理你的数据！
一、sqlite3基础入门
首先，让我们导入sqlite3库并创建一个简单的数据库：
import sqlite3
from datetime import datetime
# 连接到数据库（如果不存在会自动创建）
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
💡 小贴士：sqlite3是Python标准库的一部分，不需要额外安装。数据库文件就像一个普通文件一样，可以随时复制或移动！
二、数据库操作基础
1. 创建表
def create_tables():
"""创建基本的数据表"""
# 创建用户表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE,
created_at TIMESTAMP
)
''')
# 创建订单表
cursor.execute('''
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
amount REAL,
order_date TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users (id)
)
''')
# 保存更改
conn.commit()
2. 插入数据
def insert_user(name, email):
"""插入用户数据"""
try:
cursor.execute('''
INSERT INTO users (name, email, created_at)
VALUES (?, ?, ?)
''', (name, email, datetime.now()))
conn.commit()
return cursor.lastrowid
except sqlite3.IntegrityError:
print(f"错误：邮箱 {email} 已存在")
return None
def insert_order(user_id, amount):
"""插入订单数据"""
cursor.execute('''
INSERT INTO orders (user_id, amount, order_date)
VALUES (?, ?, ?)
''', (user_id, amount, datetime.now()))
conn.commit()
三、数据查询和更新
1. 基本查询
def query_examples():
"""基本查询示例"""
# 查询所有用户
cursor.execute('SELECT * FROM users')
all_users = cursor.fetchall()
# 查询特定用户
cursor.execute('SELECT * FROM users WHERE name = ?', ('张三',))
user = cursor.fetchone()
# 统计订单金额
cursor.execute('''
SELECT user_id, SUM(amount) as total_amount
FROM orders
GROUP BY user_id
''')
order_stats = cursor.fetchall()
return all_users, user, order_stats
2. 高级查询
def advanced_queries():
"""高级查询示例"""
# 联表查询
cursor.execute('''
SELECT users.name, orders.amount, orders.order_date
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.amount > ?
ORDER BY orders.order_date DESC
''', (1000,))
# 使用聚合函数
cursor.execute('''
SELECT
strftime('%Y-%m', order_date) as month,
COUNT(*) as order_count,
SUM(amount) as total_amount
FROM orders
GROUP BY month
HAVING total_amount > ?
''', (5000,))
四、实用工具类封装
class DatabaseManager:
def __init__(self, db_file):
self.db_file = db_file
self.conn = None
self.cursor = None
def connect(self):
"""连接数据库"""
try:
self.conn = sqlite3.connect(self.db_file)
self.cursor = self.conn.cursor()
return True
except Exception as e:
print(f"连接数据库失败：{str(e)}")
return False
def disconnect(self):
"""关闭数据库连接"""
if self.conn:
self.conn.close()
def execute(self, sql, parameters=None):
"""执行SQL语句"""
try:
if parameters:
self.cursor.execute(sql, parameters)
else:
self.cursor.execute(sql)
self.conn.commit()
return True
except Exception as e:
print(f"执行SQL失败：{str(e)}")
return False
def query(self, sql, parameters=None):
"""查询数据"""
try:
if parameters:
self.cursor.execute(sql, parameters)
else:
self.cursor.execute(sql)
return self.cursor.fetchall()
except Exception as e:
print(f"查询失败：{str(e)}")
return []
def backup_database(self, backup_file):
"""备份数据库"""
try:
with sqlite3.connect(backup_file) as backup:
self.conn.backup(backup)
return True
except Exception as e:
print(f"备份失败：{str(e)}")
return False
五、实战案例：简单的客户管理系统
class CustomerManager:
def __init__(self):
self.db = DatabaseManager('customers.db')
self.db.connect()
self.initialize_database()
def initialize_database(self):
"""初始化数据库表"""
self.db.execute('''
CREATE TABLE IF NOT EXISTS customers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
phone TEXT UNIQUE,
email TEXT,
created_at TIMESTAMP
)
''')
def add_customer(self, name, phone, email=None):
"""添加客户"""
return self.db.execute('''
INSERT INTO customers (name, phone, email, created_at)
VALUES (?, ?, ?, ?)
''', (name, phone, email, datetime.now()))
def search_customers(self, keyword):
"""搜索客户"""
return self.db.query('''
SELECT * FROM customers
WHERE name LIKE ? OR phone LIKE ? OR email LIKE ?
''', (f"%{keyword}%", f"%{keyword}%", f"%{keyword}%"))
def update_customer(self, customer_id, **kwargs):
"""更新客户信息"""
fields = ', '.join(f"{k} = ?" for k in kwargs.keys())
sql = f"UPDATE customers SET {fields} WHERE id = ?"
return self.db.execute(sql, (*kwargs.values(), customer_id))
def delete_customer(self, customer_id):
"""删除客户"""
return self.db.execute(
"DELETE FROM customers WHERE id = ?",
(customer_id,)
)
⚠️ 注意事项：
- 始终使用参数化查询防止SQL注入
- 及时关闭数据库连接
- 定期备份重要数据
- 注意处理并发访问
- 合理使用事务确保数据一致性
💡 进阶提示：
- 学习使用ORM框架（如SQLAlchemy）
- 实现数据库迁移功能
- 添加数据库性能监控
- 设计更复杂的数据库结构
