SQLAlchemy

连接池

from sqlalchemy import create_engine
engine = create_engine(
    'mysql+pymysql://root:000000@192.168.30.161:3306/sqlalchemy_test?charset=utf8mb4',
    max_overflow=0,         # 超过联机池大小外最多创建的连接，-1表示无限制
    pool_size=5,            # 连接池大小, 0表示无限制
    pool_timeout=30,        # 池中没有线程，最多等待的时间(秒）
    pool_recycle=-1,        # 多久之后对线程池中的线程进行一次连接回收(重置)
)

单表

表结构

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, create_engine
from sqlalchemy import Integer, String, Boolean, DateTime
import datetime


Base = declarative_base()


class User(Base):
    __tablename__ = 'users'  # 数据库表名

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), nullable=False, default='xxx')
    gender = Column(Boolean, nullable=False, default=True)
    date = Column(DateTime, onupdate=datetime.datetime.now, default=datetime.datetime.now, nullable=False)


if __name__ == '__main__':
    # 找到所有继承Base的类，在数据库中映射成表
    Base.metadata.create_all(engine)

    # 找到所有继承Base的类，在数据库中删除对应的表
    # Base.metadata.drop_all(engine)

增删改查

from models import User
from models import engine
from sqlalchemy.orm import sessionmaker
from datetime import timedelta
from datetime import datetime

SessionFactory = sessionmaker(bind=engine)

session = SessionFactory()


# 增
obj1 = User(name='xp')
obj2 = User(name='test')
obj3 = User(name='zhangshan')
session.add_all([obj1, obj2, obj3])           # session.add(对象) 增加一条数据
session.commit()


# 查
rest = session.query(User).all()
for row in rest:
    print(row.id, row.name, row.gender)

rest = session.query(User).filter(User.id >=1).first()
print(rest)

session.query(User).filter_by(name='xxx')   # filter(User.name=='xxx')


# 删
rest = session.query(User).filter(User.name=='def').delete()
session.commit()


# 改
session.query(User).filter(User.id == 3).update({User.name: 'aaa', User.gender: False})
session.commit()

session.query(User).filter(User.id == 3).update({'name': 'aaa', 'gender': False})
session.commit()

session.query(User).filter(User.id == 3).update({User.name: User.name+'xxx'}, synchronize_session=False)
session.commit()

ret = session.query(User).filter(User.id == 3).first()
ret.date += timedelta(days=3)
session.commit()


# 关闭连接
session.close()

其他常用操作

# 起别名
# ret = session.query(User.id, User.name.label('cname')).all()
# for item in ret:
#     print(item.cname)


# and
# session.query(User).filter(User.id > 1, User.name == 'xxx')


# between and
# session.query(User).filter(User.id.between(1, 3)).all()


# in, not in
# session.query(User).filter(User.id.in_([1, 2, 3]))
# session.query(User).filter(~User.id.in_([1, 2, 3]))  # not in


# 子查询
# session.query(User).filter(User.id.in_(session.query(User.id).filter(User.name == 'xxx')))


# from sqlalchemy import and_, or_, not_

# or
# session.query(User).filter(or_(User.id == 1, User.name == 'test')).all()

# or and
# session.query(User).filter(or_(User.id == 1, and_(User.id > 3, User.name == 'test')))

# not
# session.query(User).filter(not_(User.name=='abc'))


# 通配符
# session.query(User).filter(User.name.like('e%')).all()
# session.query(User).filter(~User.name.like('e%')).all()


# limit
# ret = session.query(User).filter(User.id>1).limit(2).all()


# 分组
# from sqlalchemy.sql import func
#
# ret = session.query(
#     func.max(User.id),
#     func.sum(User.id),
#     func.min(User.id),
#     func.group_concat(User.name)
# ).group_by(User.gender).all()
# print(ret)


# # having
# session.query(User).filter(User.id>1).having(User.gender==True)


# union union_all
# q1 = session.query(User)
# q2 = session.query(User)
# q1.union(q2).all()
# q1.union_all(q2)

一对多

表结构

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relation
class User(Base):
    __tablename__ = 'users'  # 数据库表名

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), nullable=False, default='xxx')
    depart_id = Column(Integer, ForeignKey('depart.id'))       # depart是表名，不是类名
	dp = relation('Depart', backref='user')					   # 建立关系

    
class Depart(Base):
    __tablename__ = 'depart'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(32), nullable=False)

查询数据

# 默认根据ForeignKey连接
result = session.query(User, Depart).join(Depart).all()
# result = session.query(User, Depart).join(Depart, User.depart_id==Depart,id()).all()
for row in result:
    print(row[0].name, row[1].title)

# 外连接
query = session.query(User.name, Depart.title).join(Depart, isouter=True)
print(query)


# 直接跨表，正向查询
result = session.query(User).all()
for row in result:
    print(row.name, row.dp.title)
# 反向查询
result = session.query(Depart).filter(Depart.title == '开发').first()
for row in result.user:
    print(row.name, result.title)

增加数据

# 方式1
d1 = Depart(title='测试')
u1 = User(name='alex')
d1.user.append(u1)
session.add(d1)
session.commit()


# 方式2
d1 = Depart(title='测试')
u1 = User(name='alex')
u1.dp = d1
session.add(u1)
session.commit()

修改数据

depart_obj = session.query(Depart).filter(Depart.title=='测试').first()
session.query(User).filter(User.dp==depart_obj).update({User.name: 'test'})
session.commit()


depart_obj1 = session.query(Depart).filter(Depart.title=='运维').first()
depart_obj2 = session.query(Depart).filter(Depart.title=='开发').first()
session.query(User).filter(User.dp==depart_obj1).update({User.depart_id:depart_obj2.id})
session.commit()

多对多

from sqlalchemy import UniqueConstraint

class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), nullable=True)
    # secondary 是第三张表名
    course = relation('Course', secondary='student2course', backref='student')


class Course(Base):
    __tablename__ = 'course'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(32), nullable=True)


class StudentToCourse(Base):
    __tablename__ = 'student2course'
    id = Column(Integer, primary_key=True, autoincrement=True)
    student_id = Column(Integer, ForeignKey('student.id'))
    course_id = Column(Integer, ForeignKey('course.id'))

    __table_args__ = (
        # 设置联合唯一
        UniqueConstraint('student_id', 'course_id', name='index_stu_cou'),
    )

s_obj = session.query(Student).filter(Student.name=='xxx').first()
c_obj_list = s_obj.course
for row in c_obj_list:
    print(row.title)

其他操作参考一对多

两种连接方式

方式一

from models import Student
from models import engine
from sqlalchemy.orm import sessionmaker
from threading import Thread

def task():
    Session = sessionmaker(bind=engine)
    session = Session()
    ret = session.query(Student).all()
    print(ret)
    session.close()

for i in range(20):
    Thread(target=task).start()

方式二(推荐)

from models import Student
from models import engine
from sqlalchemy.orm import sessionmaker, scoped_session
from threading import Thread

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

def task():
    ret = session.query(Student).all()
    print(ret)
    session.remove()

for i in range(20):
    Thread(target=task).start()

执行原生SQL

cursor = session.execute('select * from student')
ret = cursor.fetchall()
print(ret)
cursor.close()

cursor = session.execute("insert into student(name) value(:v1)",params={"v1":"test"})
session.commit()
cursor.close()
print(cursor.lastrowid)         # 打印最后一个自增id

session.close()

conn = engine.raw_connection()
cursor = conn.cursor()
ret = cursor.execute("select * from student")
print(ret)
cursor.close()
conn.close()