SQLAlchemy

一、简介

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果

二、使用

 连接数据库

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.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

engine = create_engine("mysql+pymysql://root:xzwz0512@127.0.0.1:3306/t1?charset=utf8", max_overflow=5)
# mysql+pymysql:固定格式
# user02:user02:用户名及密码
# @192.168.33.35:3306/
# aaa:数据库名字
# ?charset=utf8:支持utf8
Base = declarative_base()  # 生成orm基类

class Author(Base):
    __tablename__ = "author" #表名
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(30), index=True)
　　# 用户查询，与生成数据库表结构无关
    bok = relationship("Book", backref="author", lazy='dynamic')

class Book(Base):
    __tablename__ = "book"
    id = Column(Integer, primary_key=True, autoincrement=True)
    book_name = Column(String(120), index=True)
　　# 外键关联，与Author表形式一对多关系
    author_id = Column(Integer, ForeignKey("author.id"))

class Public(Base):
    __tablename__ = "public"
    id = Column(Integer, primary_key=True, autoincrement=True)
    public_name = Column(String(60), index=True)


# 第三张表用于形成多对多关系
class PublicToBook(Base):
    __tablename__ = "publictobook"
    id = Column(Integer, primary_key=True, autoincrement=True)
    book_id = Column(Integer, ForeignKey("book.id"))
    public_id = Column(Integer, ForeignKey("public.id"))

# 如果您想要一对一关系，您可以把 uselist=False 传给relationship()
# azy决定了 SQLAlchemy 什么时候从数据库中加载数据
"""
'select' (默认值) 就是说 SQLAlchemy 会使用一个标准的 select 语句必要时一次加载数据。
'joined' 告诉 SQLAlchemy 使用 JOIN 语句作为父级在同一查询中来加载关系。
'subquery' 类似 'joined' ，但是 SQLAlchemy 会使用子查询。
'dynamic' 在有多条数据的时候是特别有用的。不是直接加载这些数据，SQLAlchemy 会返回一个查询对象，在加载数据前您可以过滤（提取）它们
"""

ForeignKey()两种写法的区别

user_type_id = Column(Integer, ForeignKey(UserType.user_type_id))  # 使用这个写法，必须把UserType表放在前面
user_type_id = Column(Integer, ForeignKey('user_type.user_type_id'))

__repr__

在类加入这个方法，用于格式化

def __repr__(self):
    tmp = '%s - %s' % (self.id, self.book_name)
    return tmp
# res = session.query(Book).filter_by(book_name="目送").first()
"""
加之前：<__main__.Book object at 0x03A1D8F0>
加之后：2 - 目送
"""

删除表

def drop_db():
    Base.metadata.drop_all(engine)

基本版CRUD

增

Session = sessionmaker(engine)
session = Session()

# 增加一条
author1 = Author(name="江南")
session.add(author1)
session.commit()

#增加多条
author1 = Author(name="江南")
author2 = Author(name="龙应台")

session.add_all([
    author1,
    author2
])

session.commit()

删

session.query(Book).filter_by(book_name="123").delete()
session.commit()

改

session.query(Book).filter_by(book_name="龙族").update({Book.book_name:"龙族I"})
session.commit()

#字符串拼接
session.query(Book).filter_by(book_name="龙族I").update({Book.book_name:Book.book_name + "II"}, synchronize_session=False)
session.commit()

#数字相加
session.query(Test).filter_by(id = 1001).update({Test.num: Test.num + 1001230, })
session.commit()

查

ret = session.query(Author).all()  # 已对象形式返回所有数据
ret = session.query(Author.name, Author.id).all()
ret = session.query(Author).filter_by(name='a').all()
ret = session.query(Author).filter_by(name='a').first()

进阶版CRUD

查

ret = session.query(User).filter(User.id > 2, User.name == 'c').all()
ret = session.query(User).filter(User.id.between(1, 3), User.name == 'c').all()
ret = session.query(User).filter(User.id.in_([1, 3, 4])).all()
ret = session.query(User).filter(~User.id.in_([1, 3, 4])).all()
ret = session.query(User).filter(User.id.in_(session.query(User.id).filter_by(name='c'))).all()  # 嵌套查询

and、or

from sqlalchemy import and_,or_

ret = session.query(User).filter(and_(User.id > 2, User.name == 'c')).all()
ret = session.query(User).filter(or_(User.id > 2, and_(User.name == 'c'), User.extra != '')).all()

通配符

ret = session.query(User).filter(User.name.like('e%')).all()  # 查询已e开始的name字段
ret = session.query(User).filter(~User.name.like('e%')).all()

限制

ret = session.query(User)[1:4]  # 这个不需要all

排序

ret = session.query(User).order_by(User.name.desc()).all()  # 从高到低
ret = session.query(User).order_by(User.name.desc(), User.id.asc()).all()

连表操作

ret = session.query(Colour, Dress).filter(Colour.cid == Dress.colour_id).all()
print(ret)
for i in ret:
    print(i.name)
ret = session.query(Dress).join(Colour).all()  # 默认是inner join
ret = session.query(Dress).join(Colour, isouter=True).all()  # 默认是left join

sql = session.query(Dress.name, Colour.colour).join(Colour)  # 输出sql语句
print(sql)
a = session.query(Dress.name, Colour.colour).join(Colour).all()
print(a)

组合

q1 = session.query(User.name).filter(User.id > 4)
q2 = session.query(Colour.colour).filter(Colour.colour == 'red')
ret = q1.union(q2).all()  # 默认去重

q1 = session.query(User.name).filter(User.id > 4)
q2 = session.query(Colour.colour).filter(Colour.colour == 'red')
ret = q1.union_all(q2).all()  # 不去重

relationship

这个功能只是优化在你写代码过程中，进一步优化

一般情况下，relationship跟外键在一起，当用显示存在obj.col这个方式的时候，我们一般叫正向查找，当使用backref叫做反向查找

正向查找

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

engine = create_engine("mysql+pymysql://user02:user02@192.168.33.35:3306/aaa?charset=utf8", max_overflow=5)

Base = declarative_base()  # 生成orm基类

# 一对多
class Colour(Base):
    __tablename__ = 'colour'  # 表名
    cid = Column(Integer, primary_key=True, autoincrement=True)
    colour = Column(String(20), default='red', unique=True)


class Dress(Base):
    __tablename__ = 'dress'  # 表名
    did = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    colour_id = Column(Integer, ForeignKey(Colour.cid))
    # 与生成表结构无关，仅用于查询方便
    col = relationship("Colour", backref='uuu')

Session = sessionmaker(bind=engine)
session = Session()
ret = session.query(Dress).all()
for obj in ret:
    # obj代指Dress的每一行数据
    # obj.col代指group对象，封装了Group里面的所有数据
    print(obj.did, obj.name, obj.col.colour, obj.col.cid)

反向查找

Session = sessionmaker(bind=engine)
session = Session()
obj = session.query(Colour).filter(Colour.colour == 'red').first()
print(obj.cid)
print(obj.colour)
print(obj.uuu)