Python 数据库骚操作 -- MySQL

小小詹同学

发布于 2019-11-12 22:57:39

5960

发布于 2019-11-12 22:57:39

文章被收录于专栏：小詹同学

阅读本文大约需要 13 分钟

前言

今天这篇主要介绍 MySQL 的 orm 库 SQLAlchemy 。那什么是 orm 呢？Object Relational Mapper，描述程序中对象和数据库中数据记录之间的映射关系的统称。介绍完了，那就走起呗！

MySQL GUI 工具

首先介绍一款 MySQL 的 GUI 工具 Navicat for MySQL，初学 MySQL 用这个来查看数据真的很爽。可以即时看到数据的增删改查，不用操作命令行来查看。

Navicat for MySQL

MySQL 遇上 Docker

继续分享一下 Docker compose 代码片段，用过 docker 之后，我相信你再也不会为了配置各种开发环境而烦恼了。

version: '3' services: mysql_container: image: mysql ports: - "3306:3306" volumes: - /usr/local/db/mysql:/var/lib/mysql # - /root/docker/test-mysql/conf.d:/etc/mysql/conf.d environment: - MYSQL_DATABASE=dbname - MYSQL_ROOT_PASSWORD=your_password

增删改查

首先定义表结构

# 创建单表 class Users(Base): # 表名 __tablename__ = 'users' id = Column(BIGINT, primary_key=True, autoincrement=True) # 定义字段 name = Column(String(32)) age = Column(Integer()) # 初始化数据库 def init_db(): Base.metadata.create_all(engine) # 删除数据库 def drop_db(): Base.metadata.drop_all(engine)

连接

from sqlalchemy import create_engine, Column, Integer, String, BIGINT, ForeignKey, UniqueConstraint, Index, and_, or_, inspect from sqlalchemy.orm import sessionmaker, relationship,contains_eager # echo 为 True 将会打印 SQL 原生语句 engine = create_engine('mysql+pymysql://username:password@localhost:3306/db_name',echo=True) from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() Session = sessionmaker(bind=engine) session = Session()

增加

new_user = Users(name='zone', age=18) session.add(new_user) # 批量添加 session.add_all([ User(name='zone2', age=25), User(name='zone3', age=32) ]) # 提交 session.commit()

删除

session.query(User).filter_by(name="zone").delete() # 提交 session.commit()

修改

session.query(User).filter(User.name == 2).update({"name": "new name"}) session.query(User).filter(User.id >= 3).update({User.name: "关注公众号【zone7】"}, synchronize_session=False) session.query(User).filter(User.age == 50).update({"age": 123}, synchronize_session="evaluate") session.commit()

查找

查找的需求会比较多变，我这边就列出比较常见的查询需求。

result = session.query(User).all() # 结果为一个列表 result = session.query(User.id, User.age).all() result = session.query(User).filter_by(name='zone').first() result = session.query(User).filter_by(name='zone2').all() # 与、或 result = session.query(User).filter_by(and_(name='zone5',age="23")).all() result = session.query(User).filter_by(or_(name='zone5',age="23")).all() # 模糊查询 result = session.query(User).filter(User.name.like('zon%')).all() # 排序 result = session.query(User).order_by(User.age.desc()).all() # 分页查询 result = session.query(User).offset(1).limit(1).all()

一对多

关系型数据库，少不了各种表与表的关系。back_populates 在一对多的关系中建立双向的关系，这样的话在对方看来这就是一个多对一的关系。

def one_to_many(): class Parent(Base): __tablename__ = 'parent' id = Column(Integer, primary_key=True) children = relationship("Child", back_populates="parent") class Child(Base): __tablename__ = 'child' id = Column(Integer, primary_key=True) parent_id = Column(Integer, ForeignKey('parent.id')) parent = relationship("Parent", back_populates="children") name = Column(String(32)) # 子表类中附加一个 relationship() 方法 # 并且在(父)子表类的 relationship() 方法中使用 relationship.back_populates 参数 drop_db() init_db() child1 = Child(name="zone1") child2 = Child(name="zone2") parent = Parent(children=[child1, child2]) session.add(parent) session.commit() result = session.query(Parent).join(Child).first() print(object_as_dict(result.children[0])) one_to_many()

运行结果

parent

children

一对一

参数 back_populates 指定双向关系，参数 uselist=False 需要在一对多关系基础上，父表中使用 uselist 参数来表示。

def one_to_one(): class Parent(Base): __tablename__ = 'parent' id = Column(Integer, primary_key=True) child = relationship("Child", uselist=False, back_populates="parent") class Child(Base): __tablename__ = 'child' id = Column(Integer, primary_key=True) parent_id = Column(Integer, ForeignKey('parent.id')) parent = relationship("Parent", back_populates="child") name = Column(String(32)) # 清空数据库，并且重新初始化 drop_db() init_db() child = Child(name="zone") parent = Parent(child=child) session.add(parent) session.commit() result = session.query(Parent).join(Child).first() print(object_as_dict(result.child)) one_to_one()

parent

child

多对多

多对多关系会在两个类之间增加一个关联的表来表示其中的关系。这个关联的表在 relationship() 方法中通过 secondary 参数来表示。通常，这个表会通过 MetaData 对象来与声明基类关联。

def many_to_many(): association_table = Table('association', Base.metadata, Column('left_id', Integer, ForeignKey('left.id')), Column('right_id', Integer, ForeignKey('right.id')) ) class Parent(Base): __tablename__ = 'left' id = Column(Integer, primary_key=True,autoincrement=True) children = relationship( "Child", secondary=association_table, back_populates="parents") class Child(Base): __tablename__ = 'right' id = Column(Integer, primary_key=True,autoincrement=True) name = Column(String(32)) parents = relationship( "Parent", secondary=association_table, back_populates="children") # 清空数据库，并且重新初始化 drop_db() init_db() child1 = Child(name="zone1") child2 = Child(name="zone2") child3 = Child(name="zone3") parent = Parent() parent2 = Parent() # parent 添加 child parent.children.append(child1) parent.children.append(child2) parent2.children.append(child1) parent2.children.append(child2) # save session.add(parent) session.add(parent2) session.commit() # 查询 result = session.query(Parent).first() print(object_as_dict(result)) print(object_as_dict(result.children[1])) result2 = session.query(Child).first() print(object_as_dict(result2)) print(object_as_dict(result2.parents[1])) many_to_many()