Python入门学习笔记17（sqlalchemyd的使用）

Object-Relational Mapping，作用是将关系型数据库的表结构映射到对象上，使我们可以不必关心具体的SQL语句进行数据库的增删改查操作。

缺点是映射操作不可避免的会有性能上的损耗，优点是不必考虑SQL，程序与数据库的交互被封装，可以快速地开发。

sqlalchemy是Python中最著名的ORM框架。

sqlalchemy连接数据库

sqlalchemy支持主流的数据库，连接不同的数据库需要安装不同的组件
1.连接mysql（mariadb）
sqlalchemy默认使用mysql-python作为链接驱动，既default模式
选哪种驱动，就装哪个包。
#default默认链接方式
engine = create_engine('mysql://scott:tiger@localhost/foo')
# mysql-python，声明使用mysql-python驱动
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')
#MySQL-connector-python 声明使用MySQL-connector-python驱动（推荐使用）
engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')
#OurSQL 声明使用OurSQL驱动
engine = create_engine('mysql+oursql://scott:tiger@localhost/foo')

2.连接Microsoft SQL Server
sqlalchemy默认使用 pyodbc作为链接驱动。
#pyodbc
engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')
#pymssql
engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')

3.连接PostgreSQL
PostgreSQL默认使用 psycopg2作为链接驱动，既default模式
#default
engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')
#psycopg2
engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')
#pg8000
engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')

4.连接Oracle
Oracle可能只有 cx_oracle一个驱动包，既default模式和声明模式一样。
#default
engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')
#cx_oracle
engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')

使用sqlalchemy创建数据库中表的映射

sqlalchemy创建映射的方法有两种：

1.定义一个类通过这个类来映射表结构

from sqlalchemy.orm import sessionmaker
#映射类的基类
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine import create_engine
from sqlalchemy import Column,Integer,String

"""
定义一个类，用来映射数据库中的表
该类的基类通过declarative_base()获得
"""
Base = declarative_base()
class Customer(Base):
    __tablename__ = "customer"
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    age = Column(Integer)
    #用于打印对象时的输出
    def __repr__(self):
        return "id:%d name:%s age:%d" %(self.id,self.name,self.age)

2.通过Table创建映射

from sqlalchemy import MetaData,Table,Column,String,Integer
from sqlalchemy import create_engine
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm import mapper

"""
通过MetaData创建数据库表的表结构,这种创建方法一般用于不需要对该表手动进行操作的情况，
例如连接多张表的中间表
"""
metadata = MetaData()
customer = Table("customer",metadata,
                 Column("id",Integer,primary_key=True),
                 Column("name",String(32)),
                 Column("age",Integer)
                 )

"""
定义一个字段与表一一对应的普通类，注意此时继承的是object
"""
class Customer(object):
    def __init__(self,id,name,age):
        self.id=id
        self.name=name
        self.age=age
    #用于打印对象时的输出
    def __repr__(self):
        return "id:%d name:%s age:%d" %(self.id,self.name,self.age)

"""
通过mapper建立表结构与类的映射
"""
mapper(Customer, customer)

　　

常见的表约束关系

class Customer(Base):
    __tablename__ = "customer"
    #primary_key 主键，默认identity(1,1)
    id = Column(Integer,primary_key=True,autoincrement=False)
    #创建自增列 identity(100,1),注意一个表只能有一个自增列
    auto_id = Column(Integer,Sequence("auto_id_seq",start=100,increment=1))
    #唯一键约束
    unique_id = Column(Integer,unique=True)
    #联合唯一
    unique_name = Column(String(32))
    unique_address = Column(String(32))

    #建立联合唯一约束
    __table_args__ = (UniqueConstraint("unique_name","unique_address"),)

　　

增删改查

1.前置工作，创建表结构映射关系

from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy import or_,and_

engine = create_engine('mssql+pyodbc://python:123456@mydb',echo=False)

session_class = sessionmaker(bind=engine)
session = session_class()

Base =declarative_base()

"""
定义表结构
"""
class Student(Base):
    __tablename__ = "student"
    """
    sqlalchemy默认定义为主键便是自增的，可以用autoincrement手动指定
    """
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    grade_and_class = Column(String(32),nullable=False)
    age = Column(Integer,nullable=False)

    def __repr__(self):
        return "%d %s %s" %(self.id,self.name,self.grade_and_class)

使用sqlalchemy进行数据库的增删改查操作都是基于上面定义的映射关系

2.创建表

Base.metadata.create_all(engine)

定义好映射关系之后执行create_all操作，会循环继承自Base的所有类，如果数据库中不存在同名的表，那么orm便会根据类中的定义创建一张新的表。如果已经存在同名的表则直接pass

3.删除表

Base.metadata.drop_all(engine)

定义好映射关系之后执行drop_all操作，会循环继承自Base的所有类并将其从数据库中删除

4.添加记录

obj = Student(name="马克",grade_and_class="202",age=19)
session.add(obj)
obj = Student(name="Mike",grade_and_class="102",age=18)
session.add(obj)

obj1 = Student(name="Jack",grade_and_class="202",age=18)
obj2 = Student(name="Tom",grade_and_class="202",age=18)
session.add_all([obj1,obj2])

#add只是将对象添加到session之中，必须执行commit才会提交到数据库
session.commit()

5.删除记录

objs =session.query(Student).filter(Student.name=="Pinkman").delete()

或者

objs =session.query(Student).filter(Student.name=="Pinkman")
for obj in objs:
    session.delete(obj)

6.修改记录

方法一：

"""
修改数据
"""
objs = session.query(Student).filter(Student.name=="Tom").all()
for obj in objs:
    obj.name = "Pinkman"
session.commit()

直接在映射上进行修改，session进行commit之后就会反映到数据库中。

方法二：

使用update

session.query(Student).filter(Student.name=="Tom").update({Student.name=="Pinkman"})

　　

6.回滚

session操作的时候相当于自动开启了一个begin，在commit之前可以任意回滚

session.query(Student).delete()
session.rollback()
session.query(Student).filter(Student.name == "Mike").delete()
session.commit()

7.查询

"""
filter与filter_by的传入参数格式有区别，其他的一样
以下四种查询限定条件的写法结果完全一样
查询不用执行commit，因为对数据库并没有修改
"""
objs = session.query(Student).filter(Student.grade_and_class=="202",Student.age==18).all()
print(objs)
objs = session.query(Student).filter_by(grade_and_class="202",age=18).all()
print(objs)
objs = session.query(Student).filter(Student.grade_and_class == "202").filter(Student.age == 18).all()
print(objs)
objs = session.query(Student).filter_by(grade_and_class="202").filter_by(age=18).all()
print(objs)

 like：

objs = session.query(Student).filter(Student.name.like("%i%"))

in：

objs = session.query(Student).filter(Student.age.in_([18,20]))

not in：

objs = session.query(Student).filter(~Student.age.in_([18,20]))

or：

objs = session.query(Student).filter(or_(Student.age==18,Student.name=="马克"))

and：

objs = session.query(Student).filter(and_(Student.age==18,Student.name=="马克"))

分组函数：

#所有的分组函数都定义在func中
objs1 = session.query(Student.name,func.sum(Student.age)).group_by(Student.name).all()


objs2 = session.query(Student.name,func.count(Student.name)).group_by(Student.name).all()

打印行号row_number:

session.query(over(func.row_number(),order_by=(Member.id)).label("row_num"),Member).all()

8.多表联合查询

定义一个用户表member和一个用户角色表role，member通过role_id和role的id做连接。

如果没有建立外键约束或者不在映射中定义虚拟连接对象，可以使用多表联合查询进行查询。

cross join：

objs = session.query(Member,Role).filter(Member.role_id==Role.id).all()

inner join：

query中的第一个表为内连接左表，join中的表为内连接右表。

objs = session.query(Member,Role).join(Role,Role.id == Member.role_id).all()

outer join：

outjoin是左外链接，没有单独的右外连接，有需求的话直接将两个表反过来（小表左连接右表效率高）

objs = session.query(Member,Role).outerjoin(Role,Role.id == Member.role_id).all()

排序

对字段进行排序有多种方法：

1.使用order_by

#排序方法一
#正序
res = session.query(Member).order_by(Member.role_id).all()
#逆序一，使用desc方法
# res = session.query(Member).order_by(desc(Member.role_id)).all()
#逆序二，在排序列前面加上"-"
# res = session.query(Member).order_by(-Member.role_id).all()
for item in res:
    print(item.role_id)

#排序方法二：使用字符串，注意此时如果要指定表名的话必须使用数据库表名
res = session.query(Member).order_by("role_id").all()
# res = session.query(Member).order_by("role_id desc").all()
# res = session.query(Member).order_by("-role_id").all()
for item in res:
    print(item.role_id)

2.直接在定义映射时使用__mapper_args__设置排序

class Member(Base):
    __tablename__ = "member_demo"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    role_id = Column(Integer)

    def __repr__(self):
        return "%s %s %s" %(self.id,self.name,self.role_id)

    #定义默认的排序
    __mapper_args__ = {
        "order_by":role_id.desc()
    }

在__mpper_args__中定义了order_by之后，orm查询出来的数据就会默认排序，可以调用order_by()来改变排序顺序

外键关联

定义如下两张表，以id进行外键关联，注意外键关联的字段必须是主键或者是unique

"""
商品信息表goods：
id(编号，主键)
name(名称，非空)
place_of_origin_id(产地id，非空)
产地信息表origin：
id(编号，主键)
name(名称，非空)
"""

定义映射：

class Goods(Base):
    __tablename__ = "goods_demo"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    #创建外键
    place_of_origin_id = Column(Integer,ForeignKey("origin_demo.id"),nullable=False)
　　 #创建一个虚拟关系，通过该字段可以直接访问关联的外键表，而外键表也可以使用backref定义的字段来反向查询主表信息
    origin = relationship("Origin",backref="goods")

    def __repr__(self):
        return "[%s]产地[%s]" %(self.name,self.origin.name)


class Origin(Base):
    __tablename__ = "origin_demo"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)

    def __repr__(self):
        return "%d:%s" %(self.id,self.name)

engine = create_engine('mssql+pyodbc://python:123456@mydb',echo=False)
#建表
Base.metadata.create_all(engine)

维护外键的时候，如果在relationship中定义了backref的话，那么外键表通过该字段便可以直接反查与自己相关的引用表的数据

#引用外键的表可以通过定义的relationship访问关联的外键表数据
print("from goods to origin")
goods = session.query(Goods).filter(Goods.name == "英国的故事").all()
for item in goods:
    print(item.origin)

#外键表可以通过引用外键表中relationship中定义的backref反向查询与自己有关联的表的数据
print("from origin to goods")
origins = session.query(Origin).all()
for origin in origins:
    print(origin.goods)

多外键关联

普通的多外键语法与单外键完全一样，但如果一张表的多个字段应用了同一张表的同一个字段作为外键的话，必须在relationship中显示的声明外键对应的字段，否则在插入数据的时候程序无法区分会报错

class Mail(Base):
    __tablename__ = "Mail_demo"
    id = Column(Integer,primary_key=True)
    addr_id1 = Column(Integer,ForeignKey("address_demo.id"),nullable=False)
    addr_id2 = Column(Integer,ForeignKey("address_demo.id"),nullable=False)

    addr1 = relationship("Address",foreign_keys=[addr_id1])
    addr2 = relationship("Address",foreign_keys=[addr_id2])

多对多的表结构　

如果两个事物之间存在一种多对多的关系，比如一本书可能有一个以上的作者，而一个作者又可能写过一本以上的书，此时要维护两者之间的关系，一般会将这种关系抽离出来作为一个单独的表，加上单纯的书本信息表，作者信息表，三者构成一个多对多的数据库关系。

"""
图书信息表book
id
name
作者信息表author
id
name
图书作者关系表relation_book_author
book_id
author_id

图书信息和作者信息是多对多的关系，对应关系由关系表维护
"""

定义三个表的映射：

#中间表
relation_book_author = Table("relation_book_author_demo",Base.metadata,
                             Column("book_id",Integer,ForeignKey("book_demo.id")),
                             Column("author_id",Integer,ForeignKey("author_demo.id"))
                             )

class Book(Base):
    __tablename__ = "book_demo"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)

    authors = relationship("Author",secondary=relation_book_author,backref="books")

    def __repr__(self):
        return self.name

class Author(Base):
    __tablename__ = "author_demo"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)

    def __repr__(self):
        return self.name

添加数据

book1 = Book(name="算法导论")
book2 = Book(name="计算机系统")
book3 = Book(name="Linux基础")
book4 = Book(name="Python的前世今生")

author1 = Author(name="谢尔曼")
author2 = Author(name="肖恩")
author3 = Author(name="曼宁")
author4 = Author(name="凯夫拉")

book1.authors = [author1]
book2.authors = [author1,author2]
book3.authors = [author2,author3,author1]
book4.authors = [author2,author3]

#如果只是add所有的book的话，那么和book有关联信息的authoe也会一并提交
#注意，提交到数据库的顺序并不一定就是程序写的顺序，relation表中的id也许会和程序中的顺序不一样
session.add_all([book1,book2,book3,book4,author1,author2,author3,author4])
session.commit()

上面的代码没有直接对图书作者关系信息表进行操作，相关的操作都是orm为我们自动执行的

查询：

obj_book = session.query(Book).filter(Book.name == "Linux基础").all()
print("Linux基础的作者".center(30,"-"))
for item in obj_book:
    print(item.authors)

obj_author = session.query(Author).filter(Author.name == "肖恩").all()
print("肖恩写的书".center(30,"-"))
for item in obj_author:
    print(item.books)

结果

----------Linux基础的作者----------
[肖恩, 谢尔曼, 曼宁]
------------肖恩写的书-------------
[计算机系统, Linux基础, Python的前世今生]

同样的，直接删除Book和Author映射对象之后，orm也会为我们自动清理relation_book_author表中对应的数据。

直接删除书
obj_book = session.query(Book).filter(Book.name == "算法导论").all()
for item in obj_book:
    session.delete(item)
    session.commit()

删除某本书的一个作者
obj_book = session.query(Book).filter(Book.name == "Linux基础").first()
obj_author = session.query(Author).filter(Author.name == "肖恩").first()

if obj_author in obj_book.authors:
    obj_book.authors.remove(obj_author)
    session.commit()

子查询

使用subquery可以生成一个临时表，可对该表进行一系列的查询、连接操作

tmp=session.query(Member.name,func.sum(Member.role_id).label("sum")).group_by(Member.name).subquery()
res = session.query(tmp.c.sum).all()

注意引用临时表的列名时在表名和列名之间有一个c