SQLAlchemy

MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
 
pymysql
    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

MySQL-Connector
    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
 
cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
 
更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html

 

单表操作

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, Date, DateTime, MetaData, ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import and_, or_
from sqlalchemy.sql import func

Base = declarative_base() # 数据表的结构 用ORM 语言描述出来
engine = create_engine(
    "mysql+mysqlconnector://root:1234@127.0.0.1:3306/s927?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)


class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=False)


# ---------创建删除表------------
# Base.metadata.create_all(engine)
# Base.metadata.drop_all(engine)

# -------基本增删改查------------
SessionFactory = sessionmaker(autocommit=False,autflash=False,bind=engine)
# 通过sessionmaker得到一个类，一个能产生session的工厂
session = SessionFactory()  # 每次获取连接多需要实例化一次

基于Threading.local实现
from sqlalchemy.orm import scoped_session
SessionFactory = sessionmaker(bind=engine)
session = scoped_session(SessionFactory,可选的标识线程id)
result = session.query(User).all()
# 将连接交还给连接池
session.remove()

# 增
# obj = Users(name="alex")
# session.add(obj)
# session.add_all([obj,])
# 删
# session.query(Users).filter(Users.id > 1).delete()
# 改
# session.query(Users).filter(Users.id == 1).update(
#     {"name": Users.name + "as"},
#     synchronize_session="evaluate")  # 默认是数值的相加，字符串拼接改为false
# 查
# result = session.query(Users.id, Users.name.label('cname')).all()
# for item in result:
#     print(item[0], item.id, item.cname)
# result = session.query(Users).filter(Users.id > 1,Users.name == 'eric').first() # 对象
# result = session.query(Users).filter(~Users.id.in_([1, 2, 3])).first()  # 对象
# result = session.query(Users).filter(~Users.id.between(1, 2, 3)).first()  # 对象
# result = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter(Users.name == 'eric'))).all()

# result = session.query(Users).filter(or_(
#     Users.id < 2,
#     and_(Users.name == "eric", Users.id > 3),
#     Users.name != "eric"
# )).first()  # 对象
#
# session.query(Users).filter(Users.name.like("e%")).all()  # 通配符
# result = session.query(Users).all()[:2]  # [obj,]    切片
# session.query(Users).order_by(Users.name.desc(), Users.name.asc()).all()  # 排序
# result = session.query(Users.name, func.max(Users.id)).group_by(Users.name).all()  # 使用聚合函数sum,min,max
# result = session.query(Users.name, func.max(Users.id)).group_by(Users.name).having(func.max(Users.id > 2)).all()
#
# # UNION 相同字段的表上下拼接(去重),UNION_ALL(不去重)
# q1 = session.query(Users.name).filter(Users.id > 2)
# q2 = session.query(Users.name).filter(Users.id < 2)
# ret = q1.union(q2).all()
# ret = q1.union_all(q2).all()

session.commit()  # 增删改查都需要
session.close()  # 将连接交还给连接池

执行原生sql

# 方式一：
# 查询
# 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()

 

外键

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
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()
engine = create_engine(
    "mysql+mysqlconnector://root:1234@127.0.0.1:3306/s9day120?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
SessionFactory = sessionmaker(autocommit=Flase,autoflash=False,bind=engine)
# 生产session的工厂　　会话生成器  自动提交　　自动刷新
Base  = declarative_base()　　　　数据表的结构用ORM 的语言描述出来

# 根据Users类对users表进行增删改查
session = SessionFactory()
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='users',cascade="save-update,merge,deletes")  # 不生成字段

'''cascade 字段
在对Depart表进行操作时，希望对User表进行操作
save-update,merge 默认
save-update,merge,delete  
all
all,delete-option
'''
# 1. 查询所有用户
# ret = session.query(Users).all()
# for row in ret:
#     print(row.id,row.name,row.depart_id)
# 2. 查询所有用户+所属部门名称
# inner join
# 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)
# LEFT OUTER JOIN，没有右连接
# query = session.query(Users.id,Users.name,Depart.title).join(Depart,Users.depart_id == Depart.id,isouter=True)


# 3. relation字段:查询所有用户+所属部门名称
# ret = session.query(Users)
# 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.users:
#     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='龚林峰'),Users(name='长好梦'),Users(name='王爷们'),]
# session.add(d1)
session.commit()
session.close()

 

M2M

from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column
from sqlalchemy import Integer, String, ForeignKey, UniqueConstraint
from sqlalchemy import create_engine
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine(
    "mysql+mysqlconnector://root:1234@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()
Base = declarative_base() 

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'),                          # 联合索引
    )


# 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. 选了“生物”的所有人
# obj = session.query(Course).filter(Course.title=='生物').first()
# for item in obj.student_list:
#     print(item.name)

# 3. 创建一个课程，创建2学生，两个学生选新创建的课程。
# obj = Course(title='英语')
# obj.student_list = [Student(name='为名'),Student(name='广宗')]
# obj = Course(title='英语', student_list=[Student(name='为名'), Student(name='广宗')])
# session.add(obj)
# session.commit()


session.close()