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