python数据库框架orm_python笔记(数据库：ORM框架（sqlalchemy）)

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index, VARCHAR

from sqlalchemy.orm import sessionmaker, relationship

from sqlalchemy import create_engine

Base = declarative_base()

# 创建表单

class UserType(Base): #继承base父类

__tablename__ = 'usertype' #表的名字

id = Column(Integer, primary_key=True, autoincrement=True)# 创建表行 Integer：int型

title = Column(VARCHAR(24), nullable=True, index=True) #nullable：是否为空 index：是否添加索引

class User(Base):

__tablename__ = 'user'

id = Column(Integer, primary_key=True, autoincrement=True)

name = Column(VARCHAR(32), nullable=True, index=True)

email = Column(VARCHAR(32), unique=True)

user_type_id = Column(Integer, ForeignKey('usertype.id')) #创建外键

user_type = relationship('UserType', backref='xxoo') # 和UserType建立关系

# 添加表

def create_db():

engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/db2?charset=utf8', max_overflow=5)

Base.metadata.create_all(engine)

# 删除表

# def drop_db():

# engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/db2?charset=utf8',max_overflow=5)

# Base.metadata.drop_all(engine)

create_db()

#建立会话

engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/db2?charset=utf8', max_overflow=5)

Session = sessionmaker(bind=engine)

session = Session()

# #添加一条数据

# obj1 = UserType(title = 'haha')

# session.add(obj1)

# #添加多条数据

# objs = [

# UserType(title = 'haha'),

# UserType(title = 'hehe'),

# UserType(title = 'hihi'),

# ]

# session.add_all(objs)

# 查看数据

# user_type_list = session.query(UserType).all()

# for row in user_type_list:

# print(row.id,row.title)

'''

#条件

#filter_by:传的是参数 filter：传的是表达式

user_type_list = session.query(UserType).filter_by(title='xixi').all()

user_type_list = session.query(UserType).filter(UserType.id>1,UserType.title=='xixi').all() #默认是and

user_type_list = session.query(UserType).filter(UserType.id.between(1,3),UserType.title=='xixi').all()

user_type_list = session.query(UserType).filter(UserType.id.in_([1,2,3])).all()

user_type_list = session.query(UserType).filter(~UserType.id.in_([1,2,3])).all()

user_type_list = session.query(UserType).filter(UserType.id.in_(session.query(UserType.id).filter_by(title=='xixi'))).all()

from sqlalchemy import and_,or_

user_type_list = session.query(UserType).filter(and_(UserType.id>1,UserType.title=='xixi')).all()

user_type_list = session.query(UserType).filter(or_(UserType.id>1,UserType.title=='xixi')).all()

user_type_list = session.query(UserType).filter(

or_(

UserType.id<4,

and_(UserType.id>1,UserType.title=='xixi')

)

).all()

#通配符

user_type_list = session.query(UserType).filter(UserType.title.like('x%')).all()

user_type_list = session.query(UserType).filter(~UserType.title.like('x%')).all()

#限制

user_type_list = session.query(UserType)[1:2]

#排序(asc：从小到大排)

user_type_list = session.query(UserType).filter(UserType.title.desc()).all()

user_type_list = session.query(UserType).filter(UserType.id.asc(),UserType.title.desc()).all() # 先排UserType.id.asc()

#分组

from sqlalchemy.sql import func

user_type_list = session.query(UserType).group_by(UserType.title).all()

user_type_list = session.query(

func.max(UserType.id),

func.count(UserType.id),

func.min(UserType.id),

func.sum(UserType.id)).group_by(UserType.title).all()

user_type_list = session.query(

func.max(UserType.id),

func.min(UserType.id),

func.sum(UserType.id)).group_by(UserType.title).having(func.min(UserType.id)>2).all()

#连表：

user_type_list = session.query(UserType,User) # 相当于select * from UserType, User

user_type_list = session.query(UserType,User).filter(UserType.id == User.usertype_id) # 相当于select * from UserType, User where UserType.id = User.usertype_id

user_type_list = session.query(UserType).join(User) #相当于inner join

user_type_list = session.query(UserType).join(User,isouter=Ture) #相当于left join

#组合

#上下连(去重)

q1 = session.query(UserType.title).filter(UserType.id>2)

q2 = session.query(User.name).filter(User.id<2)

ret = q1.union(q2).all()

#上下连(不去重)

q1 = session.query(UserType.title).filter(UserType.id>2)

q2 = session.query(User.name).filter(User.id<2)

ret = q1.union_all(q2).all()

#其他

(1)select * from UserType where UserType.id in ()

ret = session.query(UserType).filter(UserType.in_([1,2,3])).all()

(2)select * from (select * from tb) as B

q1 = session.query(UserType).filter(UserType.id>2).subquery() #当成子查询

ret = session.query(q1).all()

(3)select id,(select id from xx where id=x) from xxx;

ret = session.query(UserType.id,session.query(User).filter(User.id==1).subquery()).all()

(4)select id,(select id from xx where xxx.id=xx.id) from xxx;

1)ret = session.query(UserType.id,session.query(User).filter(User.id==UserType.id).as_scalar()).all()

#问题1(将UserType与User连表，取值)

(1)无relationship

user_list = session.query(UserType.title,User.name).join(User,isouter=Ture)

for row in user_list:

print(row.title,row.name)

(2)有relationship

user_list = session.query(UserType)

for row in user_list:

print(row.title,row.name)

#问题2 获取用户类型

(1)无relationship

user_list = session.query(UserType)

for row in user_list:

print(row.title,session.query(User).filter(User.user_type_id==row.id).all())

(2)有relationship

user_list = session.query(UserType)

for row in user_list:

print(row.title,row.xxoo)

'''

# user_type_list = session.query(UserType).filter(UserType.id>1) #可以设定条件

# for row in user_type_list:

# print(row.id,row.title)

# user_type_list = session.query(UserType.title).filter(UserType.id>1) #可以设定查看的参数

# for row in user_type_list:

# print(row.title)

# 删除(先查后删)

# session.query(UserType.title).filter(UserType.id>1).delete()

# 修改(第一种写法)：满足条件的会全部修改

# user_type_list = session.query(UserType.title,UserType.title).filter(UserType.id>2).update({UserType.title:'xixi'})

# 修改(第二种写法)：在原数据的基础上拼接字符

# user_type_list = session.query(UserType.id,UserType.title).filter(UserType.id>2).update({UserType.title:UserType.title+'00'},synchronize_session=False)

# 在原数据的基础上拼接数字

# user_type_list = session.query(UserType.id,UserType.title).filter(UserType.id>2).update({UserType.id:UserType.id+1},synchronize_session='evaluate')

session.commit()

session.close()