博客

使用python ORM来操作MySQL

  • Post author:
  • Post category:mysql



昨天的博客是用MySQL官方的MySQL-connector驱动来完成数据库的连接和使用，但只适用于小项目的操作，当项目规模增加时，代码会越来越复杂，维护成本也越来越高，此时需要一个更好的设计模式。即使用ROM框架来操作MySQL。

ORM英文是（Object Relation Mapping），中文意思是对象关系映射，它是RDBMS和业务实体对象之间的一个映射。换句话说，是将底层的RDBMS封装成业务实体对象，提供给业务逻辑层使用。

优点是：

  1. 一旦定义好了对象模型，就可以让他们简单可复用，从而不必关注底层的数据库访问细节，只将注意力集中到业务逻辑层面就可以了。
  2. 即便数据库本身进行了更换，在业务逻辑代码上也不会有大的调整。因为OR

    抽象了数据的存取，同时也兼容了多种DBMS，不必关心底层采用的是哪种DBMS，例如MySQL，SQL Server，PostgreSQL或SQLite。


缺点

：对于一些复杂的数据查询，ORM会显得力不从心，性能会有损失。有时相对直接编写SQL查询语句来说，ORM编写的代码量和花费的时间会比较多。


python中的ORM框架有三种较为主流的：

  1. Django，它是Python的web应用开发框架，大而全。采用了MTV的框架模式，包括了Model（模式），View（视图）和Template（模板）。Model模型只是Django的一部分功能，我们可以通过它实现数据库的增删改查操作。
  2. SQLALchemy，是Python常用的ORM框架之一，提供了SQL工具包和ORM工具， SQLALchemy的社区更为活跃，对项目实施很有帮助。
  3. peewee，轻量级的ORM框架，简单易用。

本文主要采用SQLALchemy来操作MySQL。首先要安装SQLALchemy工具包: 

pip install sqlalchemy

接下来我们来连接数据库。create_engine 的使用方法类似我们在上篇文章中提到的 mysql.connector，都需要提供数据库 + 数据库连接框架，即对应的是mysql+mysqlconnector，后面的是用户名:密码@IP地址:端口号/数据库名称。 

# -*- coding: UTF-8 -*-
from sqlalchemy import create_engine
engine =create_engine('mysql+mysqlconnector://root:密码@localhost:3306/数据库名称')

因为我的数据库是world，并且其中有一张player的表。我以此为例，给出一个关于player操作的简单例子。

这里，我们首先需要初始化 DBSession，相当于创建一个数据库的会话实例 session。通过 session 来完成新球员的添加。我们在 Player 模型中对采用的变量名进行定义，变量名需要和数据表中的字段名称保持一致，否则会找不到数据表中的字段。在 SQLAlchemy 中，我们采用 Column 对字段进行定义，而相应的数据类型需要提前在SQLAIchemy中引用。 

from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Float, or_
# 创建对象的基类:
Base = declarative_base()

# 定义 Player 对象:
class Player(Base):
    # 表的名字:
    __tablename__ = 'player'

    # 表的结构:
    player_id = Column(Integer, primary_key=True, autoincrement=True)
    team_id = Column(Integer)
    player_name = Column(String(255))
    height = Column(Float(3, 2))

    # 增加 to_dict() 方法到 Base 类中
    def to_dict(self):
        return {c.name: getattr(self, c.name, None) for c in self.__table__.columns}
    # 将对象可以转化为 dict 类型
    Base.to_dict = to_dict
    
engine = create_engine('mysql+mysqlconnector://root:bai8632408@localhost:3306/world')
# 创建 DBSession 类型:
DBSession = sessionmaker(bind=engine)
# 创建 session 对象:
session = DBSession()

创建一个Player对象，并添加到该表中。对于新球员的数据，我们可以通过 Player 类来完成创建，在参数中指定相应的team_id, player_name, height即可。 

# 创建 Player 对象:
new_player = Player(team_id = 1101, player_name = " 约翰 - 雪诺 ", height = 2.08)
# 添加到 session:
session.add(new_player)
# 提交即保存到数据库:
session.commit()

添加完插入的新球员之后，我们可以查询下身高 ≥ 2.08m 的球员都有哪些，代码如下： 

# 查询身高 >=2.08 的球员有哪些
rows_1 = session.query(Player).filter(Player.height >= 2.08).all()
print([row.to_dict() for row in rows_1])

运行结果： 

[{'player_id': 10003, 'team_id': 1001, 'player_name': '安德烈-德拉蒙德', 'height': Decimal('2.1100000000')}, {'player_id': 10004, 'team_id': 1001, 'player_name': '索恩-马克', 'height': Decimal('2.1600000000')}, {'player_id': 10009, 'team_id': 1001, 'player_name': '扎扎-帕楚里亚', 'height': Decimal('2.1100000000')}, {'player_id': 10010, 'team_id': 1001, 'player_name': '乔恩-洛伊尔', 'height': Decimal('2.0800000000')}, {'player_id': 10011, 'team_id': 1001, 'player_name': '布雷克-格里芬', 'height': Decimal('2.0800000000')}, {'player_id': 10015, 'team_id': 1001, 'player_name': '亨利-埃伦森', 'height': Decimal('2.1100000000')}, {'player_id': 10023, 'team_id': 1002, 'player_name': '多曼塔斯-萨博尼斯', 'height': Decimal('2.1100000000')}, {'player_id': 10024, 'team_id': 1002, 'player_name': '迈尔斯-特纳', 'height': Decimal('2.1100000000')}, {'player_id': 10032, 'team_id': 1002, 'player_name': 'TJ-利夫', 'height': Decimal('2.0800000000')}, {'player_id': 10033, 'team_id': 1002, 'player_name': '凯尔-奥奎因', 'height': Decimal('2.0800000000')}, {'player_id': 10037, 'team_id': 1002, 'player_name': '伊凯·阿尼博古', 'height': Decimal('2.0800000000')}, {'player_id': 10042, 'team_id': 1003, 'player_name': ' 约翰 - 科林斯 ', 'height': Decimal('2.0800000000')}, {'player_id': 10044, 'team_id': 1300, 'player_name': ' 约翰 - 雪诺 ', 'height': Decimal('2.0800000000')}, {'player_id': 10046, 'team_id': 1300, 'player_name': ' 约翰 - 没雪诺 ', 'height': Decimal('2.0800000000')}, {'player_id': 10048, 'team_id': 1301, 'player_name': ' 约翰 - 雪诺niu ', 'height': Decimal('2.0800000000')}]

然后，我们使用SQLAlchemy中引用的or_关系查询身高要么大于等于2.08，要么小于2.10的球员。 

rows_2 = session.query(Player).filter(or_(Player.height >=2.08, Player.height <=2.10)).all()
print([row.to_dict() for row in rows_2])

运行结果： 

[{'player_id': 10001, 'team_id': 1001, 'player_name': '韦恩-艾灵顿', 'height': Decimal('1.9300000000')}, {'player_id': 10002, 'team_id': 1001, 'player_name': '雷吉-杰克逊', 'height': Decimal('1.9100000000')}, {'player_id': 10003, 'team_id': 1001, 'player_name': '安德烈-德拉蒙德', 'height': Decimal('2.1100000000')}, {'player_id': 10004, 'team_id': 1001, 'player_name': '索恩-马克', 'height': Decimal('2.1600000000')}, {'player_id': 10005, 'team_id': 1001, 'player_name': '布鲁斯-布朗', 'height': Decimal('1.9600000000')}, {'player_id': 10006, 'team_id': 1001, 'player_name': '兰斯顿-加洛韦', 'height': Decimal('1.8800000000')}, {'player_id': 10007, 'team_id': 1001, 'player_name': '格伦-罗宾逊三世', 'height': Decimal('1.9800000000')}, {'player_id': 10008, 'team_id': 1001, 'player_name': '伊斯梅尔-史密斯', 'height': Decimal('1.8300000000')}, {'player_id': 10009, 'team_id': 1001, 'player_name': '扎扎-帕楚里亚', 'height': Decimal('2.1100000000')}, {'player_id': 10010, 'team_id': 1001, 'player_name': '乔恩-洛伊尔', 'height': Decimal('2.0800000000')}, {'player_id': 10011, 'team_id': 1001, 'player_name': '布雷克-格里芬', 'height': Decimal('2.0800000000')}, {'player_id': 10012, 'team_id': 1001, 'player_name': '雷吉-巴洛克', 'height': Decimal('2.0100000000')}, {'player_id': 10013, 'team_id': 1001, 'player_name': '卢克-肯纳德', 'height': Decimal('1.9600000000')}, {'player_id': 10014, 'team_id': 1001, 'player_name': '斯坦利-约翰逊', 'height': Decimal('2.0100000000')}, {'player_id': 10015, 'team_id': 1001, 'player_name': '亨利-埃伦森', 'height': Decimal('2.1100000000')}, {'player_id': 10016, 'team_id': 1001, 'player_name': '凯里-托马斯', 'height': Decimal('1.9100000000')}, {'player_id': 10017, 'team_id': 1001, 'player_name': '何塞-卡尔德隆', 'height': Decimal('1.9100000000')}, {'player_id': 10018, 'team_id': 1001, 'player_name': '斯维亚托斯拉夫-米凯卢克', 'height': Decimal('2.0300000000')}, {'player_id': 10019, 'team_id': 1001, 'player_name': '扎克-洛夫顿', 'height': Decimal('1.9300000000')}, {'player_id': 10020, 'team_id': 1001, 'player_name': '卡林-卢卡斯', 'height': Decimal('1.8500000000')}, {'player_id': 10021, 'team_id': 1002, 'player_name': '维克多-奥拉迪波', 'height': Decimal('1.9300000000')}, {'player_id': 10022, 'team_id': 1002, 'player_name': '博扬-博格达诺维奇', 'height': Decimal('2.0300000000')}, {'player_id': 10023, 'team_id': 1002, 'player_name': '多曼塔斯-萨博尼斯', 'height': Decimal('2.1100000000')}, {'player_id': 10024, 'team_id': 1002, 'player_name': '迈尔斯-特纳', 'height': Decimal('2.1100000000')}, {'player_id': 10025, 'team_id': 1002, 'player_name': '赛迪斯-杨', 'height': Decimal('2.0300000000')}, {'player_id': 10026, 'team_id': 1002, 'player_name': '达伦-科里森', 'height': Decimal('1.8300000000')}, {'player_id': 10027, 'team_id': 1002, 'player_name': '韦斯利-马修斯', 'height': Decimal('1.9600000000')}, {'player_id': 10028, 'team_id': 1002, 'player_name': '泰瑞克-埃文斯', 'height': Decimal('1.9800000000')}, {'player_id': 10029, 'team_id': 1002, 'player_name': '道格-迈克德莫特', 'height': Decimal('2.0300000000')}, {'player_id': 10030, 'team_id': 1002, 'player_name': '科里-约瑟夫', 'height': Decimal('1.9100000000')}, {'player_id': 10031, 'team_id': 1002, 'player_name': '阿龙-霍勒迪', 'height': Decimal('1.8500000000')}, {'player_id': 10032, 'team_id': 1002, 'player_name': 'TJ-利夫', 'height': Decimal('2.0800000000')}, {'player_id': 10033, 'team_id': 1002, 'player_name': '凯尔-奥奎因', 'height': Decimal('2.0800000000')}, {'player_id': 10034, 'team_id': 1002, 'player_name': '埃德蒙-萨姆纳', 'height': Decimal('1.9600000000')}, {'player_id': 10035, 'team_id': 1002, 'player_name': '达文-里德', 'height': Decimal('1.9800000000')}, {'player_id': 10036, 'team_id': 1002, 'player_name': '阿利兹-约翰逊', 'height': Decimal('2.0600000000')}, {'player_id': 10037, 'team_id': 1002, 'player_name': '伊凯·阿尼博古', 'height': Decimal('2.0800000000')}, {'player_id': 10042, 'team_id': 1003, 'player_name': ' 约翰 - 科林斯 ', 'height': Decimal('2.0800000000')}, {'player_id': 10044, 'team_id': 1300, 'player_name': ' 约翰 - 雪诺 ', 'height': Decimal('2.0800000000')}, {'player_id': 10046, 'team_id': 1300, 'player_name': ' 约翰 - 没雪诺 ', 'height': Decimal('2.0800000000')}, {'player_id': 10048, 'team_id': 1301, 'player_name': ' 约翰 - 雪诺niu ', 'height': Decimal('2.0800000000')}]

如果我们想实现group by 的功能，那么可以使用from sqlalchemy 中的func，按照team_id分组，并筛选分组后数据行数大于5的分组，分组后按照数据行数递增的顺序排序。选取的字段有team_id，每个分组的数据行数。代码如下： 

rows_3 = session.query(Player.team_id, func.count(Player.player_id)).group_by(Player.team_id).having(func.count(Player.player_id)>5).order_by(func.count(Player.player_id).asc()).all()
print(rows_3)

运行结果： 

[(1002, 17), (1001, 20)]

总的代码如下： 

# -*- coding: UTF-8 -*-
from sqlalchemy import create_engine
from sqlalchemy import Column, String, Integer, Float, or_
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import func

# 创建对象的基类:
Base = declarative_base()
# 定义 Player 对象:
class Player(Base):
    # 表的名字:
    __tablename__ = 'player'

    # 表的结构:
    player_id = Column(Integer, primary_key=True, autoincrement=True)
    team_id = Column(Integer)
    player_name = Column(String(255))
    height = Column(Float(3, 2))
    # 增加 to_dict() 方法到 Base 类中
    def to_dict(self):
        return {c.name: getattr(self, c.name, None) for c in self.__table__.columns}
    # 将对象可以转化为 dict 类型
    Base.to_dict = to_dict
if __name__=='__main__':
    engine = create_engine('mysql+mysqlconnector://root:密码@localhost:3306/world')
    # 创建 DBSession 类型:
    DBSession = sessionmaker(bind=engine)
    # 创建 session 对象:
    session = DBSession()

    # 创建 Player 对象:
    new_player = Player(team_id=1101, player_name=" 约翰 - 雪诺 ", height=2.08)
    # 添加到 session:
    session.add(new_player)
    # 提交即保存到数据库:
    session.commit()
    session.close()
    # 查询身高 >=2.08 的球员有哪些
    rows_1 = session.query(Player).filter(Player.height >= 2.08).all()
    print([row.to_dict() for row in rows_1])

    rows_2 = session.query(Player).filter(or_(Player.height >=2.08, Player.height <=2.10)).all()
    print([row.to_dict() for row in rows_2])
    rows_3 = session.query(Player.team_id, func.count(Player.player_id)).group_by(Player.team_id).having(func.count(Player.player_id)>5).order_by(func.count(Player.player_id).asc()).all()
    print(rows_3)


    row = session.query(Player).filter(Player.player_name=='索恩-马克').first()
    row.height = 2.19
    session.commit()
    # 关闭 session:
    session.close()

    row = session.query(Player).filter(Player.player_name == ' 约翰 - 雪诺 ').first()
    session.delete(row)
    session.commit()
    session.close()

已经完成了数据的插入和查询操作，但在后面的删除操作中进行条件等值查询时，报出一个Nonetype的error，正在解决错误原因，后续添上！

问题已经解决，由于之前插入的数据有不恰当的空格，导致后面删除某一数据行时由于没有正确输入要删除的字段而报错。


版权声明：本文为weixin_42564710原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
Tags: