python学习笔记三数据库篇

JAY.LIN 收录于学习笔记 Python

2024-12-07 约 4748 字预计阅读 10 分钟

https://bing.ee123.net/img/rand?artid=131245252

python学习笔记(三)—数据库篇

一、数据库编程

数据库编程是指在应用程序中使用数据库管理系统（DBMS）进行数据存储、检索和处理的过程。数据库提供了一种结构化的方式来组织和存储数据，使得数据的管理更加高效和可靠。

1.1 关系数据库

关系数据库是一种基于关系模型的数据库系统，使用表（表格）来存储和组织数据。每个表由多个行（记录）和列（字段）组成。关系数据库使用结构化查询语言（SQL）进行数据操作和查询。

常见的关系数据库管理系统包括MySQL、PostgreSQL、Oracle和Microsoft SQL Server等。

1.2 连接数据库

在进行数据库编程之前，首先需要建立与数据库的连接。连接数据库的过程包括指定数据库的位置、认证身份和建立连接对象。

Python中可以使用第三方库（如 pymysql 、 psycopg2 等）提供的API来连接不同的数据库。

下面是一个连接MySQL数据库的示例：

import pymysql

# 建立数据库连接
connection = pymysql.connect(
    host='localhost',
    user='username',
    password='password',
    database='database_name'
)

# 使用连接对象进行数据库操作
# ...

# 关闭数据库连接
connection.close()

1.3 执行SQL语句

连接数据库后，可以使用SQL语句执行各种数据库操作，包括创建表、插入数据、查询数据、更新数据和删除数据等。

执行SQL语句的过程包括创建游标对象、执行SQL语句并获取结果。

下面是一个执行SQL查询语句的示例：

import pymysql

# 建立数据库连接
connection = pymysql.connect(
    host='localhost',
    user='username',
    password='password',
    database='database_name'
)

# 创建游标对象
cursor = connection.cursor()

# 执行SQL查询语句
sql = "SELECT * FROM users"
cursor.execute(sql)

# 获取查询结果
results = cursor.fetchall()

# 处理查询结果
for row in results:
    print(row)

# 关闭游标对象和数据库连接
cursor.close()
connection.close()

1.4 数据库操作示例

下面是一些数据库操作的示例，包括插入数据、查询数据、更新数据和删除数据等常见操作：

插入数据

import pymysql

# 建立数据库连接
connection = pymysql.connect(
    host='localhost',
    user='username',
    password='password',
    database='database_name'
)

# 创建游标对象
cursor = connection.cursor()

# 插入数据
sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')"
cursor.execute(sql)

# 提交事务
connection.commit()

# 关闭游标对象和数据库连接
cursor.close()
connection.close()

查询数据

import pymysql

# 建立数据库连接
connection = pymysql.connect(
    host='localhost',
    user='username',
    password='password',
    database='database_name'
)

# 创建游标对象
cursor =

 connection.cursor()

# 执行查询语句
sql = "SELECT * FROM users"
cursor.execute(sql)

# 获取查询结果
results = cursor.fetchall()

# 处理查询结果
for row in results:
    print(row)

# 关闭游标对象和数据库连接
cursor.close()
connection.close()

当进行多表联查时，可以使用SQL的JOIN操作来连接多个表并检索相关数据。下面是一个多表联查的示例：

import pymysql

# 建立数据库连接
connection = pymysql.connect(
    host='localhost',
    user='username',
    password='password',
    database='database_name'
)

# 创建游标对象
cursor = connection.cursor()

# 执行多表联查查询语句
sql = """
SELECT orders.order_id, customers.customer_name, products.product_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
JOIN products ON orders.product_id = products.product_id
"""
cursor.execute(sql)

# 获取查询结果
results = cursor.fetchall()

# 处理查询结果
for row in results:
    order_id, customer_name, product_name = row
    print(f"Order ID: {order_id}, Customer: {customer_name}, Product: {product_name}")

# 关闭游标对象和数据库连接
cursor.close()
connection.close()

在上述示例中，通过使用JOIN操作，将 orders 表与 customers 表和 products 表联结，检索订单的相关信息，包括订单ID、顾客名称和产品名称。

对于根据条件筛选数据的情况，可以使用SQL的WHERE子句来指定筛选条件。下面是一个根据条件筛选数据的示例：

import pymysql

# 建立数据库连接
connection = pymysql.connect(
    host='localhost',
    user='username',
    password='password',
    database='database_name'
)

# 创建游标对象
cursor = connection.cursor()

# 执行带有条件的查询语句
product_name = 'Apple'
min_price = 10
sql = f"""
SELECT product_id, product_name, price
FROM products
WHERE product_name = '{product_name}' AND price >= {min_price}
"""
cursor.execute(sql)

# 获取查询结果
results = cursor.fetchall()

# 处理查询结果
for row in results:
    product_id, product_name, price = row
    print(f"Product ID: {product_id}, Product Name: {product_name}, Price: {price}")

# 关闭游标对象和数据库连接
cursor.close()
connection.close()

在上述示例中，通过在SQL查询语句中使用WHERE子句，根据产品名称和最低价格进行筛选，检索符合条件的产品信息。

希望以上示例能帮助你理解多表联查和条件筛选的数据库操作。如有需要，请根据你的实际情况修改示例中的数据库连接信息和表名、字段名以适应你的数据库结构。

更新数据

import pymysql

# 建立数据库连接
connection = pymysql.connect(
    host='localhost',
    user='username',
    password='password',
    database='database_name'
)

# 创建游标对象
cursor = connection.cursor()

# 更新数据
sql = "UPDATE users SET email = 'new_email@example.com' WHERE id = 1"
cursor.execute(sql)

# 提交事务
connection.commit()

# 关闭游标对象和数据库连接
cursor.close()
connection.close()

删除数据

import pymysql

# 建立数据库连接
connection = pymysql.connect(
    host='localhost',
    user='username',
    password='password',
    database='database_name'
)

# 创建游标对象
cursor = connection.cursor()

# 删除数据
sql = "DELETE FROM users WHERE id = 1"
cursor.execute(sql)

# 提交事务
connection.commit()

# 关闭游标对象和数据库连接
cursor.close()
connection.close()

以上是关于数据库编程的基本概念和示例。通过掌握数据库编程，您可以使用数据库进行数据管理和操作，实现灵活的数据存储和检索功能。

二、ORM框架

ORM（对象关系映射）框架是一种将数据库表和对象模型映射起来的技术，使得开发者可以通过操作对象来实现对数据库的操作，而不需要直接编写SQL语句。ORM框架提供了一种更加面向对象的方式来进行数据库编程。

在Python中，常见的ORM框架包括SQLAlchemy、Django ORM和Peewee等。

2.1 使用ORM框架

使用ORM框架进行数据库编程的步骤通常包括以下几个方面：

定义模型：通过定义模型类来映射数据库表结构，每个模型类对应数据库中的一张表。
建立连接：连接数据库，配置数据库连接参数。
执行操作：通过调用模型类的方法来执行数据库操作，如插入数据、查询数据、更新数据和删除数据等。

下面是一个使用SQLAlchemy进行数据库操作的示例：

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 创建引擎和会话
engine = create_engine('mysql+pymysql://username:password@localhost/database_name')
Session = sessionmaker(bind=engine)
session = Session()

# 声明基类
Base = declarative_base()

# 定义模型
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    email = Column(String(50))

# 插入数据
user = User(name='John Doe', email='john@example.com')
session.add(user)
session.commit()

# 查询数据
users = session.query(User).all()
for user in users:
    print(user.name, user.email)

# 更新数据
user = session.query(User).filter_by(id=1).first()
user.email = 'new_email@example.com'
session.commit()

# 删除数据
user = session.query(User).filter_by(id=1).first()
session.delete(user)
session.commit()

# 关闭会话
session.close()

2.2 批量查询

使用ORM框架进行多表联查和条件筛选时，可以利用框架提供的查询接口和方法来构建复杂的查询语句。下面以SQLAlchemy为例，展示如何使用ORM框架进行多表联查和条件筛选的示例：

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base

# 创建引擎和会话
engine = create_engine('mysql+pymysql://username:password@localhost/database_name')
Session = sessionmaker(bind=engine)
session = Session()

# 声明基类
Base = declarative_base()

# 定义模型
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)                          # 用户ID字段，主键
    name = Column(String(50))                                       # 用户名字段
    email = Column(String(50))                                      # 邮箱字段

class Order(Base):
    __tablename__ = 'orders'
    id = Column(Integer, primary_key=True)                          # 订单ID字段，主键
    user_id = Column(Integer, ForeignKey('users.id'))               # 外键，关联到users表的id字段
    product_id = Column(Integer, ForeignKey('products.id'))         # 外键，关联到products表的id字段
    user = relationship('User', backref='orders')                   # 定义与User模型的关联关系，backref定义反向引用，可以通过User模型的orders属性获取关联的订单
    product = relationship('Product', backref='orders')             # 定义与Product模型的关联关系，backref定义反向引用，可以通过Product模型的orders属性获取关联的订单

class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)                          # 产品ID字段，主键
    name = Column(String(50))                                       # 产品名称字段
    price = Column(Integer)                                         # 产品价格字段

# 多表联查示例
orders = session.query(Order).join(User).join(Product).all()
for order in orders:
    print(f"Order ID: {order.id}, Customer: {order.user.name}, Product: {order.product.name}")

# 条件筛选示例
product_name = 'Apple'
min_price = 10
products = session.query(Product).filter(Product.name == product_name, Product.price >= min_price).all()
for product in products:
    print(f"Product ID: {product.id}, Product Name: {product.name}, Price: {product.price}")

# 关闭会话
session.close()

在上述示例中，首先定义了三个模型类： User 、 Order 和 Product ，分别对应数据库中的三个表。 Order 表与 User 表和 Product 表通过外键关联，定义了对应的关系。

对于多表联查，可以通过使用 join 方法来连接多个表，并通过点操作符访问关联的字段，例如 order.user.name 和 order.product.name 。

对于条件筛选，可以使用 filter 方法来指定筛选条件，例如 Product.name == product_name 表示产品名称等于指定的值， Product.price >= min_price 表示产品价格大于等于指定的最低价格。

通过使用ORM框架，可以更加方便地进行多表联查和条件筛选操作，而无需直接编写复杂的SQL语句。

请根据你的实际情况修改示例中的数据库连接信息和表名、字段名以适应你的数据库结构。另外，使用其他ORM框架如Django ORM和Peewee也可以类似地进行多表联查和条件筛选操作，只是具体的语法和方法会有所不同。

以下是对添加的注释的解释：

ForeignKey
用于定义外键关联，指定关联到其他表的字段。在示例中，** user_id 字段和 product_id 字段分别是对 users 表和 products **表的外键关联。
backref
在关系的另一侧创建反向引用。在示例中，** user 和 product 分别是 Order 模型与 User 模型和 Product 模型之间的关系。通过 backref 参数定义反向引用，可以通过 User 模型的 orders 属性获取关联的订单，同样地，通过 Product 模型的 orders **属性也可以获取关联的订单。
relationship
用于定义模型之间的关系。在示例中，** Order 模型与 User 模型和 Product 模型之间的关系通过 relationship 定义。 relationship **指定了两个模型之间的关联关系，可以通过该关系进行联查操作。
Column
用于定义模型类中的字段。在示例中， id 、 name 、 email 、 user_id 、 product_id 、** price 等字段都是通过 Column 定义的。 Column **指定了字段的数据类型和约束条件。

除了单条记录的插入、修改和删除操作，ORM框架通常也提供了批量处理多条记录的功能，以提高效率和性能。在某些场景下，批量操作比逐条操作更有效，特别是当需要处理大量数据时。

下面是使用ORM框架进行批量插入、修改和删除的示例代码（以SQLAlchemy为例）：

2.3 批量插入

from sqlalchemy.orm import sessionmaker
from models import User  # 导入数据模型类

# 创建Session
Session = sessionmaker(bind=engine)
session = Session()

# 创建多个User对象
users = [
    User(name='Alice', age=25),
    User(name='Bob', age=30),
    User(name='Charlie', age=35)
]

# 批量插入
session.bulk_save_objects(users)

# 提交事务
session.commit()

# 关闭Session
session.close()

2.4 批量修改

from sqlalchemy.orm import sessionmaker
from models import User  # 导入数据模型类

# 创建Session
Session = sessionmaker(bind=engine)
session = Session()

# 查询需要修改的记录
users = session.query(User).filter_by(age=30).all()

# 批量修改
for user in users:
    user.age = 31

# 提交事务
session.commit()

# 关闭Session
session.close()

2.5 批量删除

from sqlalchemy.orm import sessionmaker
from models import User  # 导入数据模型类

# 创建Session
Session = sessionmaker(bind=engine)
session = Session()

# 查询需要删除的记录
users = session.query(User).filter_by(age=35).all()

# 批量删除
session.delete(*users)

# 提交事务
session.commit()

# 关闭Session
session.close()

以上示例代码展示了使用ORM框架进行批量插入、修改和删除的操作。通过批量处理多条记录，可以显著提高数据库操作的效率和性能。

注意事项

在进行批量操作时，需要注意以下几点：

批量插入和修改需要使用 session.bulk_save_objects() 或 session.bulk_update_mappings() 方法，而不是逐条操作或使用 session.add() 方法。
批量删除需要使用 session.delete() 方法，并传入要删除的记录列表。
批量操作完成后，记得提交事务并关闭Session，以确保操作的完成和数据库的一致性。

请根据具体需求和场景，合理选择批量操作，以提高数据库编程的效率和性能。

希望以上内容能帮助你更好地理解和应用ORM框架的批量操作功能！

2.6 异常处理

在ORM框架中，异常处理是保证程序稳定性和可靠性的关键。ORM框架通常会提供特定的异常类来处理数据库操作可能抛出的异常，如连接异常、查询异常、保存异常等。通过捕获和处理这些异常，我们可以实现对错误情况的有效处理和错误信息的反馈。

下面是使用ORM框架进行异常处理的示例代码（以SQLAlchemy为例）：

from sqlalchemy.exc import SQLAlchemyError

try:
    # 执行数据库操作
except SQLAlchemyError as e:
    # 处理数据库操作异常
    print("数据库操作发生异常:", e)

2.7 连接池管理

ORM框架通常会提供连接池管理的功能，用于管理数据库连接的创建和回收。通过连接池，可以有效地重复使用连接，减少连接的开销，提高程序性能。ORM框架会负责维护连接池，并在需要时分配连接给数据库操作。

下面是使用ORM框架连接池的示例代码（以SQLAlchemy为例）：

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 创建数据库连接引擎
engine = create_engine('mysql://user:password@localhost/mydatabase')

# 创建Session工厂
Session = sessionmaker(bind=engine)

# 从工厂获取Session对象
session = Session()

# 使用Session对象进行数据库操作
# ...

# 关闭Session
session.close()

2.8 SQL注入防范

ORM框架通常会提供参数化查询（Prepared Statements）或绑定参数的功能来防范SQL注入攻击。通过将参数绑定到SQL语句中，ORM框架会自动处理参数值的转义和引号的添加，确保输入数据的安全性。

下面是使用ORM框架进行参数化查询的示例代码（以SQLAlchemy为例）：

from sqlalchemy import text

# 定义带参数的SQL语句
sql = text("SELECT * FROM users WHERE username = :username AND password = :password")

# 执行查询操作，并传入参数值
result = session.execute(sql, {"username": username, "password": password})

2.9 数据库事务处理

ORM框架通常会提供事务管理的功能，用于确保数据库操作的原子性和一致性。通过开启事务、提交事务或回滚事务，可以保证多个操作的操作结果要么全部生效，要么全部取消。

下面是使用ORM框架进行事务处理的示例代码（以SQLAlchemy为例）：

from sqlalchemy.exc import SQLAlchemyError

try:
    # 开启事务
    session.begin()

    # 执行数据库操作
    # ...

    # 提交事务
    session.commit()
except SQLAlchemyError as e:
    # 回滚事务
    session.rollback()
    print("数据库操作发生异常:", e)
finally:
    # 关闭Session
    session.close()

以上是关于异常处理、连接池管理、SQL注入防范和数据库事务处理的示例代码，这些技术可以在实际应用中提高数据库编程的稳定性、性能和安全性。

通过使用ORM框架，可以简化数据库操作的过程，提高开发效率，并且使得代码更加可读和易于

维护。