在Python中，你可以使用内置的sqlite3模块来操作SQLite数据库。以下是一些使用sqlite3模块进行数据存储的基础步骤：
1. 连接到SQLite数据库
import sqlite3
# 连接到SQLite数据库
# 如果数据库不存在，会自动在当前目录创建:
conn = sqlite3.connect('example.db')
2. 创建一个表
# 创建一个Cursor对象并通过它执行SQL语句
cursor = conn.cursor()
# 创建表
cursor.execute('''CREATE TABLE IF NOT EXISTS stocks
(date text, trans text, symbol text, qty real, price real)''')
# 提交事务
conn.commit()
3. 插入数据
# 插入数据
cursor.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
# 提交事务
conn.commit()
4. 查询数据
# 查询数据
cursor.execute('SELECT * FROM stocks')
rows = cursor.fetchall()
for row in rows:
print(row)
5. 更新数据
# 更新数据
cursor.execute("UPDATE stocks SET price = 38.00 WHERE symbol = 'RHAT'")
# 提交事务
conn.commit()
6. 删除数据
# 删除数据
cursor.execute("DELETE FROM stocks WHERE symbol = 'RHAT'")
# 提交事务
conn.commit()
7. 使用上下文管理器确保关闭连接
# 使用with语句确保连接被正确关闭
with sqlite3.connect('example.db') as conn:
cursor = conn.cursor()
# 执行SQL操作...
8. 关闭Cursor和连接
# 关闭Cursor
cursor.close()
# 关闭连接
conn.close()
在操作数据库时，通常建议使用with语句来管理数据库连接，这样可以确保即使在发生异常的情况下，连接也能被正确关闭。
此外，sqlite3模块还支持使用Python的DB-API 2.0规范定义的参数替换，这可以防止SQL注入攻击：
# 使用参数替换来防止SQL注入
t = ('RHAT',)
cursor.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(cursor.fetchone())
请确保在处理数据库时遵循良好的编程实践，包括但不限于使用参数化查询来防止SQL注入攻击，以及在事务中适当地提交和回滚更改。
创建一个学生表
import sqlite3
# 创建一个内存数据库进行操作
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
# 创建表
create_table_query = """
CREATE TABLE IF NOT EXISTS Students (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER,
email TEXT UNIQUE
);
"""
cursor.execute(create_table_query)
# 检查表是否创建成功
cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='Students';")
table_exists = cursor.fetchone()
table_exists
清空表：这将删除表中的所有行，但表结构会保留。
DELETE FROM Students;
删除表：这将删除整个表，包括表结构和所有数据。
DROP TABLE Students;
# 清空表
cursor.execute("DELETE FROM Students;")
# 检查表是否为空
cursor.execute("SELECT * FROM Students;")
is_table_empty = cursor.fetchall()
# 删除表
cursor.execute("DROP TABLE Students;")
# 检查表是否还存在
cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='Students';")
table_exists_after_drop = cursor.fetchone()
is_table_empty, table_exists_after_drop
内连接
# 创建 Customers 表
cursor.execute('''
CREATE TABLE IF NOT EXISTS Customers (
CustomerID INTEGER PRIMARY KEY,
CustomerName TEXT
);
''')
# 创建 Orders 表
cursor.execute('''
CREATE TABLE IF NOT EXISTS Orders (
OrderID INTEGER PRIMARY KEY,
CustomerID INTEGER,
OrderDate TEXT,
FOREIGN KEY(CustomerID) REFERENCES Customers(CustomerID)
);
''')
# 插入 Customers 表的示例数据
customers_data = [
(1, 'John Doe'),
(2, 'Jane Smith'),
(3, 'Alice Johnson')
]
cursor.executemany("INSERT INTO Customers (CustomerID, CustomerName) VALUES (?, ?);", customers_data)
# 插入 Orders 表的示例数据
orders_data = [
(101, 1, '2023-09-10'),
(102, 2, '2023-09-11'),
(103, 1, '2023-09-12')
]
cursor.executemany("INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES (?, ?, ?);", orders_data)
# 执行内连接查询
cursor.execute('''
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
''')
inner_join_results = cursor.fetchall()
inner_join_results
#输出：[(101, ‘John Doe’), (102, ‘Jane Smith’), (103, ‘John Doe’)]
排序
# 创建 Employees 表
cursor.execute('''
CREATE TABLE IF NOT EXISTS Employees (
EmployeeID INTEGER PRIMARY KEY,
EmployeeName TEXT,
Salary REAL
);
''')
# 插入 Employees 表的示例数据
employees_data = [
(1, 'John Doe', 70000),
(2, 'Jane Smith', 65000),
(3, 'Alice Johnson', 72000),
(4, 'Mike Brown', 63000)
]
cursor.executemany("INSERT INTO Employees (EmployeeID, EmployeeName, Salary) VALUES (?, ?, ?);", employees_data)
# 执行查询并按 Salary 降序排序
cursor.execute('SELECT EmployeeName, Salary FROM Employees ORDER BY Salary DESC;')
sorted_employees_by_salary = cursor.fetchall()
sorted_employees_by_salary
COUNT():返回表中行的数量，或者返回特定列中非NULL值的数量。
SUM():返回数值列的总和。
AVG():返回数值列的平均值。
MAX():返回数值列中的最大值，或日期/时间列中的最新值。
MIN():返回数值列中的最小值，或日期/时间列中的最早值。
# 使用聚合函数查询 Employees 表
# 1. 计算总行数
cursor.execute('SELECT COUNT(*) FROM Employees;')
total_rows = cursor.fetchone()[0]
# 2. 计算薪资总和
cursor.execute('SELECT SUM(Salary) FROM Employees;')
total_salary = cursor.fetchone()[0]
# 3. 计算平均薪资
cursor.execute('SELECT AVG(Salary) FROM Employees;')
average_salary = cursor.fetchone()[0]
# 4. 获取最高薪资
cursor.execute('SELECT MAX(Salary) FROM Employees;')
max_salary = cursor.fetchone()[0]
# 5. 获取最低薪资
cursor.execute('SELECT MIN(Salary) FROM Employees;')
min_salary = cursor.fetchone()[0]
total_rows, total_salary, average_salary, max_salary, min_salary
分组（GROUP BY）是SQL中用于将数据分成多个小组以便于聚合的语句。在分组后，可以使用聚合函数（如COUNT(), SUM(), AVG(), MAX(), MIN()等）对每个分组进行计算。
# 执行分组查询并计算每个部门的平均薪资，同时过滤出平均薪资大于50,000的部门
cursor.execute('''
SELECT department, AVG(salary) as average_salary
FROM Employees
GROUP BY department
HAVING AVG(salary) > 50000;
''')
# 获取查询结果
grouped_results = cursor.fetchall()
grouped_results
#输出：[(‘HR’, 57000.0), (‘IT’, 67666.66666666667), (‘Marketing’, 75000.0)]
使用pymysql模块操作MySQL
import pymysql
# 连接到数据库
db = pymysql.connect(
host='127.0.0.1', # 数据库地址
user='root', # 数据库用户名
password='123456', # 数据库密码
database='test', # 数据库名
charset='utf8' # 字符集
)
# 创建游标对象
cursor = db.cursor()
# 执行SQL语句
try:
# 执行一个查询操作
cursor.execute("SELECT * FROM your_table_name")
# 获取所有结果
results = cursor.fetchall()
# 打印结果
for row in results:
print(row)
# 提交事务
db.commit()
except Exception as e:
# 如果发生错误，回滚事务
db.rollback()
print(f"An error occurred: {e}")
finally:
# 关闭游标和连接
cursor.close()
db.close()
网友评论