How can I compare two SQLAlchemy queries if they are the same?

user_10650942 0
Viewed 2305

I have a function returning SQLAlchemy query object and I want to test this function that it builds correct query.

For example:

import sqlalchemy

metadata = sqlalchemy.MetaData()
users = sqlalchemy.Table(
    "users",
    metadata,
    sqlalchemy.Column("email", sqlalchemy.String(255), nullable=False, unique=True),
    sqlalchemy.Column("username", sqlalchemy.String(50), nullable=False, unique=True),
)

def select_first_users(n):
    return users.select().limit(n)

def test_queries_are_equal(self):
    expected_query = users.select().limit(10)
    assert select_first_users(10) == expected_query  # fails here
    assert select_first_users(10).compare(expected_query)  # fails here too

I have no idea how to compare two queries for equality. == doesn't work here because as far as I can see these objects do not have the __eq__ method defined, so it compares objects by address in memory and surely fails. The compare method also does is comparison.

The only solution I see is like:

assert str(q1.compile()) == str(q2.compile())

, but it is strange and contains placeholders instead of actual values.

So how can I compare two SQLAlchemy queries for equality?

I use Python 3.7.4, SQLAlchemy==1.3.10.

sqlalchemy unit-testing python
1 Answers

There is a parameter to the compile function that solves the placeholder problem query.compile(compile_kwargs={"literal_binds": True}), so instead of

SELECT users.email,
       users.username
FROM users
LIMIT :param_1

you get

SELECT users.email,
       users.username
FROM users
LIMIT 10

So I think you could do something like that

import sqlparse

def format_query(query):
    return sqlparse.format(str(query.compile(compile_kwargs={"literal_binds": True})), 
                           reindent=True, keyword_case='upper')


def test_queries_are_equal():
    expected_query = users.select().limit(10)
    assert format_query(expected_query) == format_query(select_first_users(10))

If comparison is based on an exact string match, I think it's better to ensure a consistent formatting, hence the use of sqlparse.

Natively it handles only int and strings but it can be extended, see the doc for more info https://docs.sqlalchemy.org/en/13/faq/sqlexpressions.html#faq-sql-expression-string

user_40459640
Related