Pythonic way to mock the pyodbc.Row

Question

What is the pythonic way to do the proper unittesting of a function that depends on the SQL query made by pyodbc? As I understand the best way is to mock the function that returns output from SQL server. The problem is what the mock should return?

My setup: In lib1:

def selectSQL(connection, query):
    cursor = connection.cursor()
    cursor.execute(query)
    return cursor.fetchall()

In lib2:

def function_to_be_tested(cxnx):
    my_query = "SELECT r1, r2 FROM t1"
    rows = lib1.selectSQL(cxnx, my_query)
    # do someting with the rows like:
    a = 0
    for row in rows
        a += row.r1 * row.r2
    return a

I have came with the following sollution:

• Print the lib1.selectSQL(cxnx, my_query) to a file
• Insert the data from lib1.selectSQL to the namedtuple

,

out_tuple   = namedtuple('out1', ["r1", "r2"])
printed_data = [(1,2),(2,3)]
out = [out_tuple(*row) for row in printed_data]

def test_mockSelectSQL(self):
    piotrSQL.selectSQL = MagicMock()
    piotrSQL.selectSQL.side_effect = [out]
    self.assertEqual(lib2.function_to_be_tested(True), 7)

My only concern is that the mock returns namedtuple not the pyodbc.Row like the original function. I have checked following sites in search for the information on how to properly create pyodbc.Row:

• https://github.com/mkleehammer/pyodbc/blob/master/tests2/informixtests.py
• https://github.com/mkleehammer/pyodbc/wiki/Row

In the unittest of pyodbc there is no constructor of if - neither have I found it in the source code (but I am novice so I might have omitted it)... However I have found following information on the Row documentation:

However, there are some pyodbc additions that make them very convenient:

• Values can be accessed by column name.

• The Cursor.description values can be accessed even after the cursor is closed.

• Values can be replaced.

• Rows from the same select statement share memory.

So it seams that the namedtuple is in fact behaving in the same way as pyodbc.Row (when it comes to accessing the values). Is there a more pythonic way to do a unittest on pyodbc.Row? Can one assume that this is a good Mock?

Answer 1

Further to the suggestion from @Nullman in a comment to the question, if you wanted to use an in-memory database you might try using the SQLite ODBC driver so you can return actual pyodbc.Row objects like so:

import pyodbc
conn_str = 'Driver=SQLite3 ODBC Driver;Database=:memory:'
cnxn = pyodbc.connect(conn_str, autocommit=True)
crsr = cnxn.cursor()

# create test data
crsr.execute("CREATE TABLE table1 (id INTEGER PRIMARY KEY, dtm DATETIME)")
crsr.execute("INSERT INTO table1 (dtm) VALUES ('2017-07-26 08:08:08')")

# test retrieval
crsr.execute("SELECT * FROM table1")
print(crsr.fetchall())
# prints:
# [(1, datetime.datetime(2017, 7, 26, 8, 8, 8))]

crsr.close()
cnxn.close()

I just tested it and it worked for me in PyCharm on Windows.