直接从SessionFactory里获取,此时如果需要开启多个进程,那么创建连接池的代码一定要放在循环里面
不然的话每个进程都是用一个session了
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Student,Course,Student2Course
engine = create_engine(
"mysql+pymysql://root:123456@127.0.0.1:3306/s9day120?charset=utf8",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间,否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置)
)
SessionFactory = sessionmaker(bind=engine)
def task():
# 去连接池中获取一个连接
session = SessionFactory()
ret = session.query(Student).all()
# 将连接交还给连接池
session.close()
from threading import Thread
for i in range(20):
t = Thread(target=task)
t.start()
使用scoped_session创建session
这样创建的session在多个线程里还是不同的
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
from models import Student,Course,Student2Course
engine = create_engine(
"mysql+pymysql://root:123456@127.0.0.1:3306/s9day120?charset=utf8",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间,否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置)
)
SessionFactory = sessionmaker(bind=engine)
session = scoped_session(SessionFactory)
def task():
ret = session.query(Student).all()
# 将连接交还给连接池
session.remove()
from threading import Thread
for i in range(20):
t = Thread(target=task)
t.start()
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
from models import Student,Course,Student2Course
engine = create_engine(
"mysql+pymysql://root:123456@127.0.0.1:3306/s9day120?charset=utf8",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间,否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置)
)
SessionFactory = sessionmaker(bind=engine)
session = scoped_session(SessionFactory)
def task():
""""""
# 方式一:
# 查询
# cursor = session.execute('select * from users')
# result = cursor.fetchall()
# 添加
cursor = session.execute('INSERT INTO users(name) VALUES(:value)', params={"value": 'wupeiqi'})
session.commit()
print(cursor.lastrowid)
# 方式二:
conn = engine.raw_connection()
cursor = conn.cursor()
cursor.execute(
"select * from t1"
)
result = cursor.fetchall()
cursor.close()
conn.close()
# 将连接交还给连接池
session.remove()
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy import Integer,String,Text,Date,DateTime,ForeignKey,UniqueConstraint, Index
from sqlalchemy import create_engine
from sqlalchemy.orm import relationship
Base = declarative_base()
class Depart(Base):
__tablename__ = 'depart'
id = Column(Integer, primary_key=True)
title = Column(String(32), index=True, nullable=False)
class Users(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String(32), index=True, nullable=False)
depart_id = Column(Integer,ForeignKey("depart.id"))
dp = relationship("Depart", backref='pers') # 不在数据库中生成字段 只做查询使用,反向查询用user.pers
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Users,Depart
engine = create_engine(
"mysql+pymysql://root:123456@127.0.0.1:3306/s9day120?charset=utf8",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间,否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置)
)
SessionFactory = sessionmaker(bind=engine)
# 根据Users类对users表进行增删改查
session = SessionFactory()
# 1. 查询所有用户
ret = session.query(Users).all()
for row in ret:
print(row.id,row.name,row.depart_id)
# 2. 查询所有用户+所属部门名称
ret = session.query(Users.id,Users.name,Depart.title).join(Depart,Users.depart_id == Depart.id).all()
for row in ret:
print(row.id,row.name,row.title)
query = session.query(Users.id,Users.name,Depart.title).join(Depart,Users.depart_id == Depart.id,isouter=True)
print(query)
# 3. relation字段:查询所有用户+所属部门名称
ret = session.query(Users).all()
for row in ret:
print(row.id,row.name,row.depart_id,row.dp.title)
# 4. relation字段:查询销售部所有的人员
obj = session.query(Depart).filter(Depart.title == '销售').first()
for row in obj.pers:
print(row.id,row.name,obj.title)
# 5. 创建一个名称叫:IT部门,再在该部门中添加一个员工:田硕
# 方式一:
d1 = Depart(title='IT')
session.add(d1)
session.commit()
u1 = Users(name='付勇',depart_id=d1.id)
session.add(u1)
session.commit()
# 方式二:
u1 = Users(name='付勇',dp=Depart(title='IT'))
session.add(u1)
session.commit()
# 6. 创建一个名称叫:王者荣耀,再在该部门中添加一个员工:龚林峰/长好梦/王爷们
d1 = Depart(title='王者荣耀')
d1.pers = [Users(name='小A'),Users(name='小B'),Users(name='王爷们'),]
session.add(d1)
session.commit()
session.close()
class Student(Base):
__tablename__ = 'student'
id = Column(Integer, primary_key=True)
name = Column(String(32), index=True, nullable=False)
course_list = relationship('Course', secondary='student2course', backref='student_list')
class Course(Base):
__tablename__ = 'course'
id = Column(Integer, primary_key=True)
title = Column(String(32), index=True, nullable=False)
class Student2Course(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='uix_stu_cou'), # 联合唯一索引
# Index('ix_id_name', 'name', 'extra'), # 联合索引
)
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Student,Course,Student2Course
engine = create_engine(
"mysql+pymysql://root:123456@127.0.0.1:3306/s9day120?charset=utf8",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间,否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置)
)
SessionFactory = sessionmaker(bind=engine)
# 根据Users类对users表进行增删改查
session = SessionFactory()
# 1. 录入数据
session.add_all([
Student(name='先用'),
Student(name='佳俊'),
Course(title='生物'),
Course(title='体育'),
])
session.commit()
session.add_all([
Student2Course(student_id=2,course_id=1)
])
session.commit()
# 2. 三张表关联
ret = session.query(Student2Course.id,Student.name,Course.title).join(Student,Student2Course.student_id==Student.id,isouter=True).join(Course,Student2Course.course_id==Course.id,isouter=True).order_by(Student2Course.id.asc())
for row in ret:
print(row)
# 3. “先用”选的所有课
ret = session.query(Student2Course.id,Student.name,Course.title).join(Student,Student2Course.student_id==Student.id,isouter=True).join(Course,Student2Course.course_id==Course.id,isouter=True).filter(Student.name=='先用').order_by(Student2Course.id.asc()).all()
print(ret)
obj = session.query(Student).filter(Student.name=='先用').first()
for item in obj.course_list:
print(item.title)
# 4. 选了“生物”的所有人
obj = session.query(Course).filter(Course.title=='生物').first()
for item in obj.student_list:
print(item.name)
# 5. 创建一个课程,创建2学生,两个学生选新创建的课程。
obj = Course(title='英语')
obj.student_list = [Student(name='为名'),Student(name='广宗')]
session.add(obj)
session.commit()
session.close()