Python SQLAlchemy 中查询的单元测试
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14719507/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Unit tests for Query in SQLAlchemy
提问by gotgenes
How does one go about testing queries in SQLAlchemy? For example suppose we have this models.py
如何在 SQLAlchemy 中测试查询？例如假设我们有这个models.py
from sqlalchemy import (
Column,
Integer,
String,
)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Panel(Base):
__tablename__ = 'Panels'
id = Column(Integer, primary_key=True)
category = Column(Integer, nullable=False)
platform = Column(String, nullable=False)
region = Column(String, nullable=False)
def __init__(self, category, platform, region):
self.category = category
self.platform = platform
self.region = region
def __repr__(self):
return (
"<Panel('{self.category}', '{self.platform}', "
"'{self.region}')>".format(self=self)
)
and this tests.py
和这个 tests.py
import unittest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import Base, Panel
class TestQuery(unittest.TestCase):
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
session = Session()
def setUp(self):
Base.metadata.create_all(self.engine)
self.session.add(Panel(1, 'ion torrent', 'start'))
self.session.commit()
def tearDown(self):
Base.metadata.drop_all(self.engine)
def test_query_panel(self):
expected = [Panel(1, 'ion torrent', 'start')]
result = self.session.query(Panel).all()
self.assertEqual(result, expected)
When we try running the test, it fails, even though the two Panels look identical.
当我们尝试运行测试时，它失败了，即使两个面板看起来相同。
$ nosetests
F
======================================================================
FAIL: test_query_panel (tests.TestQuery)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/Users/clasher/tmp/tests.py", line 31, in test_query_panel
self.assertEqual(result, expected)
AssertionError: Lists differ: [<Panel('1', 'ion torrent', 's... != [<Panel('1', 'ion torrent', 's...
First differing element 0:
<Panel('1', 'ion torrent', 'start')>
<Panel('1', 'ion torrent', 'start')>
[<Panel('1', 'ion torrent', 'start')>, <Panel('2', 'ion torrent', 'end')>]
----------------------------------------------------------------------
Ran 1 test in 0.063s
FAILED (failures=1)
One solution I've found is to make a query for every single instance I expect to find in the query:
我发现的一种解决方案是对我希望在查询中找到的每个实例进行查询：
class TestQuery(unittest.TestCase):
...
def test_query_panel(self):
expected = [
(1, 'ion torrent', 'start'),
(2, 'ion torrent', 'end')
]
successful = True
# Check to make sure every expected item is in the query
try:
for category, platform, region in expected:
self.session.query(Panel).filter_by(
category=category, platform=platform,
region=region).one()
except (NoResultFound, MultipleResultsFound):
successful = False
self.assertTrue(successful)
# Check to make sure no unexpected items are in the query
self.assertEqual(self.session.query(Panel).count(),
len(expected))
This strikes me as pretty ugly, though, and I'm not even getting to the point where I have a complex filtered query that I'm trying to test. Is there a more elegant solution, or do I always have to manually make a bunch of individual queries?
不过，这让我觉得非常难看，而且我什至还没有达到要测试的复杂过滤查询的地步。有没有更优雅的解决方案，还是我总是必须手动进行一堆单独的查询？
采纳答案by zzzeek
your original test is on the right track, you just have to do one of two things: either make sure that two Panelobjects of the same primary key identity compare as True:
您的原始测试在正确的轨道上，您只需要做两件事之一：要么确保Panel具有相同主键标识的两个对象比较为True：
class Panel(Base):
# ...
def __eq__(self, other):
return isinstance(other, Panel) and other.id == self.id
or you can organize your test such that you make sure you're checking against the same Panelinstance (because here we take advantage of the identity map):
或者您可以组织您的测试，以确保您检查的是同一个Panel实例（因为在这里我们利用了身份映射）：
class TestQuery(unittest.TestCase):
def setUp(self):
self.engine = create_engine('sqlite:///:memory:')
self.session = Session(engine)
Base.metadata.create_all(self.engine)
self.panel = Panel(1, 'ion torrent', 'start')
self.session.add(self.panel)
self.session.commit()
def tearDown(self):
Base.metadata.drop_all(self.engine)
def test_query_panel(self):
expected = [self.panel]
result = self.session.query(Panel).all()
self.assertEqual(result, expected)
as far as the engine/session setup/teardown, I'd go for a pattern where you use a single engine for all tests, and assuming your schema is fixed, a single schema for all tests, then you make sure the data you work with is performed within a transaction that can be rolled back. The Sessioncan be made to work this way, such that calling commit()doesn't actually commit the "real" transaction, by wrapping the whole test within an explicit Transaction. The example at https://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#joining-a-session-into-an-external-transaction-such-as-for-test-suitesillustrates this usage. Having a ":memory:" engine on every test fixture will take up a lot of memory and not really scale out to other databases besides SQLite.
就引擎/会话设置/拆卸而言，我会采用一种模式，在这种模式下，您对所有测试使用单个引擎，并假设您的架构是固定的，所有测试都使用单一架构，然后您确保数据工作with 在可以回滚的事务中执行。该Session可制成这样的工作方式，从而调用commit()实际上并没有提交“真正”的交易，通过包装内的明确整个测试Transaction。在该示例https://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites示出了这种使用情况。在每个测试装置上使用 ":memory:" 引擎将占用大量内存，并且不会真正扩展到 SQLite 之外的其他数据库。
