SQLAlchemy的增删改查
1.连接数据库
# 连接数据库
engine = create_engine('DB+DBAPI://⽤户名：密码@主机名：端⼝号/数据库名称?charset=字符编码', max_overflow=5) # DB 是所⽤的数据库（mysql，postgreSQL，oracle等等）
# DBAPI （pymysql, psycopg2, mysqlconnector等等）
# max_overflow 最⼤连接数
Session = sessionmaker(bind=engine)
session = Session()
2.创建表
declarative import declarative_base
import sessionmaker, relationship
from sqlalchemy import create_engine
from sqlalchemy import Column, String, Interger, Foreignkey
from sqlalchemt import join, outerjoin, or_, union, union_all
Base = declarative_base()
# 创建表
class UserType(Base):
__tablename__ ='usertype'
id= Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(10), nullable=True)
integral = Column(Integer)
class User(Base):
__tablename__ ='user'
id= Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(10), nullable=True)
gender = Column(String(1), nullable=True)
email = Column(string(30), nullable=True, unique=True)
user_type_id = Column(Interger, primary('usertype.id')
# 这个不会添加到数据库中作为⼀个字段，是为了⽅便以后的连表查询
user_type = relationship(UserType, backref='usertype_back')
3.创建表到数据库
# 创建表
# 删除表
# 类 -----> 表
#对象 -----> 数据⾏
4.增
# 对于增删改后⾯都要进⾏提交
# 最后加上 sessionmit()
# 然后关闭 session.close()
# 增
# 添加⼀⾏数据
# ⾃增字段可不⽤⼿动添加
value = UserType('普通会员')
session.add(value)
# 添加多⾏数据
value_list =[
UserType('铂⾦会员',0),
mysql菜鸟教程增删改查UserType('黄⾦会员',0),进度条代码
UserType('钻⽯会员',0)
]
session.add_all(value_list)
5.删
# 删
# 全删
session.query(UserType).delete()
# 按条件进⾏删除
session.query(UserType).filter(UserType.id==2).delete()
6.改
# 改
numericupdownsessionn.query(UserType).filter(UserType.id==2).update({'title':'星耀会员'})
session.query(UserType).filter(UserType.id==1).update({UserType.title: UserType.title+str(1), synchronize_session=False)
session.query(UserType).filter(UserType.id==1).update({UserType.integral: UserType.integral+1, synchronize_session=evaluate)
sessionmit()
session.close()
7.查
# 查
result = session.query(UserType).all()
# 直接打印result，这样返回的是UserType的对象(如下)，需要⽤for循环挨个取值
# [<__main__.UserType object at 0x000001A53B674B08>, <__main__.UserType object at 0x000001A53B674F88>, <__main__.UserType object at 0x000 001A53B69B048>, <__main__.UserType object at 0x000001A53B69B0C8>]
for row in reslut:
print(row.id, row.title)
result = session.query(UserType.id, UserType.title).all()
# 这样打印result可以直接返回值
# 通过⽤filter进⾏条件查询
result = session.query(UserType).filter(UserType.id==2).all()
scanf输入整型数组# and,or filter⾥有多个条件默认为
result = session.query(UserType).filter(UserType.id>1,UserType.title=='黄⾦会员')
result = session.query(UserType).filter(and_(UserType.id>1,UserType.title=='黄⾦会员'))
result = session.query(UserType).filter(or_(UserType.id>1,UserType.title=='黄⾦会员'))
# 限制，类似mysql的limit
split baby是什么意思result = session.query(UserType).filter(UserType.id>1,UserType.title=='黄⾦会员')[1:3]
# in
result = session.query(UserType).filter(UserType.id.in_([1,3]))
# not in
result = session.query(UserType).filter(~UserType.id.in_([1,3]))
# between
result = session.query(UserType).filter(UserType.id.between(1,3))
# filter ⾥插⼊另⼀个结果集
# filter ⾥插⼊另⼀个结果集
s1 = session.query(User.id).filter(User.id>2)
result = session.query(UserType).filter(UserType.id.in_(s1))
# 通配符
result = session.query(UserType).filter(UserType.title.like('铂⾦会_'))
result = session.query(UserType).filter(UserType.title.like('%⾦会员'))
# 排序
result = session.query(UserType).order_by(UserType.id.desc()).all()
# 分组
result = session.unt(User.id), func.max(User.id), func.min(User.id)).group_by(User.user_type_id).all() # 连表
result = session.query(User).join(UserType, isouter=True)
# 也可以这样
result = session.query(User).outerjoin(UserType)
# union(去重),union_all(不去重)
s1 = session.query(User.id, User.name)
s2 = session.query(UserType.id, UserType.title)
result = s1.union_all(s1)
# 笛卡尔积
result = session.query(User, UserType)
# 临时表,⼦查询 label()对查询的字段进⾏命名⼦查询的结果集.c.字段，进⾏对⼦查询的结果集进⾏筛选
select *from(select *from Usertype where id>=1)as A;
s3 = session.query(UserType.id, UserType.title).filter(UserType.id>1).subquery()
result = session.query(s3).id==2)
s4 = session.query(UserType.title)
python递归函数和生成器函数result = session.query(User.id, User.name, s4.filter(User.user_type_id==UserType.id).as_scalar())
# relationship 正向查询
result = session.query(User)
print(result)
for row in result:
print(row.id, row.name, row.user_type.title)
# 反向查询
result = session.query(UserType)
for row in result:
print(row.id, row.title,[col.name for col in row.user_type_back])
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论