英文:
Can I mock sqlite3 CURRENT_TIMESTAMP in Python tests?
问题
我想在我的Python测试中为SQLite3的CURRENT_TIMESTAMP返回自定义值，而不干扰系统时钟的情况下模拟返回值。
我发现了[这个答案][1]，但对于CURRENT_TIMESTAMP它似乎不起作用（显然因为它是一个关键字而不是一个函数）。有没有办法让它工作起来？
**更新.** 尝试根据[@forpas][2]的建议模拟DATETIME()函数，但看起来对于CURRENT_TIMESTAMP来说不起作用（不像直接调用DATETIME()）：
def mock_date(*_):
return '1975-02-14'
def mock_datetime(*_):
return '1975-02-14 12:34:56'
connection = sqlite3.connect(':memory:')
print('在DATE()模拟之前，DATE(\'now\'): ' + connection.execute('SELECT DATE(\'now\')').fetchone()[0])
connection.create_function('DATE', -1, mock_date)
print('在DATE()模拟之后，DATE(\'now\'): ' + connection.execute('SELECT DATE(\'now\')').fetchone()[0])
print('在DATETIME()模拟之前，CURRENT_TIMESTAMP: ' + connection.execute('SELECT CURRENT_TIMESTAMP').fetchone()[0])
print('在DATETIME()模拟之前，DATETIME(\'now\'): ' + connection.execute('SELECT DATETIME(\'now\')').fetchone()[0])
connection.create_function('DATETIME', -1, mock_datetime)
print('在DATETIME()模拟之后，CURRENT_TIMESTAMP: ' + connection.execute('SELECT CURRENT_TIMESTAMP').fetchone()[0])
print('在DATETIME()模拟之后，DATETIME(\'now\'): ' + connection.execute('SELECT DATETIME(\'now\')').fetchone()[0])
connection.create_function('CURRENT_TIMESTAMP', -1, mock_datetime)
print('在CURRENT_TIMESTAMP模拟之后，CURRENT_TIMESTAMP: ' + connection.execute('SELECT CURRENT_TIMESTAMP').fetchone()[0])
以下是测试结果：
在DATE()模拟之前，DATE('now'): 2023-01-11
在DATE()模拟之后，DATE('now'): 1975-02-14
在DATETIME()模拟之前，CURRENT_TIMESTAMP: 2023-01-11 21:03:40
在DATETIME()模拟之前，DATETIME('now'): 2023-01-11 21:03:40
在DATETIME()模拟之后，CURRENT_TIMESTAMP: 2023-01-11 21:03:40
在DATETIME()模拟之后，DATETIME('now'): 1975-02-14 12:34:56
在CURRENT_TIMESTAMP模拟之后，CURRENT_TIMESTAMP: 2023-01-11 21:03:40
因此，在模拟了`DATETIME()`之后，`DATETIME('now')`的结果发生了变化，但`CURRENT_TIMESTAMP`没有变化。
**更新2.** 添加了一个模拟CURRENT_TIMESTAMP本身的测试用例。
Python版本是3.9.13，sqlite3版本是3.37.2。测试在Windows环境中进行。
[1]: https://stackoverflow.com/questions/27499411/sqlite-can-i-mock-the-current-time-now-for-testing
[2]: https://stackoverflow.com/users/10498828/forpas
英文:
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.
答案1
得分: 2
我已找到三种解决这个问题的方法。我只能解释第一种，所以我建议使用第一种方法而不是其他两种：
- 明确接受正确数量的参数（无）
- 两次创建函数（较不推荐）
- 在设置函数之前避免查询函数（最不推荐）
我使用以下代码来展示每种方法的工作原理：
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("Select statement: '{}'; no_pre_query={}, narg={}".format(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()
推荐的方法：明确参数数量
在调用 connection.create_function 时，CPython 调用了 sqlite 的 sqlite3_create_function_v2。根据 sqlite 的文档：
允许注册同名但参数数量或首选文本编码不同的多个函数实现。SQLite 将使用最符合 SQL 函数使用方式的实现。具有非负 nArg 参数的函数实现比具有负 nArg 参数的函数实现更匹配。首选文本编码与数据库编码匹配的函数实现比编码不同的函数实现更匹配。首选文本编码之间的编码差异是 UTF16le 和 UTF16be 之间的编码差异，比 UTF8 和 UTF16 之间的编码差异更接近。
非负（包括零）的 nArg 比负数的 nArg 更匹配，因此将 nArg 设置为零可以解决这个问题：
$ 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')
以下是较不推荐（或者说极不推荐）的方法，但两者都值得一提，以防有人遇到类似的情况：
不推荐的方法 #2 - 两次创建函数
我可能错了（这就是为什么我不推荐这种方法的原因），但由于函数重载是可能的，定义相同的函数两次似乎会使其优先于零 nArg 选项：
$ 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')
这可能是特定实现细节的结果，因此可能会在不通知的情况下发生变化。尽管如此，我认为在某些情况下无法选择将 nArg 设置为零，这也值得一提。
不推荐的方法 #3 - 避免在创建函数之前查询函数
这是一种相当奇怪的行为，不仅没有记录，而且无法合理地归因于重载机制。出于这些原因，我强烈不建议使用它，但我仍然认为有必要提一下，以供后人参考。
当在设置覆盖函数之前不查询 CURRENT_%s 函数时，似乎 create_function 会按预期工作，即使提供的 nArg 是负数。这不应该是这样的，但是（至少在我的设置中是如此），因为它可能解释了为什么在某些代码流程中它可能“工作”，但在其他代码流程中不“工作”，所以我认为这也值得一提：
$ 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')
由于涉及到特定版本，值得注意的是，问题和建议的解决方法都成功地在 MacOS Monterey、Python 3.9.6、sqlite3.version 2.6.0 和 sqlite3.sqlite_version 3.37.0 上复现。只需将 -1 更改为 0，如下所示：
connection.create_function('CURRENT_TIMESTAMP', 0, mock_datetime)
提供的示例将打印似乎是期望的结果（不管 DATETIME() 如何）：
Before DATE() mock, DATE('now'): 2023-01-13
After DATE() mock, DATE('now'): 1975-
<details>
<summary>英文:</summary>
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:
1. Explicitly accept the correct number of arguments (none)
2. Create the functions twice [less recommended]
3. 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][1]:
>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
[1]: https://www.sqlite.org/c3ref/create_function.html
</details>
# 答案2
**得分**: 0
以下是翻译好的部分：
```python
不要问我为什么，但是当您按照这个顺序执行（无论您是否注释掉我已经注释的行），似乎会起作用：
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())
我得到这个结果：
('1975-01-01 00:00:00',)
老实说，我看不出我做了什么你没有做的事情。（请注意，使用您的代码，我得到与您完全相同的结果。必须是模拟函数的顺序有问题吗？）
*python 3.9.2 和 `sqlite3.__version__ = 3.34.0`*
**编辑：**
如果您得到的结果不同于我，我建议您升级 sqlite3。关于此问题有一个问题[在这里](https://stackoverflow.com/questions/61091438/how-to-upgrade-sqlite3-version-in-windows-10)（我没有测试过）。
英文:
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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论