Assert contents of DB tables

[sumau]

Hello

I would like to assert the contents of tables in my PG schema i.e. make sure it contains the data I'm expecting

I am aware of various options:

1) Compare the actual and expected tables using a sql query, orchestrated by sqlalchemy (i.e. create the actual and expected tables in DB, run the sql comparison script, return the output)

2) Load the actual tables as tuples and compare them with expected tuples using something like assert_result

https://github.com/sqlalchemy/sqlalchemy/blob/d933ddd503a1ca0a7c562c51c503139c541e707e/lib/sqlalchemy/testing/assertions.py#L465

3) Load the actual tables as dataframes and compare them with expected dataframes using pandas assert_frame_equal

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.testing.assert_frame_equal.html

Any recommendations / thoughts would be much appreciated, both as to the approach and the implementation :-)

[sumau]

Hello

I think my original question was too generic so rephrasing... Is there a way in sqlalchemy to:

1. Assert a ResultProxy against an expected ResultProxy (or list of RowProxies against expected list of RowProxies) 
2. Show any differences

I wanted to check first before writing my own script :-)

Regards

S

[Mike Bayer]

On Fri, Nov 29, 2019, at 9:13 AM, sumau wrote:

Hello

I think my original question was too generic so rephrasing... Is there a way in sqlalchemy to:

1. Assert a ResultProxy against an expected ResultProxy (or list of RowProxies against expected list of RowProxies) 
   
2. Show any differences
   

I wanted to check first before writing my own script :-)

you fetch the rows into a list and compare:

assert result.fetchall() == otherresult.fetchall()

that's pretty much it, obviously does not scale for thousands or more of rows very well.

Regards

S

On Friday, 22 November 2019 10:50:54 UTC, sumau wrote:

Hello

I would like to assert the contents of tables in my PG schema i.e. make sure it contains the data I'm expecting

I am aware of various options:

1) Compare the actual and expected tables using a sql query, orchestrated by sqlalchemy (i.e. create the actual and expected tables in DB, run the sql comparison script, return the output)

2) Load the actual tables as tuples and compare them with expected tuples using something like assert_result

https://github.com/sqlalchemy/sqlalchemy/blob/d933ddd503a1ca0a7c562c51c503139c541e707e/lib/sqlalchemy/testing/assertions.py#L465

3) Load the actual tables as dataframes and compare them with expected dataframes using pandas assert_frame_equal

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.testing.assert_frame_equal.html

Any recommendations / thoughts would be much appreciated, both as to the approach and the implementation :-)

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/0baf6b7a-69e7-4dcf-a76a-813ff7583ac0%40googlegroups.com.

[Steven James]

In case you want more details about the differences, you could also use difflib...

from difflib import SequenceMatcher

seq1 = [tuple(row.values()) for row in resultproxy1]

seq2 = [tuple(row.values()) for row in resultproxy2]

sm = SequenceMatcher(a=seq1, b=seq2, autojunk=False)

print(sm.get_opcodes())

print(f'similarity: {sm.ratio()}')

assert sm.ratio() == 1  # example to ensure results are equivalent

assert sm.ratio() == 1, sm.get_opcodes()  # pytest syntax to show the opcodes if the assertion fails

Steven James

[sumau]

Thanks! And to show the differences more clearly:

for tag, i1, i2, j1, j2 in sm.get_opcodes():
    if tag == 'replace':
        for i in range(i1,i2):
            print (seq1[i])
            print (seq2[i])

Soumaya