SQLAlchemy是Python一种编程语言ORM该框架基于数据库API数据库操作采用关系对象映射， 简而言之：将对象转换为：SQL，然后使用数据API执行SQL并获得执行结果。

SQLAlchemy SQL Toolkit和Object Relational Mapper处理数据库和Python综合工具。 它有几个不同的功能区，可以单独使用或组合使用。其主要组件如下所示，组件依赖于层层组织：

[外链图片存储失败，源站可能有防盗链机制，建议保存图片直接上传(img-ct9TMX84-1580611120495)(https://docs.sqlalchemy.org/en/latest/_images/sqla_arch_small.png)]

上面，SQLAlchemy两个最重要的前端部分是Object Relational Mapper和 SQL Expression Language。 SQL表达式可以独立于ORM使用。使用ORM时，SQL表达式语言仍然面向公众API因为它用于对象关系的配置和查询。

文档分为三个部分：SQLAlchemy ORM， SQLAlchemy Core和Dialects。

在SQLAlchemy ORM中，引入并完整描述了对象关系映射器。新用户应该从对象关系教程开始。如果您想使用为您自动构建的更高级别的SQL，以及Python对象的管理，请继续阅读本教程。

在SQLAlchemy Core中，记录了SQLAlchemy的SQL和数据库集成和描述服务的广度，其核心是SQL表达式语言。 SQL表达式语言是一个独立于ORM包的工具包，它可用于构造可操作的SQL表达式，可以通过编程方式构造，修改和执行，返回类似游标的结果集。 与ORM以域为中心的使用模式相反，表达式语言提供了以模式为中心的使用范例。 新用户应该从这里开始使用SQL Expression Language Tutorial。SQLAlchemy引擎，连接和池服务也在SQLAlchemy Core中描述 。

在Dialects中，提供了所有提供的数据库和DBAPI后端的参考文档。

官网链接： http://docs.sqlalchemy.org/en/latest/orm/tutorial.html 不同ORM框架对比说明 https://www.oschina.net/translate/sqlalchemy-vs-orms 常见命令 https://www.imooc.com/article/details/id/22343 常见命令2 https://www.cnblogs.com/booolee/archive/2009/08/26/1554525.html sqlalchemy框架说明 https://nettee.github.io/posts/2016/SQLAlchemy-Translation/

sqlalchemy的使用
1.sqlalchemy的使用
数据库表结构
[
    ('1', 'Michael'),
    ('2', 'Bob'),
    ('3', 'Adam')
]
ORM技术
ORM技术：Object-Relational Mapping，把关系数据库的表结构映射到对象上
在Python中，最有名的ORM框架是SQLAlchemy
用法setp1：第一步，导入SQLAlchemy，并初始化DBSession：
# 导入:
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base


# 创建对象的基类:
Base = declarative_base()

# 定义User对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(String(20), primary_key=True)
    name = Column(String(20))

# 初始化数据库连接:
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)
create_engine()用来初始化数据库连接。SQLAlchemy用一个字符串表示连接信息：
'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
setp2:使用session对象  --  Session对象可视为当前数据库连接。
# 创建session对象:
session = DBSession()
# 创建新User对象:
new_user = User(id='5', name='Bob')
# 添加到session:
session.add(new_user)
# 提交即保存到数据库:
session.commit()
# 关闭session:
session.close()
step3:查询数据库表
如何从数据库表中查询数据呢？有了ORM，查询出来的可以不再是tuple，而是User对象。SQLAlchemy提供的查询接口如下
# 创建Session:
session = DBSession()
# 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
user = session.query(User).filter(User.id=='5').one()
# 打印类型和对象的name属性:print 'type:', type(user)
print 'name:', user.name
# 关闭Session:
session.close()


运行结果如下：
type: <class '__main__.User'>
name: Bob
可见，ORM就是把数据库表的行与相应的对象建立关联，互相转换。
setp4：ORM的外键关联 -- relationship(向外连接), ForeignKey(连接主键)
由于关系数据库的多个表还可以用外键实现一对多、多对多等关联，相应地，ORM框架也可以提供两个对象之间的一对多、多对多等功能。
例如，如果一个User拥有多个Book，就可以定义一对多关系如下：
class User(Base):
  __tablename__ = 'user'
  id = Column(String(20), primary_key=True)
  name = Column(String(20))
  # 一对多:
  books = relationship('Book')
class Book(Base):
  __tablename__ = 'book'
  id = Column(String(20), primary_key=True)
  name = Column(String(20))
  # “多”的一方的book表是通过外键关联到user表的:
  user_id = Column(String(20), ForeignKey('user.id'))
当我们查询一个User对象时，该对象的books属性将返回一个包含若干个Book对象的list。
小结
ORM框架的作用就是把数据库表的一行记录与一个对象互相做自动转换。
正确使用ORM的前提是了解关系数据库的原理

用SQLAlchemy的主要原因是，把你从底层的数据库和SQL奇葩语法中解放出来。SQLAlchemy将常用语句和类型和SQL语句对应起 来，让你可以更容易地理解数据库类型，而不需要担心太多细节。这样在处理像Oracle到PostgreSQL数据库这类的迁移工作，或从一个应用数据库 到数据仓库时，事情就简单了。它还能确保数据在增加到数据库之前是经过安全的，适当转义处理的。这样可以避免SQL注入之类的事情发生。

SQLAlchemy通过两个主要的模型来实现灵活的操作：SQL表达式语言（通常也叫Core）和ORM（Object-relational mapping，对象关系映射）。这两个模型可以根据你的需要独立使用，也可以合在一起使用。

SQLAlchemy Core和SQL表达式语言

SQL表达式语言是用Pythonic方式的来表达SQL语句和表达式，只是对传统的SQL语言的轻微抽象。它侧重于实用数据库的模式 （schema，其实是具体到一个Tabel和View等），但是它实现了不同数据库之间标准化的接口。SQL表达式语言也是SQLAlchemy ORM的基础。

ORM

SQLAlchemy ORM与你在其他语言里遇到的ORM类似。它侧重于应用的Domain Model（一种将数据与其行为集成在一起的模式），借助工作单元的模式来维护对象状态。它还在SQL表达式语言之上增加了一层抽象，让用户可以更容易的 操作数据库。你可以把ORM和SQL表达式语言结合起来构建强大的应用。ORM构建了一个声明式的系统，与许多其他ORM模型（如Ruby on Rails）使用的 active-record systems类似。

虽然ORM非常有用，但是你要注意，类的很多用法与数据库的工作方式是不一样的。我们将在后面的章节介绍这些差异。

究竟是选择Core还是ORM作为应用的数据链接层呢？除了个人喜好，理由可以归结为一些影响因素。这两种模式的语法不太一样，但Core和ORM最大的差异是Core对数据模式和业务对象（business objects）的不同处理方式。

SQLAlchemy Core是以模式为中心，和普通SQL一样有表，键和索引等。SQLAlchemy Core最擅长的时数据仓库，报表分析，以及其他使用数据查询和其他操作可以牢牢掌控的地方。它拥有强大的数据库连接池（ connection pool）和数据结果集（ResultSet）优化，非常适合处理大量数据，甚至多数据库也适用。

但是，如果你更侧重于领域驱动设计(domain driven design)， 那么ORM就可以将原数据和业务对象的底层的模式和结构大部分细节都封装起来。这样封装让数据库连接更简单，更像Python代码。大多数应用都更适合按 照这种方法建模。ORM可以用一种非常高效的方法把领域驱动设计方法导入传统应用，或者改造原来带有原始SQL语句的应用。还有一个好处就是，通过对底层 数据库的合理抽象，ORM让开发者把精力更多地集中在业务流程的实现上。

不过，ORM是建立在SQLAlchemy Core基础之上的，你可以把处理MySQL的同样方式用于Oracle的数据仓库和Amazon Redshift数据库。当你需要业务对象和仓库数据时，ORM可以无缝的衔接每个环节。

如果你的应用框架已经使用了ORM，但是想要更强大的报表功能，使用Core

如果你不想像普通SQL一样以模式为中心，用ORM

如果你的数据不需要业务对象，用Core

如果你把数据看成业务对象，用ORM

如果要建立快速原型，用ORM

如果你既要业务对象，又要其他数据无关的功能（报表，数据分析等等），两个都用。

以MySQL为例，

• URL格式 mysql+{driver}://{username}:{password}@{host}:{port}/{name}，
• driver是Python的数据库驱动，比如MySQL官方的数据库驱动mysql-connector-python，driver是mysqlconnector；
• username是数据库用户名；
• password是密码；
• host是数据库主机；
• port是数据库端口；
• name是数据库名。

DB2的URL格式： URL ='ibm_db_sa://lqr:qwe123@155.104.1.141:50000/lqr'

ORACLE的URL格式： "oracle://$DB_USER:$DB_PASSWD@$DB_HOST:$DB_PORT/$DB_INSTANCE"

from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import sessionmaker

db_url = "mysql+mysqlconnector://root:@localhost:3306/test"

# 通过数据库连接url创建数据库引擎
# 如果想回显SQLAlchemy操作数据库的日志，设置echo=True
engine = create_engine(db_url, echo=True)

# 通过数据库引擎绑定元信息
metadata = MetaData(engine)

# 通过绑定数据库引擎获取数据库会话类
Session = sessionmaker(bind=engine)

# 获取数据库会话
session = Session()

以ORM方式来对数据库中的数据做增删查改操作是通过Session实例来完成的， 在学习了解如何以ORM方式操作数据之前首先我们要对数据的状态有个基本的了解。

首先在ORM中，数据库中的数据表对应于Python中的类，而数据表中的记录对应于类的实例对象。 因此，对数据表中的记录进行增删查改在Python中实际上就是对实例对象的操作。 数据实例对象有四种状态，分别是

Transient - （瞬时的）

表示该实例对象不在session中，当然也没有保存到数据库中， 主键一般情况下为None（如果一个Persistent状态的对象进行事务回滚后虽然主键有值，但却是Transient状态）。

Pending - （挂起的）

调用session.add()后，Transient状态的对象就会变成Pending状态的对象，这个时候它只是在session中， 并没有保存到数据库，因此主键依旧为None。 只有触发了session.flush()操作才会保存到数据库使得主键有值，比如查询操作就会触发flush。

Persistent - （持久的） session和数据库中都有对应的记录存在，为持久状态。

Detached - （游离的） 数据库中可能有记录，但是session中不存在。对这种对象的操作不会触发任何SQL语句。 要查看数据对象的状态可以用如下方式

>>> from sqlalchemy import inspect
>>> status = inspect(data_object)
>>> status.persistent
True

数据库的数据类型和常用参数示例

import sqlalchemy
from datetime import datetime
from sqlalchemy import create_engine,Column,Integer,String,Text,Boolean,Date,DateTime,Float
from sqlalchemy.ext.declarative import declarative_base
# ======================================================================================================================

# 数据库的数据类型和常用参数示例

# 1. Integer：整形，映射到数据库中的int类型。
# 2. String：字符类型，映射到数据库中的varchar类型，使用时，需要提供一个字符长度。
# 3. Text：文本类型，映射到数据库中的text类型。
# 4. Boolean：布尔类型，映射到数据库中的bool类型，在使用的时候，传递`True/False`进去。
# 5. Date：日期类型，没有时间。映射到数据库中是`date`类型，在使用的时候，传递`datetime.date()`进去。
# 6. DateTime：日期时间类型。映射到数据库中的是`datetime`类型，在使用的时候，传递`datetime.datetime()`进去。
# 7. Float：浮点类型。

# 1. `primary_key`：主键，True和False。
# 2. `autoincrement`：是否自动增长，True和False。
# 3. `unique`：是否唯一。
# 4. `nullable`：是否可空，默认是True。
# 5. `default`：默认值。
# 6. `onupdate`：在更新的时候，一般用在时间上面。

记录sqlalchemy的API导入

# 常用包引入
from sqlalchemy import and_, func, or_

Sqlchemy常用API接口、参数。

import sqlalchemy
print dir(sqlalchemy)
In [2]: print dir(sqlalchemy)
['ARRAY', 'BIGINT', 'BINARY', 'BLANK_SCHEMA', 'BLOB', 'BOOLEAN', 'BigInteger', 'Binary', 'Boolean', 'CHAR', 'CLOB', 'CheckConstraint', 'Column', 'ColumnDefault', 'Constraint', 'DATE', 'DATETIME', 'DDL', 'DECIMAL', 'Date', 'DateTime', 'DefaultClause', 'Enum', 'FLOAT', 'FetchedValue', 'Float', 'ForeignKey', 'ForeignKeyConstraint', 'INT', 'INTEGER', 'Index', 'Integer', 'Interval', 'JSON', 'LargeBinary', 'MetaData', 'NCHAR', 'NUMERIC', 'NVARCHAR', 'Numeric', 'PassiveDefault', 'PickleType', 'PrimaryKeyConstraint', 'REAL', 'SMALLINT', 'Sequence', 'SmallInteger', 'String', 'TEXT', 'TIME', 'TIMESTAMP', 'Table', 'Text', 'ThreadLocalMetaData', 'Time', 'TypeDecorator', 'Unicode', 'UnicodeText', 'UniqueConstraint', 'VARBINARY', 'VARCHAR', '__all__', '__builtins__', '__doc__', '__file__', '__go', '__name__', '__package__', '__path__', '__version__', 'alias', 'all_', 'and_', 'any_', 'asc', 'between', 'bindparam', 'case', 'cast', 'collate', 'column', 'cprocessors', 'create_engine', 'cresultproxy', 'cutils', 'delete', 'desc', 'dialects', 'distinct', 'engine', 'engine_from_config', 'event', 'events', 'exc', 'except_', 'except_all', 'exists', 'extract', 'false', 'func', 'funcfilter', 'insert', 'inspect', 'inspection', 'interfaces', 'intersect', 'intersect_all', 'join', 'lateral', 'literal', 'literal_column', 'log', 'modifier', 'not_', 'null', 'nullsfirst', 'nullslast', 'or_', 'outerjoin', 'outparam', 'over', 'pool', 'processors', 'schema', 'select', 'sql', 'subquery', 'table', 'tablesample', 'text', 'true', 'tuple_', 'type_coerce', 'types', 'union', 'union_all', 'update', 'util', 'within_group']

# 查询去重
session.query(Phone.brand).distinct().all()
# 查询数据是否存在
session.query(User).filter(exists().where(Address.user_id == User.id))
session.query(User).filter(User.addresses.any())
# 查询数据条数
sessino.query(User).all().count()

# 查询模糊匹配
session.query(User).filter(User.code.like('%'+code.strip().upper()+'%')).all()

查询-func函数

from sqlalchemy import func
# 忽略大小写
session.query(User).filter(func.upper(User.code)=='AAA').all()
session.query(User).filter(func.lower(User.code)=='aaa').all()
# 日期格式转字符
session.query(User).filter(func.to_char(User.code)=='2018-01-01').all()
# 列表查询 + 倒序/顺序查询
from sqlalchemy import asc, desc
User.query.filter(User.id.in_((1, 2, 3))).order_by(desc(User.datetime)).all()
# 

简单查询

session.query(User).all()
session.query(User.name, User.fullname).all()
session.query(User, User.name).all()

带条件查询 filter_by filter like

session.query(User).filter_by(name='user1').all())
session.query(User).filter(User.name == "user").all()) 
print(session.query(User).filter(User.name.like("user%")).all()

多条件查询 and_ or_ like ilike

print(session.query(User).filter(and_(User.name.like("user%"), User.fullname.like("first%"))).all())
print(session.query(User).filter(or_(User.name.like("user%"), User.password != None)).all())

sql过滤 filter params

session.query(User).filter("id>:id").params(id=1).all()

关联查询 join outerjoin innerjoin

session.query(User, Address).filter(User.id == Address.user_id).all()
session.query(User).join(User.addresses).all()
session.query(User).outerjoin(User.addresses).all()

聚合查询 func.count func.sum group_by label