python orm框架用哪个好 python的orm框架

面向对象：

　　共同参数分类

　　模板"约束"

　　　　一类事物共同拥有的属性和行为

obj1()　　# 调用__call__()

obj1['k']　　# 调用__get__item()

obj1['k'] = 123　　# 调用__setitem__()

del obj1['k']　　# 调用__delitem__()

obj.__dict__　　# 显示所有属性

 

ORM框架：SQLAlchemy

　　- 作用：

　　　　1.提供简单的规则

　　　　2.自动转换成SQL语句

　　- DB first:手动创建数据库和表，自动生成类

　　- code first：手动创建类和数据库，自动生成表　　# 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()


# 创建单表
"""
1   白金
2   黑金
反向
obj.xx ==> [obj,obj...]
"""
class UserType(Base):
    __tablename__ = 'usertype'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(VARCHAR(32), nullable=True, index=True)

"""
1   方少伟   1
2   成套     1
3   小白     2
# 正向
ut = relationship（backref='xx'）
obj.ut ==> 1   白金
"""
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')     # 表内建立关系，哪里有foreignkey写在哪里
    __table_args__ = (
        UniqueConstraint('id', 'name', name='uix_id_name'),
        Index('ix_n_ex','name', 'email',),
    )


def create_db():
    engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/s4day62db?charset=utf8", max_overflow=5)
    Base.metadata.create_all(engine)

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)

objs =[
  UserType(title='超级用户'),
  UserType(title='白金用户'),
  UserType(title='黑金用户'),
]
session.add_all(objs)

###### 查 ######
print(session.query(UserType))  # SQL语句
user_type_list = session.query(UserType).all()
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)
for row in user_type_list:
    print(row.id,row.title)


ret = session.query(Users, UserType)

ret = session.query(Users, UserType).filter(Users.usertype_id==UserType.id)

result = session.query(Users).join(UserType)    # 自动找到外键，默认innerjoin
print(result)

result = session.query(Users).join(UserType,isouter=True)   # leftjoin
print(result)




# 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()    # 不加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)






# 问题1. 获取用户信息以及与其关联的用户类型名称(FK,Relationship=>正向操作)
user_list = session.query(Users,UserType).join(UserType,isouter=True)
print(user_list)
for row in user_list:
    print(row[0].id,row[0].name,row[0].email,row[0].user_type_id,row[1].title)

user_list = session.query(Users.name,UserType.title).join(UserType,isouter=True).all()
for row in user_list:
    print(row[0],row[1],row.name,row.title)


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())

type_list = session.query(UserType)
for row in type_list:
    print(row.id,row.title,row.xxoo)






###### 删除 ######
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) # 字符串参数
session.query(UserType.id,UserType.title).filter(UserType.id > 0).update({"num": Users.num + 1}, synchronize_session="evaluate")    # 数字参数


session.commit()
session.close()