#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy  Column,Integer,String,ForeignKey,UniqueConstraint,Index
from sqlalchemy.orm  sessionmaker,relationship
 create_engine


Base = declarative_base()


 创建单表
class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32))
    extra = Column(String(16))




 Phone(Base):
    Phone
    pid = Column(Integer,primary_key=))
    person = relationship(Person',backref=person)




def init_db():
    engine = create_engine("MysqL+pyMysqL://root:qq995405033@127.0.0.1:3306/web",max_overflow=5)

    Base.Metadata.create_all(engine)


 drop_db():
    engine = create_engine()

    Base.Metadata.drop_all(engine)


if __name__ == __main__:
    drop_db()
    init_db()

## 缺点：会删除数据

"""
    数据库简单操作
"""
 create_engine

 如在 Phone_tb 中添加一条数据，先创建一个数据库模型类 Phone 的对象 phone_one， 然后使用 db.session 的 add() 方法将 phone_one 添加到数据库会话中， 最后执行 db.session.commit() 提交数据库会话。

engine = create_engine()
Session = sessionmaker(bind=engine)
session = Session()

from BlueMap.pro_flask.models  Users,Phone


 增
userone = Users(name=wanghongextra)
phoneone = Phone(name=m87SS)
session.add(userone)
session.add(phoneone)

 删除
session.query(Users).filter(Users.id ==3).delete()

 改
session.query(Users).filter(Users.name == ").update({':123"})

 查
print(session.query(Users.name).all())
print(session.query(Users.name).filter_by(name=).all())


session.commit()

　条件
ret = session.query(Users).filter_by(name=alex).all()
ret = session.query(Users).filter(Users.id > 1,Users.name == eric).all()
ret = session.query(Users).filter(Users.id.between(1,3),1)">).all()
ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(~Users.id.in_([1,1)">])).all()
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name=))).all()
 and_,or_
ret = session.query(Users).filter(and_(Users.id > 3,1)">)).all()
ret = session.query(Users).filter(or_(Users.id < 2,1)">)).all()
ret = session.query(Users).filter(
    or_(
        Users.id < 2,and_(Users.name == ),Users.extra != ""
    )).all()


 通配符
ret = session.query(Users).filter(Users.name.like(e%)).all()
ret = session.query(Users).filter(~Users.name.like()).all()

 限制
ret = session.query(Users)[1:2]

 排序
ret = session.query(Users).order_by(Users.name.desc()).all()
ret = session.query(Users).order_by(Users.name.desc(),Users.id.asc()).all()

 分组
from sqlalchemy.sql  func

ret = session.query(Users).group_by(Users.extra).all()
ret = session.query(
    func.max(Users.id),func.sum(Users.id),func.min(Users.id)).group_by(Users.name).all()

ret =).all()

 连表

ret = session.query(Users,Favor).filter(Users.id == Favor.nid).all()

ret = session.query(Person).join(Favor).all()

ret = session.query(Person).join(Favor,isouter=True).all()


 组合
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()

q1 = session.query(Users.name).filter(Users.id > 2)
ret = q1.union_all(q2).all()