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
