ORM（关系对象映射）框架之面向对象

1. 提取共性
2. 分类
3. 模板“约束”
4. 当一类函数公用同样参数时候，可以转变成类进行 - 分类
3. 面向对象： 数据和逻辑（属性和行为）组合在一起
    函数编程：数据和逻辑分离

 

特殊方法：
        class Foo:
            def __init__(self,name):
                self.name = name
                
            
            def show(self):
                print(self.name)
                
            def __call__(self):
                pass
                
            def __getitem__(self,key):
                pass
                
            def __setitem__(self,key,value):
                pass
                
            def __delitem__(self,key):
                pass
                
        obj1 = Foo('eric')
        
        obj1()
        obj1['k']
        obj1['k'] = 123
        del obj[k]
        obj.__dict__

>>>>>>>>>>>>>>>>>>>对象后面直接加括号调用call方法 Python特有的

SQLAlchemy的增删改查操作：

import pymysql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint,Index
from sqlalchemy.orm import sessionmaker, relationships
from sqlalchemy import create_engine

Base = declarative_base()
#创建表
def create_db():
    engine = create_engine("mysql+pymysql://kevin:12121@127.0.0.1:3306/manage?charset=utf8", max_overflow=5)
    Base.metadata.create_all(engine)
#删除表
def drop_db():
    engine = create_engine("mysql+pymysql://kevin:12121@127.0.0.1:3306/manage?charset=utf8", max_overflow=5)
    Base.metadata.drop_all(engine)


class UserInfo(Base):
    __tablename__ = 'userinfo'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(36), nullable=True)
    age = Column(Integer)
    email = Column(String(20), index=True)
    utp_id = Column(Integer, ForeignKey('usertype.uid'))

    __table_args__ = (
        UniqueConstraint('id', 'email', name='ix_id_mail'),
        Index('mail_name','email','utp_id'),
    )

    def __repr__(self):
        return "%s-%s-%s" % (self.name,self.age,self.email)

class UserType(Base):
    __tablename__ = 'usertype'
    uid = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(30), nullable=True, index=True)

#插入数据

engine = create_engine("mysql+pymysql://kevin:12121@127.0.0.1:3306/manage?charset=utf8", max_overflow=5)
Session=sessionmaker(bind=engine)
session=Session()
# obj1=UserInfo(name='kevin',age=18,email='asax@sina.com')
# ses.add(obj1)
#类--->表
#对象---->行

# objs=[
#     UserType(title='普通会员'),
#     UserType(title='银牌会员'),
#     UserType(title='黄金会员'),
#     UserType(title='钻石会员'),
# ]
# session.add_all(objs)  #记录写入表中

#查询
# type_list=session.query(UserType).all()
# print(session.query(UserType))     #查询select SQL语句
#SELECT usertype.uid AS usertype_uid, usertype.title AS usertype_title FROM usertype
# for row in  type_list:
#     print(row.uid,row.title)

#删除
# info_list=session.query(UserInfo).filter(UserInfo.id>2).delete()
# for row in info_list:
#     print(row.name,row.email)

#修改 字符串的修改
# info_list=session.query(UserInfo).filter(UserInfo.id>0).update({UserInfo.name:UserInfo.name+'_cn'},synchronize_session=False)

#进行数字的计算
info_list=session.query(UserInfo).filter(UserInfo.id>0).update({UserInfo.age:UserInfo.age+5},synchronize_session='evaluate')

session.commit()   #提交
session.close()

create_db()

 子查询操作：

# s1=session.query(UserInfo).filter(UserInfo.id>2)
# s2=session.query(UserType).filter(UserType.uid<2)
# ret1=s1.union(s2).all()    #两次结果连接自动去重
# ret2=s1.union_all(s2).all()  #两次结果连接全部值 不去重
ret=session.query(UserType,session.query(UserInfo).filter(UserInfo.age>20).subquery())
# ret=session.query(UserType.uid,session.query(UserInfo).filter(UserInfo.id==UserType.uid).as_scalar())
print(ret)