python pymysql peewee關(guān)于時(shí)區(qū)問題分析

更新時(shí)間：2023年06月19日 10:05:44 作者：ponponon

這篇文章主要為大家介紹了python pymysql peewee關(guān)于時(shí)區(qū)問題分析，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

正文

研究的問題：如果我插入一個(gè) datetime 的 tzinfo 北京時(shí)區(qū)的，peewee insert 的時(shí)候，會(huì)幫我轉(zhuǎn)成 utc 再插入嗎？

答案：不會(huì)

研究過程

我們通過

peewee 返回對(duì)的 sql
peewee 內(nèi)部的 sql
以及 wireshark 抓包獲取的 sql

驗(yàn)證判斷

from loguru import logger
import settings
from peewee import *
from datetime import datetime, timedelta, timezone
import time
import contextlib
def get_min_utc_timestamp() -> datetime:
    return (datetime(year=1970, month=1, day=1) + timedelta(seconds=1)).replace(tzinfo=timezone.utc)
def get_utc_now_timestamp() -> datetime:
    """ https://blog.csdn.net/ball4022/article/details/101670024 """
    return datetime.utcnow().replace(tzinfo=timezone.utc)
def get_cst_now_timestamp() -> datetime:
    """ https://segmentfault.com/q/1010000043912065 """
    try:
        from zoneinfo import ZoneInfo
        tz = ZoneInfo('Asia/Shanghai')
        return datetime.now(tz)
    except ImportError:
        beijing_offset = timedelta(hours=8)
        current_time = datetime.now(timezone(beijing_offset))
        return current_time
host = settings.MYSQL_CONFIG.host
port = settings.MYSQL_CONFIG.port
username = settings.MYSQL_CONFIG.username
password = settings.MYSQL_CONFIG.password
database_name = settings.MYSQL_CONFIG.database_name
db = MySQLDatabase(
    database=database_name,
    host=host,
    port=port,
    user=username,
    password=password,
    charset='utf8mb4'
)
class User(Model):
    name = CharField(unique=True)
    age = IntegerField(null=True)
    address = CharField(null=True)
    city = CharField(null=True)
    birth = DateTimeField(null=True)
    created_at = DateTimeField(
        null=False,
        constraints=[SQL('DEFAULT CURRENT_TIMESTAMP')],
        help_text='使用數(shù)據(jù)庫時(shí)間'
    )
    updated_at = DateTimeField(
        null=False,
        constraints=[
            SQL('DEFAULT CURRENT_TIMESTAMP'),
            SQL('ON UPDATE CURRENT_TIMESTAMP'),
        ]
    )
    class Meta:
        database = db
        table_name = 'user'
model_set = [User]
db.drop_tables(model_set)
db.create_tables(model_set)
d = get_cst_now_timestamp()
print(d)
q = User.select().where(
    User.age == 1,
    User.birth == d
)
print('> sql', str(q))
list(q)
q = User.insert({'created_at': d})
logger.debug(str(q))
q.execute()

修改 peewee 的源碼

輸出如下：

>>> execute_sql sql SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE() AND table_type != %s ORDER BY table_name
>> execute sql DROP TABLE IF EXISTS `user`
>>> execute_sql sql DROP TABLE IF EXISTS `user`
>>> execute_sql sql SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE() AND table_type != %s ORDER BY table_name
>> execute sql CREATE TABLE IF NOT EXISTS `user` (`id` INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `age` INTEGER, `address` VARCHAR(255), `city` VARCHAR(255), `birth` DATETIME, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
>>> execute_sql sql CREATE TABLE IF NOT EXISTS `user` (`id` INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `age` INTEGER, `address` VARCHAR(255), `city` VARCHAR(255), `birth` DATETIME, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
>> execute sql CREATE UNIQUE INDEX `user_name` ON `user` (`name`)
>>> execute_sql sql CREATE UNIQUE INDEX `user_name` ON `user` (`name`)
2023-06-17 14:26:22.111281+08:00
> sql SELECT `t1`.`id`, `t1`.`name`, `t1`.`age`, `t1`.`address`, `t1`.`city`, `t1`.`birth`, `t1`.`created_at`, `t1`.`updated_at` FROM `user` AS `t1` WHERE ((`t1`.`age` = 1) AND (`t1`.`birth` = '2023-06-17 14:26:22.111281+08:00'))
>> execute sql SELECT `t1`.`id`, `t1`.`name`, `t1`.`age`, `t1`.`address`, `t1`.`city`, `t1`.`birth`, `t1`.`created_at`, `t1`.`updated_at` FROM `user` AS `t1` WHERE ((`t1`.`age` = %s) AND (`t1`.`birth` = %s))
>>> execute_sql sql SELECT `t1`.`id`, `t1`.`name`, `t1`.`age`, `t1`.`address`, `t1`.`city`, `t1`.`birth`, `t1`.`created_at`, `t1`.`updated_at` FROM `user` AS `t1` WHERE ((`t1`.`age` = %s) AND (`t1`.`birth` = %s))
2023-06-17 14:26:22.116 | DEBUG | __main__:<module>:68 - INSERT INTO `user` (`created_at`) VALUES ('2023-06-17 14:26:22.111281+08:00')
>> execute sql INSERT INTO `user` (`created_at`) VALUES (%s)
>>> execute_sql sql INSERT INTO `user` (`created_at`) VALUES (%s)
self._query_type 0

注意，我現(xiàn)在測(cè)試的時(shí)間，就是北京時(shí)間下午2點(diǎn)

查看 wireshark 的時(shí)間