Python--数据库sqlalchemy

1.什么是sqlalchemy？

sqlalchemy是Python ORM的开源框架，使用它可以快速方便的构建数据库模型

2.如何使用sqlalchemy？

1.安装

pip3 install sqlalchemy

2.导包

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import NullPool

3.连接

engine = create_engine('mysql+pymysql://root:1234@127.0.0.1:3306/test?charset=utf8',
                       max_overflow=0,  # 超过连接池大小外最多创建的连接，为0表示超过5个连接后，其他连接请求会阻塞 （默认为10）
                       encoding='utf-8',  # 编码格式
                       echo=True,  # 是否开启sql执行语句的日志输出
                       pool_timeout=30,  # 连接线程池中，没有连接时最多等待的时间，不设置无连接时直接报错 （默认为30）
                       pool_recycle=-1,  # 多久之后对线程池中的线程进行一次连接的回收（重置） （默认为-1）,其实session并不会被close
                       poolclass=NullPool  # 无限制连接数
                       )

4.创建 session

#声名Base
Base = declarative_base()

# 创建数据表
Base.metadata.create_all(engine)

#创建session
db_session = sessionmaker(bind=engine)

5.创建表

# 创建类，继承基类，用基本类型描述数据库结构
class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, )
    name = Column(String(64))
    psw = Column(String(64))
    psw_remark = Column(String(128))
    __table_args__ = {
        "mysql_charset": "utf8"
    }

    def obj_2_json(self):
        return {
            'id': self.id,
            'name': self.name,
            'psw': self.psw,
            'psw_remark': self.psw_remark
        }

    def __repr__(self):
        return 'id:%d,name=%s,psw=%s,psw_remark=%s' % (self.id if self.id else 0, self.name, self.psw, self.psw_remark)

6.操作表

# 保存记录
def save(user):
    is_success = True
    session = db_session()
    try:
        session.add(user)
        session.commit()
        print(user)
    except BaseException:
        is_success = False
        print('error=', traceback.format_exc())
    session.close()
    return is_success

# 根据ID删除
def delete(id):
    is_success = True
    session = db_session()
    try:
        print(user)
        session.query(User).filter(User.id == id).delete()
        session.commit()
    except BaseException:
        is_success = False
        print('error=', traceback.format_exc())
    session.close()
    return is_success

# 修改
def update(user):
    is_success = True
    session = db_session()
    try:
        print(user)
        session.query(User).filter(User.id == user.id).update({User.name: user.name})
        session.commit()
    except BaseException:
        is_success = False
        print('error=', traceback.format_exc())
    session.close()
    return is_success

# 查询单条
def queryOne(name):
    session = db_session()
    user = session.query(User).filter(User.name == name).limit(1).one()
    session.close()
    return user

# 条件查询
def query():
    session = db_session()
    list = session.query(User).filter(User.name == '唐人').all()
    session.close()
    return list

#查询所有
def queryAll():
    session = db_session()
    list = session.query(User).all()
    session.close()
    return list

7.条件查询总结

表名：User

1.条件查询
session.query(User).filter(User.name=='张三'){
                                               .all()   查询所有
                                               .one()   查询单个（如果存在多个会异常）
                                               .first() 查询符合条件的第一个
                                               .limit(1).one() limit限制查询，limit(1).one()升级第一个
                                               .count()  查询符合条件的总个数
                                               }

2.主键查询
session.query(User).get(0)  查询主键ID=0

3.offset(n) 限制前面n个，显示后面n+1个
#查询出第三个后面的所有
session.query(User).offset(3).all()

4.slice()切片
#slice(1,3) 与python的slice一致，从0开始 左闭右开，显示1,2两个元素
session.query(User).slice(1,,3).all()

5.order_by() 默认升序
session.query(User).order_by(User.id).all()

6.desc() 降序
session.query(User).order_by(desc(User.id)).all()

7.like 模糊匹配，与sql一样
session.query(User).filter(User.neme.like('%吴')).add()

8.notlike 与7相反


form operator import *

9.in_() 包含
#查询是否包含唐人、TangRen这个用户的信息
session.query(User).filter(User.name.in_(['唐人','TangRen'])).all()


10.notin_() 不包含

11.is_  两种表达方式 None
#查询所有手机号为null的信息
session.query(User).filter(User.phone==None).all()
session.query(User).filter(User.phone.is_(None)).all()

12. isnot()

13. or_ 条件或者关系
#查询name==TangRen或者唐人的用户信息
session.query(User).filter(or_(User.name=='唐人',User.name=='TangRen'))

聚合函数

1.count group_by
#查询所有的密码并且计算其相同的个数
from sqlalchemy import func
ssession.query(db_user.psw,func.count(db_user.psw)).group_by(db_user.psw).all()

2.having
having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。
而having子句在聚合后对组记录进行筛选。真实表中没有此数据，这些数据是通过一些函数生存。
即先成组在筛选

#查询所有的密码并且计算其相同的个数,having条件相同密码总数大于1的数据
ssession.query(db_user.psw,func.count(db_user.psw)).group_by(db_user.psw).having(func.count(db_user.psw)>1).all()

3.sum
#计算所有id的总和
ssession.query(func.sum(db_user.id)).all()

4.max
#最大的ID
 ssession.query(func.max(db_user.id)).all()

5.min
#最小的id
 ssession.query(func.min(db_user.id)).all()

6.lable 别名
lable别名不能用在having中

7.extract 提取时间元素
from sqlalchemy import extract