day-45mysql
SQLAlchemy
回顾一下, 之前操作MySQL, 用PyMySQL操作MySQL
使用PyMySQl的前提条件:
1. 表先建好
2. 自己需要手动去写多条SQL语句
改进:
类 ----> 表
用类生成表
实例化----> 数据
将类实例化生成表中数据
这种思想叫: ORM (Object Relationship Mapping) 对象关系映射
基于这种思想开发的产品, Python比较出名的ORM框架: SQLAlchemy
SQLAlchemy的操作:
基本原理:
将代码转换成SQL语句执行
安装:
pip3 install SQLAlchemy
使用SQLAlchemy连接mysql:
底层使用 pyMySQL来进行操作的
注意:
1. 字符编码:
创建数据库的时候:
create database db3 charset=utf8;
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/db3?charset=utf8", max_overflow=5)
具体操作：
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine # 连接MySQL engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/db3?charset=utf8", max_overflow=5) Base = declarative_base() # 元类生成Base类 ''' create table users( id int auto_increment primary key, name varchar(32) not null default '', extra varchar(32) not null default '' ) ''' ''' Usertype id title xxoo 1 普通用户 row.xxoo : 多条记录对象 ''' class UserType(Base): __tablename__ = 'usertype' # 表名 id = Column(Integer, autoincrement=True, primary_key=True) title = Column(String(32), nullable=False, server_default='') # Colum 字段/列 ''' users id name extra type_id 1 zekai nb 1 usertype = releationship('Usertype') row.usertype ''' class Users(Base): __tablename__ = 'users' id = Column(Integer, autoincrement=True, primary_key=True) name = Column(String(32), nullable=False, server_default='',unique=True) # 后面的唯一索引 extra = Column(String(32), nullable=False, server_default='') type_id = Column(Integer, ForeignKey(UserType.id)) #外键 usertype = relationship('UserType', backref='xxoo') # UserType ：查询某一个用户的用户类型（父查子） 正向查询 # 在Users表中创建一个隐藏usertype列，这个列包含了UserType所有数据 # 多对一 # xxoo： 某一个类型下面的用户(子查父) 反向查询 # 在UserType表中创建一个隐藏Users列，这个列包含了Users所有数据 # 一对多 ''' __table_args__ = ( UniqueConstraint('id', 'name', name='uix_id_name'), # 联合唯一索引 Index('ix_name_extra', 'name', 'extra'), # 组合索引 ) ''' def drop_db(): Base.metadata.drop_all(engine) def create_db(): # 会将当前执行文件中所有继承自Base类的类,生成多张表 Base.metadata.create_all(engine) drop_db() create_db() #操作表中的数据 Session = sessionmaker(bind=engine) session = Session() #实例化Session相当从连接池拿出一条进行操作 #session就相当一个cmd界面 # 增加一条数据 # obj = UserType(title='普通用户') # session.add(obj) 类实例化一个对象，将对象添加到数据库 # # 添加多条数据 # session.add_all([ # UserType(title='VIP用户'), 对象相当于数据库的一行数据 # UserType(title='VIP中P用户'), # UserType(title='SVIP用户'), # UserType(title='黑金用户') # ]) # 查询: # 查询全部 # 返回的是一个列表, 列表中套对象 # res = session.query(UserType).all() # query查询的意思 # for row in res: # print(row.id, row.name) # 查询一条数据 # res = session.query(UserType).first() # print(res) # where条件 # res = session.query(UserType).filter(UserType.name=='VIP用户', UserType.id==2).all() # for row in res: # print(row.id, row.name) # filter 过滤的意思 # print(res[0].name, res[0].id) #一条数据的时候 # res = session.query(UserType).filter_by(name='VIP用户').all() #根据列名查 # print(res) # 删除: # session.query(UserType).filter(UserType.id>3).delete() # 修改 # session.query(UserType).filter(UserType.id == 3).update({"name" : "SVIP用户"}) # MySQL 高级查询操作 # 通配符
ret = session.query(Users).filter(Users.name.like('e%')).all()
ret = session.query(Users).filter(~Users.name.like('e%')).all()
# between # res = session.query(UserType).filter(UserType.id.between(1,3)).all() # for row in res: # print(row.id, row.title) # in 操作 # res = session.query(UserType).filter(UserType.id.in_([1,3,4])).all() # print(res)
# 限制 limit
ret = session.query(Users)[1:2]
# 排序
ret = session.query(Users).order_by(Users.name.desc()).all()
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()
# not in（取反） # res = session.query(UserType).filter(~UserType.id.in_([1,3,4])).all() # for row in res: # print(row.id, row.title) # # session.query(UserType).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all() # 子查询 # res = session.query(UserType.title).filter(~UserType.id.in_([1,3,4])).all() # for row in res: # print( row.title)
#and_,or # from sqlalchemy import and_, or_ and_,or_必须先导模块
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
#分组 from sqlalchemy.sql import func # res = session.query( # Users.type_id, # func.max(Users.id), # func.min(Users.id)).group_by(Users.type_id).all() # # print(res) ret = session.query( func.max(Users.id), func.min(Users.id)).group_by(Users.type_id).having(func.min(Users.id) >2).all()
#连表 # left join #1. 查询某一个用户的用户类型 # 第一种方法: # res = session.query(Users,UserType).join(UserType, isouter=True).all() #isouter=True把内连接变做左连接 # # print(res) #注意：返回的对象时列表套元祖 # for row in res: # print(row[0].id, row[0].name, row[1].title) # 第二种方法 # res = session.query(Users).all() # for row in res: # print(row.id, row.name, row.extra, row.usertype.title) # 2. 某一个类型下面的用户 # 第一种方法 # res = session.query(UserType).all() # for row in res: # print(row.id, row.title, session.query(Users).filter(Users.type_id == row.id).all() ) # 第二种方法 # res = session.query(UserType).all() # # for row in res: # print(row.id, row.title, row.xxoo) session.commit() session.close()
DB first : 先有DB, 在有后续的操作
例如pymysql，先有表，再有sql语句
Code first : 先有代码, 通过代码创建DB
例如SQLALchemy，先有代码，再有DB
