sqlalchemy 使用

sqlalchemy 使用

创建数据库以及更新数据库

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import 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))




class Phone(Base):
    __tablename__ = 'Phone'
    pid = Column(Integer, primary_key=True)
    name = Column(String(32))
    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)


def drop_db():
    engine = create_engine("mysql+pymysql://root:qq995405033@127.0.0.1:3306/web", max_overflow=5)

    Base.metadata.drop_all(engine)


if __name__ == '__main__':
    drop_db()
    init_db()## 缺点：会删除数据

数据库增删改查

"""
    数据库简单操作
"""
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

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

engine = create_engine("mysql+pymysql://root:qq995405033@127.0.0.1:3306/web", max_overflow=5)
Session = sessionmaker(bind=engine)
session = Session()

from BlueMap.pro_flask.models import Users,Phone


# 增
userone = Users(name="wanghong",extra='extra')
phoneone = Phone(name="wanghong",style='m87SS')
session.add(userone)
session.add(phoneone)

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

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

# 查
print(session.query(Users.name).all())
print(session.query(Users.name).filter_by(name="wanghong").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), Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()
from sqlalchemy import and_, or_
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'eric', Users.id > 3),
        Users.extra != ""
    )).all()


# 通配符
ret = session.query(Users).filter(Users.name.like('e%')).all()
ret = session.query(Users).filter(~Users.name.like('e%')).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 import 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 = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).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)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all()