SQLAlchemy

SQLAlchemy介绍

SQLAIchemy是一个基于python的ORM框架,帮助我们使用类和对象快速的实现数据库的操作.

数据库:

　　-原生

　　　　-MySQLdb:py2

　　　　-pymysql : py2/py3

　　-ORM框架

　　　　-SQLAIchemy

安装:

pip install sqlalchemy

SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

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

ORM表创建

创建数据库表

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import datetime
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index

Base = declarative_base()
class Users(Base):
    #表名
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=False)
    # email = Column(String(32), unique=True)
    # ctime = Column(DateTime, default=datetime.datetime.now)
    # extra = Column(Text, nullable=True)

    __table_args__ = (
        # UniqueConstraint('id', 'name', name='uix_id_name'),   #联合唯一索引
        # Index('ix_id_name', 'name', 'email'),　　　　　　　　　　#联合索引
    )

def init_db():
    """
    根据类创建数据库表
    :return: 
    """
    engine = create_engine(
        "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )

    Base.metadata.create_all(engine)
def drop_db():
    """
    根据类删除数据库表
    :return: 
    """
    engine = create_engine(
        "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )

    Base.metadata.drop_all(engine)
if __name__ == '__main__':
    drop_db()
    init_db()

操作数据库表

基本操作

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Users
  
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
  
# 每次执行数据库操作时，都需要创建一个session
session = Session()
  
# ############# 执行ORM操作 #############
obj1 = Users(name="alex1")
session.add(obj1)
  
# 提交事务
session.commit()
# 关闭session
session.close()

单表的增删改查

• 注意每次操作都需要将session close()掉
• 增加,修改,删除都需要commit,不然不会对数据库造成影响
• filter(过滤条件,接近原生sql)
• filter_by(字段名=...)接近Django的orm
• 需要查看sql语句可以不加后面的.all()

单表增加

import time
import threading

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
from sqlalchemy.sql import text

from db import Users, Hosts

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()

##########添加##############
obj1 = Users(name="wupeiqi")
#单条添加
session.add(obj1)

#多条添加
session.add_all([
    Users(name="wupeiqi"),
    Users(name="alex"),
    Hosts(name="c1.com"),
])
session.commit()
session.close()

单表删除

session.query(Users).filter(Users.id > 2).delete()
session.commit()

单表修改

#update后面可以是字典套字段名
session.query(Users).filter(Users.id > 0).update({"name" : "099"})

#可以是字典套句子的值 字符串拼接需要加synchronize_session=False
session.query(Users).filter(Users.id > 0).update({Users.name: Users.name + "099"}, synchronize_session=False)

session.query(Users).filter(Users.id > 0).update({"age": Users.age + 1}, synchronize_session="evaluate")
session.commit()

单表查询

# 查询Users表所
r1 = session.query(Users).all()

# 只查某几个字段
r2 = session.query(Users.name.label('xx'), Users.age).all()

#Users.name双等于'alex'
r3 = session.query(Users).filter(Users.name == "alex").all()

#filter_by把字段名字='' 跟Django比较相似
#filter接近原生
r4 = session.query(Users).filter_by(name='alex').all()

r5 = session.query(Users).filter_by(name='alex').first()

r6 = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all()

r7 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()
session.close()

条件查询

ret = session.query(Users).filter_by(name='alex').all()

#默认为and
ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()

#与原生between一样 在1和3之间..
ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()

#在什么什么里面   in_**
ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
# 不在里面
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
#子查询,且两次fliter
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()


from sqlalchemy import and_, or_
#and_(里面是and关系)
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
# or_(里面是or关系)
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()

# or_包含and_
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()
# 按名字反向排序,如果名字一样再按id正向排序
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()

组合

行的拼接 前提:列要一致

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

外键一对多操作

创建表结构

import datetime
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
from sqlalchemy.orm import relationship

Base = declarative_base()

class Depart(Base):
    ''' 部门'''
    __tablename__ = 'depart'
    id = Column(Integer, primary_key=True)
    title = Column(String(50))


class Person(Base):
    ''' 人'''
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    depart_id = Column(Integer, ForeignKey("depart.id"))

    # 与生成表结构无关，仅用于查询方便
    # Depart是类名,not表名
    depart = relationship("Depart", backref='pers')  #正反向字段


def init_db():
    """
    根据类创建数据库表
    :return: 
    """
    engine = create_engine(
        "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )

    Base.metadata.create_all(engine)


def drop_db():
    """
    根据类删除数据库表
    :return: 
    """
    engine = create_engine(
        "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )

    Base.metadata.drop_all(engine)


if __name__ == '__main__':
    drop_db()
    init_db()

添加

import time
import threading

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
from sqlalchemy.sql import text
from sqlalchemy.engine.result import ResultProxy
from db import Users, Hosts,Depart, Person

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()

# 不使用外键,手动添加操作 不推荐使用
session.add_all([
    Depart(caption='IT部'),
    Depart(caption='销售部'),
    Person(name='张三', hobby_id=3),
    Person(name='李四', hobby_id=4),
])

#使用外键,
#添加部门是保安部,并且添加张九,关联保安部
person = Person(name='张九', depart=Depart(title='保安部'))
session.add(person)

#使用外键
#添加部门是运维部, 添加多个人关联运维部门
hb = Depart(title='运维部')
hb.pers = [Person(name='文飞'), Person(name='博雅')]
session.add(hb)
session.commit()

外键正反向查询

# 使用relationship正向查询
# 查询某个人对应的部门 先查询某个人,然后通过relationship进行关联
person = session.query(Person).filter(Person.name=='张三').first()
print(person.name)
print(person.depart.title)


# 使用relationship反向查询
# 查询某个部门有哪些人, 先查询某个部门,再通过relationship反向关联
dp = session.query(Depart).filter(Depart.title='IT部').first()
print(dp.title)
for ret in dp.pers:
    print(ret)

session.close()

多对多字段操作

创建多对多表

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import datetime
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
from sqlalchemy.orm import relationship

Base = declarative_base()
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='std_un_cou'),   #联合唯一索引
        # Index('ix_id_name', 'name', 'email'),　#联合索引
    )


class Student(Base):
    #学生表
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)

    # 与生成表结构无关，仅用于查询方便
    cour = relationship('Course', secondary='student2course', backref='stud')


class Course(Base):
    #课程表
    __tablename__ = 'course'

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(64), unique=True, nullable=False)
    
def init_db():
    """
    根据类创建数据库表
    :return: 
    """
    engine = create_engine(
        "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )

    Base.metadata.create_all(engine)


def drop_db():
    """
    根据类删除数据库表
    :return: 
    """
    engine = create_engine(
        "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )

    Base.metadata.drop_all(engine)


if __name__ == '__main__':
    drop_db()
    init_db()

多对多之添加

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import time
import threading

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
from sqlalchemy.sql import text
from sqlalchemy.engine.result import ResultProxy
from db import Student, Course, Student2Course

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()
# 添加
#不使用外键,需要添加三张表
session.add_all([
    Student(name='alex'),
    Student(name='chen'),
    Course(title='英语'),
    Course(title='数学'),
])
session.commit()
s2c = Student2Course(student_id=1, course_id=1)
session.add(s2g)
session.commit()

#使用外键
#添加语文课程,并且张三李四都悬了语文课程.. 数据库自动创建第三表关系
cr = Course(title='语文')
cr.stud = [Student(name='张三'),Student(hostname='李四')]
session.add(cr)
session.commit()


#使用外键
#添加 学生为玄武,并且创建化学和生物两门课程与玄武创建了对应关系
sd = Student(name='玄武')
sd.cour = [Course(title='化学'),Course(title='生物')]
session.add(sd)
session.commit()

多对多之正反查询

# 使用relationship正向查询
student = session.query(Student).first()
print(student.name)
print(student.cour)

# 使用relationship反向查询
course= session.query(Course).first()
print(course.title)
print(course.stud)

session.close()

两种连接数据库的方式

方式一:

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

方式二：（推荐，基于Threading.local实现)

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

执行原生sql

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 threading import Thread

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