Tornado、Flask常用的ORM:sqlalchemy

sqlalchemy是什么

alchemy英文原意：炼金术;炼丹术;（改变事物、物质的）魔力（或方法）;（事物、物质的）神秘变化

sqlalchemy:sql炼金术，哈哈，这个名字很好听的ORM

　　

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   VIP
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   adamanter   1
2   antonio     1
3   eric     2
# 正向
ut = relationship（backref='xx'）
obj.ut ==> 1   vip 
"""
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')

　　

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):
    __tablename__ = 'usertype'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(VARCHAR(32), nullable=True, index=True)



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


    # relation里面是类的名字
    user_type = relationship("UserType", backref='back_users')

    # __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/hello?charset=utf8", max_overflow=5)
    Base.metadata.create_all(engine)

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

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/hello?charset=utf8", max_overflow=5)
Session = sessionmaker(bind=engine)
session = Session()


# 创建表
# create_db()
# engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/hello?charset=utf8", max_overflow=5)
# Base.metadata.create_all(engine)




# 类 -> 表


# ###### 增加 ######
# 单次插入
# obj1 = UserType(title='普通用户')
# session.add(obj1)


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



# ###### 查 ######
# print(session.query(UserType))
# 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)

# ###### 删除 ######
# 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")


# 知识点：语句的表达
# .subquery():作为子查询
# .all():全部拿出到内存
# as_scalar():处理笛卡尔积


# q1 = session.query(UserType).filter(UserType.id > 3)
# print(q1)
# q2 = session.query(UserType).filter(UserType.id > 3).subquery()
# print(q2)
# result = session.query(q2).all()
# print(result)


# q3 = session.query(Users).filter(Users.id == 1).subquery()
# print("q3:",q3)
# q4 = session.query(UserType,q3)
# print("q4:",q4)
# q5= session.query(UserType,session.query(Users).as_scalar())
# print("q5:",q5)


q6 = session.query(Users).filter(Users.user_type_id == UserType.id).as_scalar()
print("q6:",q6)

result = session.query(UserType.id,q6)
print("result:",result)

# 知识点：关系的的正向和反向映射
# 获取用户信息以及用户类型

# 以查询的对象作为row
# 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)

# 以查询的对象作为row
# user_list = session.query(Users.name,UserType.title).join(UserType,isouter=True).all()
# print(user_list)
# for row in user_list:
#     print(row[0],row[1])
#     print(row.name,row.title)

# 利用关系外键对象直接取对方信息
# user_list = session.query(Users)
# for row in user_list:
#     print(row.id,row.name,row.user_type_id,row.user_type.title)

# 获取用户类型
# 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.back_users[0].name,row.back_users[1].name)




session.commit()
session.close()

　　

简述ORM机制原理

问题：简述ORM原理
	
		class User:
			def __init__(self):
				self.id =..
				self.name =.
				self.email
			
			def order_by():
				sss
				
				
		obj = User()
		obj.__dict__ = {
			id:'',
			name:,
			email:
		}
		
		select id,name,email from user	order by ...