sqlalchemy 4 Core测试

app的测试大都包括 两种：单元测试 和 功能测试。

做测试需要的文件分为三部分：待测试的应用程序app.py，测试(用)数据库和连接db.py，测试代码test_app.py

---

测试数据库db.py，进行 引擎、库、表的定义和预初始化，在test_app.py里正式初始化。db.py

from datetime import datetime
from sqlalchemy import Table, Column, Integer, Numeric, String, ForeignKey, Boolean
from sqlalchemy import CheckConstraint, DateTime, create_engine, MetaData
class DataAccessLayer:
    connection = None
    engine = None
    conn_string = None
    metadata = MetaData()    #初始化metadata
    cookies = Table('cookies', metadata,
        Column('cookie_id', Integer(), primary_key=True),
        Column('cookie_name', String(50), index=True),
        Column('cookie_recipe_url', String(225)),
        Column('cookie_sku', String(55)),
        Column('quantity', Integer()),
        Column('unit_cost', Numeric(12,2)),  #长度12、精度2位小数
        CheckConstraint('quantity > 0', name='quantity_positive')
        )
    users = Table('users', metadata,
        Column('user_id', Integer(), primary_key=True),
        Column('username', String(15), nullable=False, unique=True),
        Column('email_address', String(255), nullable=False),
        Column('phone', String(20), nullable=False),
        Column('password', String(25), nullable=False),
        Column('created_on', DateTime(), default=datetime.now),
        Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now),
        )
    orders = Table('orders', metadata,
        Column('order_id', Integer(), primary_key=True),
        Column('user_id', ForeignKey('users.user_id')),
        Column('shipped', Boolean(), default=False),
        )
    line_items = Table('line_items', metadata,
        Column('line_items_id', Integer(), primary_key=True),
        Column('order_id', ForeignKey('orders.order_id')),
        Column('cookie_id', ForeignKey('cookies.cookie_id')),
        Column('quantity', Integer()),
        Column('extended_cost', Numeric(12,2)),
        )
    def db_init(self, conn_string):
        self.engine = create_engine(conn_string or self.conn_string)    #创建引擎
        self.metadata.create_all(self.engine)    #表持久化
        self.connection = self.engine.connect()    #连接

dal = DataAccessLayer()

待测试的应用程序app.py。 注意表都是从dal里引用的，开头要加dal.

from c4_db import dal
from sqlalchemy.sql import select

def get_orders_by_customer(cust_name, shipped=None, details=True):
    columns =[dal.orders.c.order_id, dal.users.c.username, dal.users.c.phone]
    joins = dal.users.join(dal.orders)
    if details:
        columns.extend([dal.cookies.c.cookie_name,
                        dal.line_items.c.quantity,
                        dal.line_items.c.extended_cost])    #extended_cost会导致Decimal的输出，导致测试错误，建议去掉
        joins = joins.join(dal.line_items).join(dal.cookies)

    cust_orders = select(columns)
    cust_orders = cust_orders.select_from(joins).where(dal.users.c.username == cust_name)
    if shipped is not None:
        cust_orders = cust_orders.where(dal.orders.c.shipped == shipped)
    result = dal.connection.execute(cust_orders).fetchall()    #fetchall以后就是列表
    return result    #这一步不要忘

测试 要针对应用程序 进行设计。要测试get_orders_by_customer(cust_name, shipped=None, details=True)，就要考虑所有情况。cust_name有三种：空、有效客户名、无效客户名；shipped有None和非None；details有True和False。若测试所有组合，则需要3*3*2=18个测试。test_app.py

import unittest
from c4_db import dal
from sqlalchemy import select, insert, update
from c4_app import get_orders_by_customer

class TestApp(unittest.TestCase):
    
    @classmethod
    def setUpClass(cls):
        dal.db_init('sqlite:///:memory:')    #环境初始化
        #以下都是插入数据
        ins = insert(dal.users).values(
            username='cookiemon',
            email_address='mon@cookie.com',
            phone='111-111-1111',
            password='password',
            )
        result = dal.connection.execute(ins)
        ins = dal.cookies.insert()
        inventory_list = [
            {
                'cookie_name': 'chocolate chip',
                'cookie_recipe_url': 'http://some.aweso.me/cookie/recipe.html',
                'cookie_sku': 'cc01',
                'quantity':'12',
                'unit_cost':'0.50',
            },
            {
                'cookie_name': 'dark chocolate chip',
                'cookie_recipe_url': 'http://some.aweso.me/cookie/recipe_dark.html',
                'cookie_sku': 'cc02',
                'quantity':'1',
                'unit_cost':'0.75', 
            }
        ]
        result = dal.connection.execute(ins, inventory_list)
        #插入两份订单数据
        ins = insert(dal.orders).values(user_id=1, order_id='1')
        result = dal.connection.execute(ins)
        ins = insert(dal.line_items)
        order_items = [
            {
                'order_id': 1,
                'cookie_id': 1,
                'quantity': 9,
                'extended_cost': 4.50,
            },
        ]
        result = dal.connection.execute(ins, order_items)
    #以下6个测试是 无 用户名的情况
    def test_orders_by_customer_blank(self):    #unittest要求每个测试都以test开头
        results = get_orders_by_customer('')
        self.assertEqual(results, [])

    def test_orders_by_customer_blank_shipped(self):
        results = get_orders_by_customer('', True)
        self.assertEqual(results, [])

    def test_orders_by_customer_blank_notshipped(self):
        results = get_orders_by_customer('', False)
        self.assertEqual(results, [])

    def test_orders_by_customer_blank_details(self):
        results = get_orders_by_customer('', details=True)
        self.assertEqual(results, [])

    def test_orders_by_customer_blank_shipped_details(self):
        results = get_orders_by_customer('', True, True)
        self.assertEqual(results, [])

    def test_orders_by_customer_blank_notshipped_details(self):
        results = get_orders_by_customer('', False, True)
        self.assertEqual(results, [])

 执行测试要 在命令行cmd，激活虚拟环境venv\scripts\activate

(venv) $ python -m unittest test_app

D:\alchemy\venv\lib\site-packages\sqlalchemy\sql\sqltypes.py:732: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
  "storage." % (dialect.name, dialect.driver)
......
----------------------------------------------------------------------
Ran 6 tests in 0.055s

OK

在之前的代码执行里也会出现这个警告。是有关SQlite和decimal类型的警告，SQLite中没有真正的decimal类型，SQLAlchemy希望开发者知道 从SQLite的float类型转换而来的值可能有点奇怪。忽略这个警告就好。

---

在对 有效用户名 进行测试前 修改一下代码：插入数据 这段 这么长的代码放在setUpClass()里不太合适，把他放到工厂函数所在的db.py        （真实情况下通常放到test.py或者app.py）

from sqlalchemy import select, insert, update

class DataAccessLayer:
    #...

def prep_db():
    #insert users
    customer_list = [
        {
            'username':'cookiemon',
            'email_address':'mon@cookie.com',
            'phone':'111-111-1111',
            'password':'password',
        },
        {
            'username':'cakeeater',
            'email_address':'cakeeater@cake.com',
            'phone':'222-222-2222',
            'password':'password',
        },
        {
            'username':'pieguy',
            'email_address':'guy@pie.com',
            'phone':'333-333-3333',
            'password':'password',
        },
    ]
    ins = dal.users.insert()
    dal.connection.execute(ins, customer_list)
    #insert cookies
    ins = dal.cookies.insert()
    #第一种插入方法
    dal.connection.execute(ins, cookie_name = 'dark chocolate chip',
        cookie_recipe_url = 'http://some.aweso.me/cookie/recipe_dark.html',
        cookie_sku = 'cc02',
        quantity='1',
        unit_cost='0.75',)
    #第二种插入方法
    inventory_list = [
        {
            'cookie_name': 'peanut butter',
            'cookie_recipe_url': 'http://some.aweso.me/cookie/peanut.html',
            'cookie_sku': 'pb01',
            'quantity':'24',
            'unit_cost':'0.25',
        },
        {
            'cookie_name': 'oatmeal raisin',
            'cookie_recipe_url': 'http://some.aweso.me/cookie/raisin.html',
            'cookie_sku': 'eww01',
            'quantity':'100',
            'unit_cost':'1.00', 
        }
    ]
    result = dal.connection.execute(ins, inventory_list)
    #orders 1
    ins = insert(dal.orders).values(user_id=1, order_id='wlk001')    #order_id的类型要改一下，不然报错
    dal.connection.execute(ins)
    ins = insert(dal.line_items)
    order_items = [
        {
            'order_id': 'wlk001',
            'cookie_id': 1,
            'quantity': 2,
            'extended_cost': 1.00,
        },
        {
            'order_id': 'wlk001',
            'cookie_id': 3,
            'quantity': 12,
            'extended_cost': 3.00,
        },
    ]
    dal.connection.execute(ins, order_items)
    #orders 2
    ins = insert(dal.orders).values(user_id=2, order_id='ol001')
    dal.connection.execute(ins)
    ins = insert(dal.line_items)
    order_items = [
        {
            'order_id': 'ol001',
            'cookie_id': 1,
            'quantity': 24,
            'extended_cost': 12.00,
        },
        {
            'order_id': 'ol001',
            'cookie_id': 4,
            'quantity': 6,
            'extended_cost': 6.00,
        },
    ]
    dal.connection.execute(ins, order_items)

setUpClass()里直接引用prep_db()，干净整洁。test_app.py

from c4_db import dal, prep_db
class TestApp(unittest.TestCase):
    
    @classmethod
    def setUpClass(cls):
        dal.db_init('sqlite:///:memory:')
        prep_db()

    #vain
    #....
    #valid
    def test_orders_by_validcustomer_blank(self):
        results = get_orders_by_customer('cookiemon')
        self.assertEqual(results, [('wlk001', 'cookiemon', '111-111-1111')])

    def test_orders_by_validcustomer_blank_shipped(self):
        results = get_orders_by_customer('cookiemon', True)
        self.assertEqual(results, [])

    def test_orders_by_validcustomer_blank_notshipped(self):
        results = get_orders_by_customer('cookiemon', False)
        self.assertEqual(results, [('wlk001', 'cookiemon', '111-111-1111')])

    def test_orders_by_validcustomer_blank_details(self):
        expected_results = [('wlk001', 'cookiemon', '111-111-1111', 
                        'dark chocolate chip', 2), 
                        ('wlk001', 'cookiemon', '111-111-1111', 'oatmeal raisin', 12)]
        results = get_orders_by_customer('cookiemon', details=True)
        self.assertEqual(results, expected_results)

    def test_orders_by_validcustomer_blank_shipped_details(self):
        results = get_orders_by_customer('cookiemon', True, True)
        self.assertEqual(results, [])

    def test_orders_by_validcustomer_blank_notshipped_details(self):
        expected_results = [('wlk001', 'cookiemon', '111-111-1111', 
                        'dark chocolate chip', 2), 
                        ('wlk001', 'cookiemon', '111-111-1111', 'oatmeal raisin', 12)]    #这里把extended_cost去掉了，有个Decimal在很麻烦
        results = get_orders_by_customer('cookiemon', False, True)
        self.assertEqual(results, expected_results)
    #invalid
    def test_orders_by_invalidcustomer_blank(self):
        results = get_orders_by_customer('aaa')
        self.assertEqual(results, [])

    def test_orders_by_invalidcustomer_blank_shipped(self):
        results = get_orders_by_customer('aaa', True)
        self.assertEqual(results, [])

    def test_orders_by_invalidcustomer_blank_notshipped(self):
        results = get_orders_by_customer('aaa', False)
        self.assertEqual(results, [])

    def test_orders_by_invalidcustomer_blank_details(self):
        results = get_orders_by_customer('aaa', details=True)
        self.assertEqual(results, [])

    def test_orders_by_invalidcustomer_blank_shipped_details(self):
        results = get_orders_by_customer('aaa', True, True)
        self.assertEqual(results, [])

    def test_orders_by_invalidcustomer_blank_notshipped_details(self):
        results = get_orders_by_customer('aaa', False, True)
        self.assertEqual(results, [])

---

当创建 测试数据库 没意义或不可行时，使用mock。