【Python】SQLAlchemy使用方法介绍

ORM框架SQLAlchemy
SQLAlchemy作用是提供简单的规则，自动转换成SQL语句

ORM框架两种模式

• DB first: 手动创建数据库以及表 -> ORM框架 -> 自动生成类
• code first： 手动创建类、和数据库 -> ORM框架 -> 自动生成表

对于Django中的ORM框架两种模式都支持，对于SQLAlchemy默认是code first，若使用DB first模式，还要安装第三方工具

3 SQLAlchemy功能

使用类与对象转换为SQL语句，但是连接数据库不是SQLAlchemy做的，是由pymyql,mysqldb等模块做的

4 使用SQLAlchemy操作数据库

在SQLAlchemy中，利用类和对象去操作数据表和数据行，类=数据表，对象==数据行

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

Base = declarative_base()

class UserType(Base): #必须继承Base类，创建UserType类，等于创建数据表UserType
__tablename__ = 'usertype'
#创建id列，整数类型，主键，自动增加
id = Column(Integer, primary_key=True, autoincrement=True)
#创建title列，varchar类型，可为空，创建普通索引
title = Column(VARCHAR(32), nullable=True, index=True)

# __table_args__ = (
# UniqueConstraint('id', 'name', name='uix_id_name'), #为id，name列创建联合唯一索引
# Index('ix_n_ex','name', 'email',), #为name，email列创建联合索引，第一个参数为联合索引的名字
# )
class Users(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(VARCHAR(32), nullable=True, index=True)
email = Column(VARCHAR(16), unique=True)
user_type_id = Column(Integer,ForeignKey("usertype.id"))
user_type = relationship("UserType",backref='xxoo') #在定义外键的类创建relationship就行

def create_db():
engine = create_engine("mysql+pymysql://root:12345678@127.0.0.1:3306/s4day62db?charset=utf8", max_overflow=5) #连接到数据库，格式为("数据库名称+连接版块://用户名:密码@主机ip地址:端口号/所连数据库名称？charset=编码方式",最大连接数)
Base.metadata.create_all(engine) #将页面中的所有类，转换为sql语句并创建数据表

def drop_db():
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/s4day62db?charset=utf8", max_overflow=5)
Base.metadata.drop_all(engine) #删除数据库中相应数据表

====增，删，改，查====
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/s4day62db? charset=utf8", max_overflow=5) #先连接
Session = sessionmaker(bind=engine)
session = Session() #拿一个连接过来

# 类 -> 表
# 对象 -> 行
# ###### 增加 ######
#
# obj1 = UserType(title='普通用户')
# session.add(obj1) #添加1条数据

# objs =[
# UserType(title='超级用户'),
# UserType(title='白金用户'),
# UserType(title='黑金用户'),
# ]
# session.add_all(objs) #批量添加数据

# ###### 查 ######
# print(session.query(UserType)) #打印出sql语句
# user_type_list = session.query(UserType).all() #拿到UserType表中的所有对象所组成的列表
# for row in user_type_list:
# print(row.id,row.title)

# select xxx UserType where
# user_type_list = session.query(UserType.id,UserType.title).filter(UserType.id > 2) #filter相当于查询中的where条件语句
# for row in user_type_list:
# print(row.id,row.title)

#查询还包括 分组，排序，连表，通配符，子查询，limit等

###连表，连表方式1###
# ret = session.query(Users, UserType).filter(Users.usertype_id==UserType.id)
# select * from user,usertype whre user.usertype_id = usertype.id

###连表，连表方式2，利用join关键字###
# result = session.query(Users).join(UserType) #默认是inner join
# print(result)

# result = session.query(Users).join(UserType,isouter=True) #isouter为true时，表示left join
# print(result)

###子查询的3种情况
#1. select * from b where id in (select id from tb2)

#2. select * from (select * from tb) as B
# q1 = session.query(UserType).filter(UserType.id > 0).subquery()
# result = session.query(q1).all()
# print(result)

#3. select id, (select * from users where users.user_type_id=usertype.id) from usertype;
# session.query(UserType,session.query(Users).filter(Users.id == 1).subquery()) #还是会返回笛卡尔积，因为子查询的结果还是一张表
# session.query(UserType,Users) #直接查两张表会返回笛卡尔积
# result = session.query(UserType.id,session.query(Users).as_scalar())
# print(result)
# result = session.query(UserType.id,session.query(Users).filter(Users.user_type_id==UserType.id).as_scalar())
# print(result) #将查询结果作为一个元素返回

# ###### 删除 ######
# session.query(UserType.id,UserType.title).filter(UserType.id > 2).delete()

# ###### 修改 ######
# session.query(UserType.id,UserType.title).filter(UserType.id > 0).update({"title" : "黑金"})
# session.query(UserType.id,UserType.title).filter(UserType.id > 0).update({UserType.title: UserType.title + "x"}, synchronize_session=False) # synchronize_session=False表示字符串类型相加，简单拼接
# session.query(UserType.id,UserType.title).filter(UserType.id > 0).update({"num": Users.num + 1}, synchronize_session="evaluate") #synchronize_session="evaluate"表示数字类型相加

session.commit() #对增，删，改需要用commit()
session.close()

获得用户的关系

#问题1. 获取用户信息以及与其关联的用户类型名称(FK外键,Relationship=>正向操作)

user_list = session.query(Users,UserType).join(UserType,isouter=True) #不加all返回是一个迭代器
print(user_list)
# for row in user_list: #迭代结果是由Users与UserType对象组成的元祖
# print(row[0].id,row[0].name,row[0].email,row[0].user_type_id,row[1].title)
#row[0].id 表示选择元祖的第一个元素即Users对象中的id列，其他同理
# user_list = session.query(Users.name,UserType.title).join(UserType,isouter=True).all() #加all之后返回的是User.name与UserType.title元祖为一个元素组成的列表
# for row in user_list:
# print(row[0],row[1],row.name,row.title)

#使用了relationship之后，查询不需要外键，直接查就可以
# user_list = session.query(Users)
# for row in user_list:
# print(row.name,row.id,row.user_type.title)

# 问题2. 获取用户类型
# type_list = session.query(UserType)
# for row in type_list:
# print(row.id,row.title,session.query(Users).filter(Users.user_type_id == row.id).all()) #外键连表，这里没有用join

#使用relationship之后，查询更简单，定义relationship在定义Users类的时候
# type_list = session.query(UserType)
# for row in type_list:
# print(row.id,row.title,row.xxoo)