Can I mock sqlite3 CURRENT_TIMESTAMP in Python tests?-CodePudding

I want to return custom value for SQLite3 CURRENT_TIMESTAMP in my Python tests by mocking the return value (without interfering system clock).

I discovered this answer but it doesn't work for CURRENT_TIMESTAMP (apparently because it is a keyword and not a function). Any ideas how to get this working?

UPD. Tried to mock the DATETIME() function according to suggestion by @forpas, but looks like it is not working for CURRENT_TIMESTAMP (unlike calling DATETIME() directly):

def mock_date(*_):
    return '1975-02-14'

def mock_datetime(*_):
    return '1975-02-14 12:34:56'

connection = sqlite3.connect(':memory:')
print('Before DATE() mock, DATE(\'now\'): '   connection.execute('SELECT DATE(\'now\')').fetchone()[0])
connection.create_function('DATE', -1, mock_date)
print('After DATE() mock, DATE(\'now\'): '   connection.execute('SELECT DATE(\'now\')').fetchone()[0])
print('Before DATETIME() mock, CURRENT_TIMESTAMP: '   connection.execute('SELECT CURRENT_TIMESTAMP').fetchone()[0])
print('Before DATETIME() mock, DATETIME(\'now\'): '   connection.execute('SELECT DATETIME(\'now\')').fetchone()[0])
connection.create_function('DATETIME', -1, mock_datetime)
print('After DATETIME() mock, CURRENT_TIMESTAMP: '   connection.execute('SELECT CURRENT_TIMESTAMP').fetchone()[0])
print('After DATETIME() mock, DATETIME(\'now\'): '   connection.execute('SELECT DATETIME(\'now\')').fetchone()[0])
connection.create_function('CURRENT_TIMESTAMP', -1, mock_datetime)
print('After CURRENT_TIMESTAMP mock, CURRENT_TIMESTAMP: '   connection.execute('SELECT CURRENT_TIMESTAMP').fetchone()[0])

Here are the test results:

Before DATE() mock, DATE('now'): 2023-01-11
After DATE() mock, DATE('now'): 1975-02-14
Before DATETIME() mock, CURRENT_TIMESTAMP: 2023-01-11 21:03:40
Before DATETIME() mock, DATETIME('now'): 2023-01-11 21:03:40
After DATETIME() mock, CURRENT_TIMESTAMP: 2023-01-11 21:03:40
After DATETIME() mock, DATETIME('now'): 1975-02-14 12:34:56
After CURRENT_TIMESTAMP mock, CURRENT_TIMESTAMP: 2023-01-11 21:03:40

So after DATETIME() is mocked, DATETIME('now') result has changed but CURRENT_TIMESTAMP has not.

UPD2. Added test case with mocking CURRENT_TIMESTAMP itself.

The python version is 3.9.13 and sqlite3 version is 3.37.2. Test is performed in Windows environment.

CodePudding user response：

Don't ask me why, but when you do it that order (wether you comment the lines I've commented or not), it seems to work:

import sqlite3

# def mock_date(*_):
#     return '1975-01-01'

# def mock_time(*_):
#     return '00:00:00'

def mock_datetime(*_):
    return '1975-01-01 00:00:00'


with sqlite3.connect(':memory:') as con:
    
    # con.create_function('CURRENT_TIME', -1, mock_time)
    # print(con.execute('SELECT CURRENT_TIME').fetchone())
    
    # con.create_function('CURRENT_DATE', -1, mock_date)
    # print(con.execute('SELECT CURRENT_DATE').fetchone())
    
    con.create_function('CURRENT_TIMESTAMP', -1, mock_datetime)
    print(con.execute('SELECT CURRENT_TIMESTAMP').fetchone())

I get this result :

('1975-01-01 00:00:00',)

I honestly can't see what I did that you didn't. (Note that using your code, I get the exact same results as you . Must be something with the order of mocking functions?)

python 3.9.2 and sqlite3.__version__ = 3.34.0

EDIT :

If you don't get the same result as me, I'd advise to update sqlite3. There's a question about it here (which I haven't tested)

CodePudding user response：

I've found three ways to solve this issue. I can only explain the first one, so I would recommend using that over the other two:

Explicitly accept the correct number of arguments (none)
Create the functions twice [less recommended]
Avoid querying the functions before setting them [least recommended]

I used the following code to show how each method works:

import sqlite3
import argparse

CURRENT_KEYWORDS = (
    'CURRENT_TIME',
    'CURRENT_DATE',
    'CURRENT_TIMESTAMP',
)


def mocked(*_):
    return 'MOCKED'


def check(no_pre_query, narg):
    connection = sqlite3.connect(':memory:')
    select_stmt = "SELECT {}".format(",".join(CURRENT_KEYWORDS))
    print(f"Select statement: '{select_stmt}'; {no_pre_query=}, {narg=}")

    if no_pre_query:
        print('Skipping initial query')
    else:
        print('Before mock: {}'.format(connection.execute(select_stmt).fetchone()))

    for sql_kw in CURRENT_KEYWORDS:
        connection.create_function(sql_kw, narg, mocked)
    print('After mock: {}'.format(connection.execute(select_stmt).fetchone()))

    for sql_kw in CURRENT_KEYWORDS:
        connection.create_function(sql_kw, narg, mocked)
    print('Second attempt after mock: {}'.format(connection.execute(select_stmt).fetchone()))


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--no-pre-query", action="store_true", default=False)
    parser.add_argument("--narg", type=int, default=-1)
    args = parser.parse_args()
    check(args.no_pre_query, args.narg)


if __name__ == "__main__":
    main()

Recommended method: explicit arg count

When calling connection.create_function CPython calls sqlite's sqlite3_create_function_v2. From sqlite's documentation:

It is permitted to register multiple implementations of the same functions with the same name but with either differing numbers of arguments or differing preferred text encodings. SQLite will use the implementation that most closely matches the way in which the SQL function is used. A function implementation with a non-negative nArg parameter is a better match than a function implementation with a negative nArg. A function where the preferred text encoding matches the database encoding is a better match than a function where the encoding is different. A function where the encoding difference is between UTF16le and UTF16be is a closer match than a function where the encoding difference is between UTF8 and UTF16.

A non-negative (incl. zero) nArg is a better match than a negative one, so setting nArg to zero resolves the issue:

$ python /tmp/sql.py --narg=0
Select statement: 'SELECT CURRENT_TIME,CURRENT_DATE,CURRENT_TIMESTAMP'; no_pre_query=False, narg=0
Before mock: ('19:22:53', '2023-01-13', '2023-01-13 19:22:53')
After mock: ('MOCKED', 'MOCKED', 'MOCKED')
Second attempt after mock: ('MOCKED', 'MOCKED', 'MOCKED')

Following are the less-recommended (or.. highly discouraged) methods, but both are worth mentioning in case someone else encounters such symptoms:

Unrecommended method #2 - create_function twice

I might be wrong (which is why I don't recommend this method), but as overloading functions is possible, it looks like defining the same function twice will make it take precedence over a zero-nArg option:

$ python /tmp/sql.py
Select statement: 'SELECT CURRENT_TIME,CURRENT_DATE,CURRENT_TIMESTAMP'; no_pre_query=False, narg=-1
Before mock: ('19:30:18', '2023-01-13', '2023-01-13 19:30:18')
After mock: ('19:30:18', '2023-01-13', '2023-01-13 19:30:18')
Second attempt after mock: ('MOCKED', 'MOCKED', 'MOCKED')

This might be a result of specific implementation details, and as such (as well as being undocumented) it might change without notice. Still, I figured it might be worth noting in case setting nArg to zero is not an option, for some reason.

Unrecommended method #3 - avoid queries before create_function

This is quite a strange behavior, which not only is not documented, but also cannot be sanely attributed to an overloading mechanism. For these reasons I strongly discourage its use, but I still think it's worth mentioning, for posterity.
When the CURRENT_%s are not queried before setting the overriding function, it appears as if create_function works as expected, even if the provided nArg is negative. This should not be the case, but it is (at least on my setup), and as it might explain why in some code-flows it might "work" but not in others, I think this too is worth mentioning:

$ python /tmp/sql.py --no-pre-query
Select statement: 'SELECT CURRENT_TIME,CURRENT_DATE,CURRENT_TIMESTAMP'; no_pre_query=True, narg=-1
Skipping initial query
After mock: ('MOCKED', 'MOCKED', 'MOCKED')
Second attempt after mock: ('MOCKED', 'MOCKED', 'MOCKED')

As specific versions came to question, it is worth noting that the problem and suggested solutions were all successfully reproduced on MacOS Monterey, Python 3.9.6, sqlite3.version 2.6.0, and sqlite3.sqlite_version 3.37.0.
The provided code results in the same output, and by simply changing -1 to 0 like so:

connection.create_function('CURRENT_TIMESTAMP', 0, mock_datetime)

The provided example will print what appears to be the desired result (regardless of DATETIME()):

Before DATE() mock, DATE('now'): 2023-01-13
After DATE() mock, DATE('now'): 1975-02-14
Before DATETIME() mock, CURRENT_TIMESTAMP: 2023-01-13 20:05:54
Before DATETIME() mock, DATETIME('now'): 2023-01-13 20:05:54
After DATETIME() mock, CURRENT_TIMESTAMP: 2023-01-13 20:05:54
After DATETIME() mock, DATETIME('now'): 1975-02-14 12:34:56
After CURRENT_TIMESTAMP mock, CURRENT_TIMESTAMP: 1975-02-14 12:34:56