python 64式: 第20式、sqlalchemy进行数据库操作与alembic进行数据库升级

文章目录编排如下:
1 引言
2 使用sqlalchemy实现数据库增删改查
3 使用alembic进行数据库升级
4 总结

1 引言

sqlalchemy是python项目采用的ORM(对象关系映射)，主要用于数据库相关的操作。
而alembic是与sqlalchemy搭配使用的数据库升级工具，主要用于数据库相关表结构的修改升级。

基于这篇文章:

python 64式: 第18式、python项目通用rest api框架搭建与编写

的基础上，介绍编写sqlalchemy与alembic部分。

前提:已经创建了项目myproject

2 使用sqlalchemy实现数据库增删改查

2.1 在myproject下面创建db目录
2.2 db目录下创建__init__.py文件，文件内容如下:
#!/usr/bin/env python
# -*- coding: utf-8 -*-

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import retrying
import six.moves.urllib.parse as urlparse
from stevedore import driver

G_NAMESPACE = "myproject.storage"

def getConnectionFromConfig(conf):
    url = conf.database.connection
    connectionScheme = urlparse.urlparse(url).scheme
    mgr = driver.DriverManager(G_NAMESPACE, connectionScheme)
    retries = conf.database.max_retries

    @retrying.retry(wait_fixed=conf.database.retry_interval * 1000,
                    stop_max_attempt_number=retries if retries >= 0 else None)
    def getConnection():
        return mgr.driver(conf)

    connection = getConnection()
    return connection

分析:
1) 这里最重要的代码如下:
mgr = driver.DriverManager(G_NAMESPACE, connectionScheme)
主要是采用stevedore.driver.DriverManager来获取对应的数据库插件。
stevedore.driver.DriverManager :一个名字对应一个entry point。根据插件命名空间和名字，定位到单独插件
stevedore.driver.DriverManager(namespace, name, invoke_on_load, invoke_args=(), invoke_kwds={})
namespace: 命名空间
name: 插件名称
invoke_on_load:如果为True，表示会实例化该插件的类
invoke_args:调用插件对象时传入的位置参数
invoke_kwds:传入的字典参数

2.3 设定数据库插件
修改myproject/setup.cfg中的内容为如下内容:

[metadata]
name = myproject
version = 1.0
summary = myproject
description-file =
    README.rst
author = me
author-email = 
classifier =
    Intended Audience :: Developers
    Programming Language :: Python :: 2.7

[global]
setup-hooks =
    pbr.hooks.setup_hook

[files]
packages =
    myproject
data_files =
    /etc/myproject = etc/myproject/*
    /var/www/myproject = etc/apache2/app.wsgi
    /etc/httpd/conf.d = etc/apache2/myproject.conf

[entry_points]
wsgi_scripts =
    myproject-api = myproject.api.app:build_wsgi_app

console_scripts =
    myproject-dbsync = myproject.cmd.storage:dbsync

oslo.config.opts =
    myproject = myproject.opts:list_opts

myproject.storage =
    mysql = myproject.db.mariadb.impl_mariadb:Connection
    mysql+pymysql = myproject.db.mariadb.impl_mariadb:Connection

分析:
1) 其中最重要的内容是在[entry_points]下面新增了:
myproject.storage =
    mysql = myproject.db.mariadb.impl_mariadb:Connection
    mysql+pymysql = myproject.db.mariadb.impl_mariadb:Connection
来表示myproject.storage的命名空间下，mysql或者mysql+pymysql这个插件名称对应的
处理对象是: myproject.db.mariadb.impl_mariadb下面的Connection类

2) 插件格式
命名空间 = 
  插件名称=模块:可导入对象

2.4 定义数据库模型
在db目录下新建models.py,具体内容如下:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from sqlalchemy import Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Index
from sqlalchemy import Integer
from sqlalchemy import PrimaryKeyConstraint
from sqlalchemy import String

Base = declarative_base()

class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer(), nullable=False)
    userId = Column(String(256), nullable=False)
    name = Column(String(256), nullable=False)
    age = Column(Integer(), nullable=True)
    email = Column(String(256), nullable=True)
    __table_args__ = (
        Index('ix_student_userId', 'userId'),
        PrimaryKeyConstraint('id')
    )

    TRANSFORMED_FIELDS = ['id', 'userId', 'name', 'age', 'email']
    # from database model to dict
    def as_dict(self):
        result = dict()
        for field in Student.TRANSFORMED_FIELDS:
            result[field] = getattr(self, field, '')
        return result

分析:
1) as_dict方法主要是用于将models.Student对象转换为字典

2.5 为请求绑定database hook, config hook
2.5.1 在myproject的api目录下新建hooks.py
具体内容如下:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from pecan import hooks

class ConfigHook(hooks.PecanHook):
    """Attach config information to the request
    
    """
    def __init__(self, conf):
        self.conf = conf

    def before(self, state):
        state.request.cfg = self.conf

class DatabaseHook(hooks.PecanHook):
    """Attach database information to the request
    
    """
    def __init__(self, conn):
        self.storage = conn

    def before(self, state):
        state.request.storage = self.storage

分析:
1) 这个hook在每个请求进来的时候实例化一个db的Connection对象，然后在controller代码中我们可以直接使用这个Connection实例
具体参考:
https://pecan.readthedocs.io/en/latest/hooks.html
hook的作用:
pecan hooks是一种可以与框架交互的方式，不需要编写单独的中间件。
Hooks允许你在请求生命周期的关键时间点去执行代码:

on_route(): called before Pecan attempts to route a request to a controller
before(): called after routing, but before controller code is run
after(): called after controller code has been run
on_error(): called when a request generates an exception

参考:
https://www.sqlalchemy.org/library.html#tutorials
https://segmentfault.com/a/1190000004466246

2)修改myproject的api目录下app.py
内容为如下内容:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import os

from oslo_config import cfg
from oslo_log import log
from paste import deploy
import pecan

from myproject.api import hooks
from myproject import service
from myproject import db

PECAN_CONFIG = {
    'app': {
        'root': 'myproject.api.controllers.root.RootController',
        'modules': ['myproject.api'],
    },
}

LOG = log.getLogger(__name__)

def app_fac