day63
参考：http://www.cnblogs.com/wupeiqi/articles/5713330.html
SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：
MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...] 更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html |
可以连接不同的数据库。
1. ORM框架：SQLAlchemy
- 作用：
1. 提供简单的规则
2. 自动转换成SQL语句
- DB first: 手动创建数据库以及表 -> ORM框架 -> 自动生成类
- code first： 手动创建类、和数据库 -> ORM框架 -> 以及表
a. 功能
- 创建数据库表
- 连接数据库（非SQLAlchemy,pymyql,mysqldb,....）
- 类转换SQL语句
- 操作数据行
增
删
改
查
- 便利的功能
1 from sqlalchemy.ext.declarative import declarative_base 2 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 3 from sqlalchemy.orm import sessionmaker, relationship 4 from sqlalchemy import create_engine 5 6 7 8 Base = declarative_base() 9 class UsersType(Base):#必须继承base 10 __tablename__ = 'usertype'#表名 #自增 11 id = Column(Integer, primary_key=True, autoincrement=True)#生成三列 Column 12 title = Column(String(32), nullable=True, index=True)#建立索引 13 14 # 创建单表 15 class Users(Base):#必须继承base 16 __tablename__ = 'users'#表名 #自增 17 id = Column(Integer, primary_key=True, autoincrement=True)#生成三列 Column 18 name = Column(String(32), nullable=True, default='sf', index=True)#建立索引 19 extra = Column(String(16), unique=True)#唯一索引 20 21 user_type_id = Column(Integer, ForeignKey("usertype.id"))#建立外键 user_type_id对应usertype中的id 22 23 __table_args__ = ( 24 UniqueConstraint('id', 'name', name='uix_id_name'), #联合唯一索引 25 Index('ix_id_name', 'name', 'extra'), #普通索引 26 ) 27 28 #连远程 127.0.0.1本机 配置 #最多5个连接 29 engine = create_engine("mysql+pymysql://root:112358@127.0.0.1:3306/nzp?charset=utf8", max_overflow = 5) 30 Base.metadata.create_all(engine)
实现增、删、改、查
1 from sqlalchemy.ext.declarative import declarative_base 2 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 3 from sqlalchemy.orm import sessionmaker, relationship 4 from sqlalchemy import create_engine 5 6 7 8 Base = declarative_base() 9 10 class UsersType(Base):#必须继承base 11 __tablename__ = 'usertype'#表名 #自增 12 id = Column(Integer, primary_key=True, autoincrement=True)#生成三列 Column 13 title = Column(String(32), nullable=True, index=True)#建立索引 14 15 # 创建单表 16 class Users(Base):#必须继承base 17 __tablename__ = 'users'#表名 #自增 18 id = Column(Integer, primary_key=True, autoincrement=True)#生成三列 Column 19 name = Column(String(32), nullable=True, default='sf', index=True)#建立索引 20 extra = Column(String(16), unique=True)#唯一索引 21 22 user_type_id = Column(Integer, ForeignKey("usertype.id"))#建立外键 23 24 __table_args__ = ( 25 UniqueConstraint('id', 'name', name='uix_id_name'), #联合唯一索引 26 Index('ix_id_name', 'name', 'extra'),#普通索引 27 ) 28 29 # #连远程 127.0.0.1本机 配置 #最多5个连接 30 # engine = create_engine("mysql+pymysql://root:112358@127.0.0.1:3306/nzp?charset=utf8", max_overflow = 5) 31 # Base.metadata.create_all(engine) #创建表 32 # #Base.metadata.drop_all(engine)# 删掉表 33 34 #建表 35 def create_db(): 36 engine = create_engine("mysql+pymysql://root:112358@127.0.0.1:3306/nzp?charset=utf8", max_overflow=5) 37 Base.metadata.create_all(engine) # 创建表 38 #删表 39 def drop_db(): 40 engine = create_engine("mysql+pymysql://root:112358@127.0.0.1:3306/nzp?charset=utf8", max_overflow=5) 41 Base.metadata.drop_all(engine) # 创建表 42 43 44 engine = create_engine("mysql+pymysql://root:112358@127.0.0.1:3306/nzp?charset=utf8", max_overflow=5) 45 Session = sessionmaker(bind=engine) 46 session = Session() 47 48 # # 增 49 # # 添加一行 50 # # obj1 = UsersType(title="狗剩")#给usertype表添加 51 # # session.add(obj1) 52 # '''添加多行数据''' 53 # objs = [ 54 # UsersType(title='tghh'), 55 # UsersType(title='sdcs'), 56 # UsersType(title='cbsu'), 57 # ] 58 # session.add_all(objs) 59 # session.commit() 60 61 # #查 62 # user_type_list = session.query(UsersType).all()#其中每一个元素是UserType类型，每一个对象是一行数据 63 # print(type(user_type_list[0])) #row是UsersType类对象 ，类代指表 64 # for row in user_type_list: 65 # print(row.id, row.title) 66 67 # #查部分 #相当于映射 #相当于where 68 # user_type_list = session.query(UsersType.id, UsersType.title).filter(UsersType.id > 2)#其中每一个元素是UserType类型，每一个对象是一行数据 69 # for row in user_type_list: 70 # print(row.id, row.title) 71 72 # #删 73 # session.query(UsersType.id, UsersType.title).filter(UsersType.id > 2).delete() 74 # session.commit() 75 76 #改 77 #session.query(UsersType.id, UsersType.title).filter(UsersType.id > 0).update({'title':"nizhipeng"})#批量更改 78 #session.query(UsersType.id, UsersType.title).filter(UsersType.id > 0).update({UsersType.title: UsersType.title+"x"}, synchronize_session=False) 79 session.query(UsersType.id, UsersType.title).filter(UsersType.id > 0).update({"title": UsersType.id + 1}, synchronize_session="evaluate") 80 session.commit()#需要确认 81 82 83 session.close()
其他：
1 # 条件 2 #or,and,in,between 3 ret = session.query(Users).filter_by(name='alex').all() 4 ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all() 5 ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all() 6 ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() 7 ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() 8 ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all() 9 from sqlalchemy import and_, or_ 10 ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all() 11 ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all() 12 ret = session.query(Users).filter( 13 or_( 14 Users.id < 2, 15 and_(Users.name == 'eric', Users.id > 3), 16 Users.extra != "" 17 )).all() 18 19 20 # 通配符 21 ret = session.query(Users).filter(Users.name.like('e%')).all() 22 ret = session.query(Users).filter(~Users.name.like('e%')).all()#非 23 24 # 限制 25 ret = session.query(Users)[1:2] #切片limit 26 27 # 排序 28 ret = session.query(Users).order_by(Users.name.desc()).all() 29 ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all() 30 31 # 分组 32 from sqlalchemy.sql import func 33 34 ret = session.query(Users).group_by(Users.extra).all() 35 ret = session.query( 36 func.max(Users.id), 37 func.sum(Users.id), 38 func.min(Users.id)).group_by(Users.name).all() 39 40 ret = session.query( 41 func.max(Users.id), 42 func.sum(Users.id), 43 func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()#通过名字分组 44 45 # 连表 46 ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all() 47 48 ret = session.query(Person).join(Favor).all() 49 50 ret = session.query(Person).join(Favor, isouter=True).all() #out join 51 52 53 # 组合 54 q1 = session.query(Users.name).filter(Users.id > 2) 55 q2 = session.query(Favor.caption).filter(Favor.nid < 2) 56 ret = q1.union(q2).all()#上下连接 去重 57 58 q1 = session.query(Users.name).filter(Users.id > 2) 59 q2 = session.query(Favor.caption).filter(Favor.nid < 2) 60 ret = q1.union_all(q2).all() # 不去重
其中连表：
改变左右参数顺序，可设置right join或left join。
1 from sqlalchemy.ext.declarative import declarative_base 2 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index 3 from sqlalchemy.orm import sessionmaker, relationship 4 from sqlalchemy import create_engine 5 6 7 8 Base = declarative_base() 9 10 class UsersType(Base):#必须继承base 11 __tablename__ = 'usertype'#表名 #自增 12 id = Column(Integer, primary_key=True, autoincrement=True)#生成三列 Column 13 title = Column(String(32), nullable=True, index=True)#建立索引 14 15 # 创建单表 16 class Users(Base):#必须继承base 17 __tablename__ = 'users'#表名 #自增 18 id = Column(Integer, primary_key=True, autoincrement=True)#生成三列 Column 19 name = Column(String(32), nullable=True, default='sf', index=True)#建立索引 20 extra = Column(String(16), unique=True)#唯一索引 21 22 user_type_id = Column(Integer, ForeignKey("usertype.id"))#建立外键 23 24 __table_args__ = ( 25 UniqueConstraint('id', 'name', name='uix_id_name'), #联合唯一索引 26 Index('ix_id_name', 'name', 'extra'),#普通索引 27 ) 28 29 # #连远程 127.0.0.1本机 配置 #最多5个连接 30 # engine = create_engine("mysql+pymysql://root:112358@127.0.0.1:3306/nzp?charset=utf8", max_overflow = 5) 31 # Base.metadata.create_all(engine) #创建表 32 # #Base.metadata.drop_all(engine)# 删掉表 33 34 #建表 35 def create_db(): 36 engine = create_engine("mysql+pymysql://root:112358@127.0.0.1:3306/nzp?charset=utf8", max_overflow=5) 37 Base.metadata.create_all(engine) # 创建表 38 #删表 39 def drop_db(): 40 engine = create_engine("mysql+pymysql://root:112358@127.0.0.1:3306/nzp?charset=utf8", max_overflow=5) 41 Base.metadata.drop_all(engine) # 创建表 42 43 44 engine = create_engine("mysql+pymysql://root:112358@127.0.0.1:3306/nzp?charset=utf8", max_overflow=5) 45 Session = sessionmaker(bind=engine) 46 session = Session() 47 48 # #连表 49 # result = session.query(Users).join(UsersType).all()#INNER JOIN usertype ON usertype.id = users.user_type_id 50 # print(result) 51 52 53 # #子查询 54 # #select * from (select * from tb) as B 55 # q1 = session.query(UsersType).filter(UsersType.id > 2).subquery()#子查询 #当做一张表 56 # result = session.query(q1).all() 57 # print(result) 58 59 # #select id, (select id from where user.user_type_id = 1) from xxx; 60 # result = session.query(UsersType.id, session.query(Users).filter(Users.id == 1).subquery()).all() 61 # #UsersType.id 加上 Users的第一行 62 # for row in result: 63 # print(row) 64 65 #select * from users where users.user_type_id = usertype.id; 66 result = session.query(UsersType.id, session.query(Users).filter(Users.user_type_id == UsersType.id).as_scalar())#作为一项 67 #session.query(Users).as_scalar()#作为一项#带括号(SELECT users.id, users.name, users.extra, users.user_type_id FROM users) 68 print(result) 69 70 71 session.close()
以上为连表与子查询。
relationship
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 Base = declarative_base() class UsersType(Base):#必须继承base __tablename__ = 'usertype'#表名 #自增 id = Column(Integer, primary_key=True, autoincrement=True)#生成三列 Column title = Column(String(32), nullable=True, index=True)#建立索引 # 创建单表 class Users(Base):#必须继承base __tablename__ = 'users'#表名 #自增 id = Column(Integer, primary_key=True, autoincrement=True)#生成三列 Column name = Column(String(32), nullable=True, default='sf', index=True)#建立索引 extra = Column(String(16), unique=True)#唯一索引 user_type_id = Column(Integer, ForeignKey("usertype.id"))#建立外键 __table_args__ = ( UniqueConstraint('id', 'name', name='uix_id_name'), #联合唯一索引 Index('ix_id_name', 'name', 'extra'),#普通索引 ) user_type = relationship('UsersType') # #连远程 127.0.0.1本机 配置 #最多5个连接 # engine = create_engine("mysql+pymysql://root:112358@127.0.0.1:3306/nzp?charset=utf8", max_overflow = 5) # Base.metadata.create_all(engine) #创建表 # #Base.metadata.drop_all(engine)# 删掉表 #建表 def create_db(): engine = create_engine("mysql+pymysql://root:112358@127.0.0.1:3306/nzp?charset=utf8", max_overflow=5) Base.metadata.create_all(engine) # 创建表 #删表 def drop_db(): engine = create_engine("mysql+pymysql://root:112358@127.0.0.1:3306/nzp?charset=utf8", max_overflow=5) Base.metadata.drop_all(engine) # 创建表 engine = create_engine("mysql+pymysql://root:112358@127.0.0.1:3306/nzp?charset=utf8", max_overflow=5) Session = sessionmaker(bind=engine) session = Session() # 问题1. 获取用户信息以及与其关联的用户类型名称(FK,Relationship=>正向操作) user_list = session.query(Users) for row in user_list: print(row.name, row.id, row.user_type.title)#row.user_type直接在usertype中将对应的title数据找到了 # 问题2. 获取用户类型 type_list = session.query(UsersType) for row in type_list: print(row.id, row.title, session.query(Users).filter(Users.user_type_id == row.id).all()) # type_list = session.query(UserType) # for row in type_list: # print(row.id,row.title,row.xxoo) session.close()