SQLAlchemy

       一 . 介绍       

 　　SQLAlchemy 是一个基于 python 实现的 ORM 框架.该框架建立在 DB API之上,使用关系对象映射进行数据库操作,简而言之就是: 将类和对象转换成SQL,然后使用数据API进行SQL并获取执行结果.

 pip install sqlalchemy -i http://pypi.douban.com/simple  --trusted-host pypi.douban.com 

# 下载SQLAlchemy 模块

 

 

组成部分：

• Engine，框架的引擎
• Connection Pooling ，数据库连接池
• Dialect，选择连接数据库的DB API种类
• Schema/Types，架构和类型
• SQL Exprression Language，SQL表达式语言

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

 

 

       二 . 使用       

    1 . 创建数据表   

 　　注意 : 要先创建对应的数据库,我的数据库叫 s1

 # ORM中的数据表是什么呢?
 # Object Relation Mapping
 # Object - Table 通过 Object 去操纵数据表
 # 从而引出了我们的第一步创建数据表 - 创建Object
 # 1. 创建Object
 # class User(object):
 #     pass



#  通过SQLAlchemy创建数据表
#  1.导入SQLAlchemy

from sqlalchemy.ext.declarative import declarative_base

#  2.创建ORM模型基类,要被继承
Base = declarative_base()

#  3.导入ORM对应数据库类型的字段,比如:列,整型,字符串
from sqlalchemy import Column, Integer, String


#  4.创建ORM对象
# 当前的这个Object继承了Base也就是代表了Object继承了ORM的模型
class User(Base):   # 相当于 Django Models中的 Model
       # 创建表名,__双下划线
    __tablename__ = "user"
    #  设置为主键,并且自增(默认)
    # id = Column(数据类型,索引,主键,外键,等等)
    id = Column(Integer, primary_key=True, autoincrement=True)
    # 设置索引
    name = Column(String(32), index=True)


#  5.创建数据库连接
from sqlalchemy import create_engine

# 数据库创建连接完成
# 连接时 用mysql 和 python 的pymsql
enginne = create_engine("mysql+pymysql://root:@127.0.0.1:3306/s1?charset=utf8")

#  6.去数据库中创建于User所对应的数据表
#  去User数据库中创建所有基层Base类的 ORM对象
# Base 自动检索所有继承Base的ORM 对象 并且创建所有的数据表
Base.metadata.create_all(enginne)

 

      2 . 增删改查操作    

　　注意 : 在增删改查的操作中,除了查询不用 commit之外,增/删/改  都需要

#执行会话的操作
db_session.commit()
#关闭会话
db_session.close()

 2.1 增加数据

# 创建会话 -- 打开数据库连接
from create_table import enginne
from sqlalchemy.orm import sessionmaker
# 创建会话窗口
# 创建 sessionmaker 会话对象,将数据库引擎 engine 交给 sessionmaker
Session = sessionmaker(enginne)
# 打开会话窗口
db_session = Session()

#  1.增加数据   == insert
#  原生mysql语句 , insert into user(name) values("yirenye")
from create_table import User
user_obj = User(name="异人夜")
# 通过de_session已经打开的会话窗口,提交数据
db_session.add(user_obj)  # 相当于 insert into
# 执行会话窗口中的所有操作
db_session.commit()
# 关闭窗口
db_session.close()

# 2.增加批量数据 -- 多条
from create_table import User
db_session.add_all([
    User(name="小慯"),
    User(name="Tom"),
    User(name="Ealse"),
])
db_session.commit()
db_session.close()

# 扩展
from create_table import User
User1 = User(name="666")
User2 = User(name="888")
User3 = User(name="999")

db_session.add(User1)
db_session.add(User2)
db_session.add(User3)

db_session.commit()
db_session.close()

 

2.2 查询书籍

# 会话窗口
from sqlalchemy.orm import sessionmaker
from create_table import enginne

Session = sessionmaker(enginne)
db_session = Session()

from create_table import User

# 1.简单查询
#  原生sql语句  select * from table
# 查询全部
user_list = db_session.query(User).all()
for row in user_list:
    print(row.id, row.name)
"""
5 666
6 888
7 999
4 Ealse
3 Tom
2 小慯
1 异人夜
"""
# 查询一条
user = db_session.query(User).first()
print(user.id, user.name)
# 5 666

# 2.带条件查询
user_list = db_session.query(User).filter(User.id == 4).all()
print(user_list[0].id, user_list[0].name)
# 4 Ealse

user = db_session.query(User).filter_by(id=3).first()
print(user.id, user.name)
# 3 Tom

user_list = db_session.query(User).filter(User.id >= 4).all()
for row in user_list:
    print(row.id, row.name)
"""
4 Ealse
5 666
6 888
7 999
"""

# 扩展,查看sql语句
sql = db_session.query(User).filter(User.id >= 5)
print(sql)
"""
SELECT user.id AS user_id, user.name AS user_name 
FROM user 
WHERE user.id >= %(id_1)s
"""
# 关闭会话
db_session.close()

2.3 修改数据 

# 更新修改数据
from sqlalchemy.orm import sessionmaker
from create_table import enginne, User

Session = sessionmaker(enginne)
db_session = Session()

# 修改数据 -- 先查询数据在进行修改
#  原生sql语句的修改(将id=1的数据的 name 修改成666) : update "user" set "name" = "666" where id = 1
res = db_session.query(User).filter(User.name=="Ealse").update({"name":"杰斯"})
print(res)
# 1  -- 符合条件的只有一条
db_session.commit()
db_session.close()

res = db_session.query(User).filter(User.id >= 5).update({"name":666666})
print(res)
# 3  -- 符合条件的有3条
db_session.commit()
db_session.close()

 

2.4 删除数据 

# 删除数据
# 导入 ORM 创建会话
from sqlalchemy.orm import sessionmaker
from create_table import enginne, User

Session = sessionmaker(enginne)
db_session = Session()

# 删除也是先查询--在删除
# sql原生语句删除 :  delete from user where id=5
#  1.删除一个
res = db_session.query(User).filter(User.id == 5).delete()
print(res)
# 1  -- 符合条件的只有一条
db_session.commit()
db_session.close()

# 2.删除多条数据
res = db_session.query(User).filter(User.name == "666666").delete()
print(res)
# 2  -- 符合条件的有2条
db_session.commit()
db_session.close()

2.5 . 高级版查询操作 

 

# 高级版查询操作,厉害了哦
# 老规矩
from create_table import User, enginne
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(enginne)
db_session = Session()

# 1.查询表操作
#  and  or 的用法,在SQLAlchemy 中,要用SQLAlchemy 的and 和 or
# from sqlalchemy.sql import and_, or_

ret = db_session.query(User).filter(and_(User.id == 4,User.name == "Tom")).all()
print(ret)
#  [] ,因为id=4的name!=Tom,所有会查询不到

ret1 = db_session.query(User).filter(or_(User.id == 4,User.name == "Tom")).all()
print(ret1)
# [<create_table.User object at 0x000002451F90FE80>, <create_table.User object at 0x000002451F938048>] 查询出来2条记录

#  2.查询所有
ret2 = db_session.query(User).all()

#  3.查询数据,指定查询数据列,加入别名
ret3 = db_session.query(User.id.label("uid"),User.name.label("username")).first()
print(ret3.uid, ret3.username)
# 3 Tom , 在查询first的时候,会找在数据库中的第一个,可能不是id=1的

#  4.表达式筛选条件
ret4 = db_session.query(User).filter(User.name == "Tom").all()
print(ret4)
# [<create_table.User object at 0x00000278BBBBFF98>]

#  5.元素sql筛选条件
ret5 = db_session.query(User).filter_by(name="Tom").all()
print(ret5)
# [<create_table.User object at 0x000001EDB192F470>]

ret52 = db_session.query(User).filter_by(name="Tom").first()
print(ret52)
# <create_table.User object at 0x000001EDB192F470>

#  6.字符串匹配方式筛选条件,并进行 order_by 进行排序
from sqlalchemy.sql import text

ret6 = db_session.query(User).filter(text("id<:value")).params(value=3).order_by(User.id).all()
print(ret6)
# [<create_table.User object at 0x0000018C4BA2FF98>, <create_table.User object at 0x0000018C4BA2F828>]

#  7.原生SQL查询
ret7 = db_session.query(User).from_statement(text("SELECT * FROM User where name=:name")).params(name='DragonFire').all()

#  8.筛选查询列
# query的时候我们不在使用User ORM对象,而是使用User.name来对内容进行选取
user_list8 = db_session.query(User.name).all()
print(user_list8)
# [('Tom',), ('小慯',), ('异人夜',), ('杰斯',)]
for row in user_list8:
    print(row.name)
"""
Tom
小慯
异人夜
杰斯
"""

#  9.别名映射  name as nick
user_list9 = db_session.query(User.name.label("nick")).all()
print(user_list9)
for row in user_list9:
    print(row.nick)
# 这里要写别名了

#  10.排序 :
# 正序,默认
user_list10 = db_session.query(User).order_by(User.id.asc()).all()
# 反序
user_list11 = db_session.query(User).order_by(User.id.desc()).all()
for row in user_list11:
    print(row.name,row.id)

#  11.
ret12 = db_session.query(User).filter(User.id.between(1,3)).all()
print(ret12)
# between 大于等于1 小于等于3的
# [<create_table.User object at 0x000002687461F080>, <create_table.User object at 0x000002687461F0B8>, <create_table.User object at 0x000002687461FE80>]
ret13 = db_session.query(User).filter(User.id.in_([1,4])).all() # in_([1,3,4])
# 只查询id等于1,3,4的
ret14 = db_session.query(User).filter(~User.id.in_([1,2,3])).all() # ~xxxx.in_([1,3,4])
# 查询不等于1,3,4的

#   12.and_ , or_ 的其他用法
# 查询条件中符合其中一条就可以
from sqlalchemy.sql import and_, or_
ret15 = db_session.query(User).filter(
    or_(
        User.id < 2,
        and_(User.name == "Tom",User.id > 2),
        User.name != ""
    )
)
print(ret15)
"""
SELECT user.id AS user_id, user.name AS user_name 
FROM user 
WHERE user.id < %(id_1)s OR user.name = %(name_1)s AND user.id > %(id_2)s OR user.name != %(name_2)s
"""

#   13.其他查询条件
"""
ret = session.query(User).filter_by(name='DragonFire').all()
ret = session.query(User).filter(User.id > 1, User.name == 'DragonFire').all()
ret = session.query(User).filter(User.id.between(1, 3), User.name == 'DragonFire').all() # between 大于1小于3的
ret = session.query(User).filter(User.id.in_([1,3,4])).all() # in_([1,3,4]) 只查询id等于1,3,4的
ret = session.query(User).filter(~User.id.in_([1,3,4])).all() # ~xxxx.in_([1,3,4]) 查询不等于1,3,4的
ret = session.query(User).filter(User.id.in_(session.query(User.id).filter_by(name='DragonFire'))).all() 子查询
from sqlalchemy import and_, or_
ret = session.query(User).filter(and_(User.id > 3, User.name == 'DragonFire')).all()
ret = session.query(User).filter(or_(User.id < 2, User.name == 'DragonFire')).all()
ret = session.query(User).filter(
    or_(
        User.id < 2,
        and_(User.name == 'eric', User.id > 3),
        User.extra != ""
    )).all()
# select * from User where id<2 or (name="eric" and id>3) or extra != "" 

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

# 限制
ret = db_session.query(User)[1:2]

# 排序
ret = db_session.query(User).order_by(User.name.desc()).all()
ret = db_session.query(User).order_by(User.name.desc(), User.id.asc()).all()

# 分组
from sqlalchemy.sql import func

ret = db_session.query(User).group_by(User.extra).all()
ret = db_session.query(
    func.max(User.id),
    func.sum(User.id),
    func.min(User.id)).group_by(User.name).all()

ret = db_session.query(
    func.max(User.id),
    func.sum(User.id),
    func.min(User.id)).group_by(User.name).having(func.min(User.id) >2).all()
"""

2.6 . 高级版修改操作 

 　　未修改的数据 :                                                           

# 高级版更新操作
from create_table import User, enginne
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(enginne)
db_session = Session()

#  1.直接修改
ret = db_session.query(User).filter(User.id > 0).update({"name":"异人夜真帅"})

#  2.在原有值基础上添加 - 1+
#  synchronize_session 是否提交完之后再跟新,有可能会前面有提交的东西会发生覆盖现象
ret2 = db_session.query(User).filter(User.id > 0).update({User.name:User.name + "天才"},synchronize_session=False)

#  3.在原有值基础上添加 - 2
db_session.query(User).filter(User.id > 0).update({"age": User.age + 1}, synchronize_session="evaluate")
db_session.commit()

   

 

      3 . 一对多操作 : ForeignKey   

3.1 创建数据表及关系 relationship : 

# 一对多建表操作
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship


class Student(Base):
    __tablename__ = "student"
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    school_id = Column(Integer,ForeignKey("school.id"))
    # 正向跨表查询用stu2sch, 反向跨表查询用sch2stu
    stu2sch = relationship("School",backref="sch2stu")


class School(Base):
    __tablename__ = "school"
    id = Column(Integer, primary_key=True)
    name = Column(String(32))


from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/s1?charset=utf8")

Base.metadata.create_all(engine)

3.2 基于 relationship 增加数据 :  

# 添加数据
from sqlalchemy.orm import sessionmaker
from create_table_ForeignKey import engine,Student,School

Session = sessionmaker(engine)
db_session = Session()

#  1.添加数据 --笨
# 添加学校
sch_obj = School(name="清华大学")
db_session.add(sch_obj)
db_session.commit()
# 添加学生
sch = db_session.query(School).filter(School.name == "清华大学").first()
stu_obj = Student(name="异人夜",school_id=sch.id)
db_session.add(stu_obj)
db_session.commit()
db_session.close()


#  2.添加数据 -- 正向  relationship 版,学生->学校
stu_obj = Student(name="chuan",stu2sch=School(name="香港大学"))
db_session.add(stu_obj)
db_session.commit()
db_session.close()

# 3.添加数据 - 反向 relationship 版,学校->学生
sch_obj = School(name="厦门大学")
sch_obj.sch2stu = [Student(name="雯雯"),Student(name="lantian")]
db_session.add(sch_obj)
db_session.commit()
db_session.close()

3.3 基于 relationship  查询数据 :  

# 添加数据
from sqlalchemy.orm import sessionmaker
from create_table_ForeignKey import engine,Student,School

Session = sessionmaker(engine)
db_session = Session()

# 1.查询数据 - relationship 版 正向
stu = db_session.query(Student).all()
for row in stu:
# row.to_class.name 通过Student对象中的关系字段relationship stu2sch获取关联 School中的name
    print(row.id,row.name,row.school_id,row.stu2sch.name)

db_session.close()
"""
1 异人夜 1 清华大学
2 chuan 3 香港大学
3 雯雯 4 厦门大学
4 lantian 4 厦门大学
"""

# 2.查询数据 - relationship 版 反向
sch = db_session.query(School).all()
for school in sch:
    for student in school.sch2stu:
        print(school.id, school.name,student.name)

db_session.close()
"""
1 清华大学 异人夜
3 香港大学 chuan
4 厦门大学 雯雯
4 厦门大学 lantian
"""

3.4 基于 relationship  修改数据 :   

# 添加数据
from sqlalchemy.orm import sessionmaker
from create_table_ForeignKey import engine,Student,School

Session = sessionmaker(engine)
db_session = Session()

# 修改数据
sch = db_session.query(School).filter(School.name=="北京大学").first()
db_session.query(Student).filter(Student.name=="lantian").update({"school_id":sch.id})
db_session.commit()
db_session.close()

3.5 基于 relationship  删除数据 : 

# 删除数据
from sqlalchemy.orm import sessionmaker
from create_table_ForeignKey import engine,Student,School

Session = sessionmaker(engine)
db_session = Session()


sch = db_session.query(School).filter(School.name=="北京大学").first()
db_session.query(Student).filter(Student.school_id == sch.id).delete()
db_session.commit()
db_session.close()

 

 

       4 . 多对多 : ManyToMany      

4.1 创建表及关系

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship


class Girls(Base):
    __tablename__ = "girl"
    id = Column(Integer, primary_key=True)
    name = Column(String(32))

    # 建立多表之间的关系,girl表与boy表建立关系,通过hotel表
    girl2boy = relationship("Boys", secondary="hotel", backref="boy2girl")


class Boys(Base):
    __tablename__ = "boy"
    id = Column(Integer, primary_key=True)
    name = Column(String(32))


class Hotel(Base):
    __tablename__ = "hotel"
    id = Column(Integer, primary_key=True)
    girl_id = Column(Integer, ForeignKey("girl.id"))
    boy_id = Column(Integer, ForeignKey("boy.id"))


from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/s1?charset=utf8")
Base.metadata.create_all(engine)

4.2.基于 relationship 增加数据 

# 多对多添加数据
from sqlalchemy.orm import sessionmaker
from create_table_manytomany import engine,Boys,Girls,Hotel

Session = sessionmaker(engine)
db_session = Session()

# 1.通过Boy添加Girl和Hotel数据,relationship  - 反向
boy = Boys(name="小明")
boy.boy2girl = [Girls(name="小丽"),Girls(name="小红")]
db_session.add(boy)
db_session.commit()
db_session.close()

# 2.通过Boy添加Girl和Hotel数据,relationship  - 正向
girl_obj = Girls(name="露丝",girl2boy=[Boys(name="小王"),Boys(name="小李")])
db_session.add(girl_obj)
db_session.commit()
db_session.close()

4.3.基于 relationship 查询数据 

# 多对多查询数据
from sqlalchemy.orm import sessionmaker
from create_table_manytomany import engine,Boys,Girls,Hotel

Session = sessionmaker(engine)
db_session = Session()

#  1.查询 relationship  正向
girl_list = db_session.query(Girls).all()
for girl in girl_list:
    for boy in girl.girl2boy:
        print(girl.name,boy.name)
"""
小丽 小明
小红 小明
露丝 小王
露丝 小李
"""

#  2.查询 relationship  反向
boy_list = db_session.query(Boys).all()
for boy in boy_list:
    for girl in boy.boy2girl:
        print(boy.name,girl.name)
"""
小明 小丽
小明 小红
小王 露丝
小李 露丝
"""