SQLAlchemy——Python SQL toolkit and Object Relational Mapper

ORM：Object-Relational Mapping，把关系数据库的表结构映射到对象上。
在Python中，最有名的ORM框架是SQLAlchemy。

本文以操作MySQL数据库为例

1. 安装SQLAlchemy

pip install SQLAlchemy

mysql windows安装
https://dev.mysql.com/downloads/installer/

2. 创建数据库引擎

The Engine is the starting point for any SQLAlchemy application. It’s “home base” for the actual database and its DBAPI, delivered to the SQLAlchemy application through a connection pool and a Dialect, which describes how to talk to a specific kind of database/DBAPI combination.

The general structure can be illustrated as follows:

Where above, an Engine references both a Dialect and a Pool, which together interpret the DBAPI’s module functions as well as the behavior of the database.

Creating an engine is just a matter of issuing a single call, create_engine():

from sqlalchemy import create_engine
engine = create_engine(‘postgresql://scott:tiger@localhost:5432/mydatabase’)

The above engine creates a Dialect object tailored towards PostgreSQL, as well as a Pool object which will establish a DBAPI connection at localhost:5432 when a connection request is first received. Note that the Engine and its underlying Pool do not establish the first actual DBAPI connection until the Engine.connect() method is called, or an operation which is dependent on this method such as Engine.execute() is invoked. In this way, Engine and Pool can be said to have a lazy initialization behavior.

The Engine, once created, can either be used directly to interact with the database, or can be passed to a Session object to work with the ORM. This section covers the details of configuring an Engine. The next section, Working with Engines and Connections, will detail the usage API of the Engine and similar, typically for non-ORM applications.

Database Urls
The create_engine() function produces an Engine object based on a URL. These URLs follow RFC-1738, and usually can include username, password, hostname, database name as well as optional keyword arguments for additional configuration. In some cases a file path is accepted, and in others a “data source name” replaces the “host” and “database” portions.

The typical form of a database URL is:

dialect+driver://username:password@host:port/database

Dialect names include the identifying name of the SQLAlchemy dialect, a name such as sqlite, mysql, postgresql, oracle, or mssql. The drivername is the name of the DBAPI to be used to connect to the database using all lowercase letters. If not specified, a “default” DBAPI will be imported if available - this default is typically the most widely known driver available for that backend.

对于各种引擎创建方式：
数据库+数据库驱动，只要安装了相应的数据库和驱动都能使用。

1、create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')
这个mysqlconnector是MySQL自带的数据库驱动程序

2、create_engine('mysql+mysqldb://root:password@localhost:3306/test')
使用mysqldb驱动连接数据库

3、create_engine('mysql://root:password@localhost:3306/test')
这个没有驱动器的情况下

3. 数据库操作

3.1 使用传统的connection的方式连接和操作数据库

connection事务：使用事务可以进行批量提交和回滚

with engine.connect() as connection:
    trans = connection.begin()
    try:
        r1 = connection.execute("select * from user")
        r2 = connection.execute("insert into user(id,name) values( '23' ,'lockey')")
        trans.commit()
    except:
        trans.rollback()
        raise

3.2 通过session对象连接并且操作数据库

通过session对象，session可以记录和跟踪数据的改变，在适当的时候提交，并且支持强大的ORM的功能

#!/usr/bin/env python
#coding:utf-8
'''
file: sqlalchemyLearning.py
date: 2018/2/6 13:52
author: lockey
email: iooiooi23@163.com
github: https://github.com/LockeyCheng
csdn: http://blog.csdn.net/Lockey23
desc: 
'''
from sqlalchemy import Column, String, create_engine,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

# 初始化数据库连接,echo为True时,会打印所有的sql语句，echo参数可省略
engine = create_engine('mysql+mysqlconnector://lockey:lockey23@localhost:3306/test',echo=True)
#创建表（如果表已经存在，则不会创建）
Base.metadata.create_all(engine)
DBSession = sessionmaker(bind=engine)
# 创建session对象:
session = DBSession()

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

def testfunc():
    user = User(id = '13',name='lockey23')
    book = Book(id="1",name='book1')
    user.books = [book]
    session.add(user)
    session.commit()

    #修改数据
    session.query(User).filter(User.id == 1).update({'name': 'admin'})
    session.commit()

    #删除数据
    session.query(Role).filter(Role.id == 1).delete()
    session.commit()

    #查询数据
    #1 返回结果集的第二项
    user = session.query(User).get(2)

    #2 返回结果集中的第2-3项
    users = session.query(User)[1:3]

    #3 查询条件
    user = session.query(User).filter(User.id < 6).first()

    #4 排序
    users = session.query(User).order_by(User.name)

    #5 降序（需要导入desc方法）
    from sqlalchemy import desc
    users = session.query(User).order_by(desc(User.name))

    #6 只查询部分属性
    users = session.query(User.name).order_by(desc(User.name))
    for user in users:
        print user.name

    #7 给结果集的列取别名
    users = session.query(User.name.label('user_name')).all()
    for user in users:
        print user.user_name

    #8 去重查询（需要导入distinct方法）
    from sqlalchemy import distinct
    users = session.query(distinct(User.name).label('name')).all()

    #9 统计查询
    user_count = session.query(User.name).order_by(User.name).count()
    age_avg = session.query(func.avg(User.age)).first()
    age_sum = session.query(func.sum(User.age)).first()

    #10 分组查询
    users = session.query(func.count(User.name).label('count'), User.age).group_by(User.age)
    for user in users:
        print 'age:{0}, count:{1}'.format(user.age, user.count)

    #exists查询(不存在则为~exists())
    from sqlalchemy.sql import exists
    session.query(User.name).filter(~exists().where(User.name == 'lockey'))

    #除了exists，any也可以表示EXISTS
    session.query(User).filter(User.name.any())

    #random
    from sqlalchemy.sql.functions import random
    user = session.query(User).order_by(random()).first()

多表查询
多表查询通常使用join进行表连接，第一个参数为表名，第二个参数为条件，例如

users = db.session.query(User).join(Role, Role.id == User.role_id)

for u in users:
    print u.name

join为内连接，还有左连接outerjoin，用法与join类似，右连接和全外链接在1.0版本上不支持，通常来说有这两个结合查询的方法基本够用了，1.1版本貌似添加了右连接和全外连接的支持，但是目前只是预览版

还可以直接查询多个表，如下

result = db.session.query(User, Role).filter(User.role_id = Role.id)
# 这里选择的是两个表，使用元组获取数据
for u, r in result:
      print u.name

参考资料
http://docs.sqlalchemy.org/en/latest/intro.html
https://segmentfault.com/a/1190000006949536
http://www.sqlalchemy.org/