查询数据 (SELECT)完整代码如下：
from contextlib import contextmanager
import mysql.connector
from mysql.connector import Error
class DatabaseConfig:
"""数据库配置类，集中管理连接参数"""
HOST = '192.168.116.70' # 数据库服务器地址
PORT = 3306 # 数据库端口号
DATABASE = 'python_mysql_demo' # 数据库名
USER = 'root' # 数据库用户名
PASSWORD = 'aaaaaa' # 数据库密码
CHARSET = 'utf8mb4' # 字符集，该字符集可支持emoji表情
class Database:
"""数据库操作封装"""
def __init__(self, config=DatabaseConfig):
self.config = config
self.connection = None
def connect(self):
"""建立连接"""
self.connection = mysql.connector.connect(
host=self.config.HOST,
port=self.config.PORT,
database=self.config.DATABASE,
user=self.config.USER,
password=self.config.PASSWORD,
charset=self.config.CHARSET,
autocommit=False
)
return self.connection
def close(self):
"""关闭连接"""
if self.connection and self.connection.is_connected():
self.connection.close()
def __enter__(self):
"""
进入上下文管理器，建立数据库连接
在 with 语句入口处被调用，建立连接并返回实例本身
"""
self.connect()
return self
def __exit__(self, exc_type, exc_val, exc_tb):
"""
退出上下文管理器，关闭数据库连接
"""
self.close()
def get_all_students(db):
"""查询所有学生"""
sql = "SELECT id, name, age, email, enrolled_at FROM students"
cursor = db.connection.cursor(dictionary=True) # 返回字典格式
cursor.execute(sql)
return cursor.fetchall()
def get_students_by_age(db, min_age, max_age):
"""按年龄范围查询，参数化查询"""
sql = "SELECT * FROM students WHERE age BETWEEN %s AND %s"
cursor = db.connection.cursor(dictionary=True)
cursor.execute(sql, (min_age, max_age))
return cursor.fetchall()
def search_students(db, keyword):
"""模糊搜索，LIKE 查询"""
sql = "SELECT * FROM students WHERE name LIKE %s"
cursor = db.connection.cursor(dictionary=True)
cursor.execute(sql, ('%' + keyword + '%',))
return cursor.fetchall()
def get_student_count(db):
"""聚合查询，COUNT"""
sql = "SELECT COUNT(*) as total, AVG(age) as avg_age FROM students"
cursor = db.connection.cursor(dictionary=True)
cursor.execute(sql)
return cursor.fetchone()
# 分页查询
def get_students_paginated(db, page=1, page_size=10):
"""分页查询，LIMIT / OFFSET"""
offset = (page - 1) * page_size
sql = "SELECT * FROM students ORDER BY id LIMIT %s OFFSET %s"
cursor = db.connection.cursor(dictionary=True)
cursor.execute(sql, (page_size, offset))
return cursor.fetchall()
if __name__ == '__main__':
with Database() as db:
# 查询所有学生
students = get_all_students(db)
print("所有学生：")
for student in students:
print(student)
# 按年龄范围查询
students_in_age_range = get_students_by_age(db, 19, 20)
print("\n年龄在19-20之间的学生：")
for student in students_in_age_range:
print(student)
# 模糊搜索
search_result = search_students(db, '张')
print("\n查询'张'的结果：")
for student in search_result:
print(student)
# 聚合查询
student_count = get_student_count(db)
print("\n学生总数和平均年龄：")
print(student_count)
# 分页查询
paginated_students = get_students_paginated(db, page=1, page_size=3)
print("\n分页学生数据（第1页，每页3条）：")
for student in paginated_students:
print(student)
川公网安备51010802032098