您尝试过此选项吗?
Time zone offset aware and preservation No
Daylight saving aware No
,
对SQLAlchemy中的datetimeoffset
处理进行了一些最近的改进(如“今天上午”)。它们将包含在下一个版本(可能是1.3.13)中,但与此同时,请尝试从1.3.x分支的最新来源进行安装...
pip install --upgrade git+https://github.com/sqlalchemy/sqlalchemy@rel_1_3
...,看看是否更适合您。
编辑:
在进一步调查中,问题似乎出在to_sql
上。如果DataFrame仅包含一行,则时区偏移会丢失:
import datetime
from pprint import pprint
import sqlalchemy as sa
# ...
engine = sa.create_engine(connection_uri,fast_executemany=True)
# test environment
table_name = 'DateTimeOffset_Test'
engine.execute(sa.text(f"DROP TABLE IF EXISTS [{table_name}]"))
engine.execute(sa.text(f"CREATE TABLE [{table_name}] (id int primary key,dto datetimeoffset)"))
# test data
my_tz = datetime.timezone(datetime.timedelta(hours=-7))
dto_value = datetime.datetime(2020,1,tzinfo=my_tz)
print(dto_value) # 2020-01-01 00:00:00-07:00
# ^
num_rows = 1
row_data = [(x,dto_value) for x in range(num_rows)]
df = pd.DataFrame(row_data,columns=['id','dto'])
print(df)
# id dto
# 0 0 2020-01-01 00:00:00-07:00
# ^
df.to_sql(table_name,engine,if_exists='append',index=False)
result = engine.execute(sa.text(f"SELECT id,CAST(dto as varchar(50)) AS foo FROM [{table_name}]")).fetchall()
pprint(result)
# [(0,'2020-01-01 00:00:00.0000000 +00:00')]
# ^ -- wrong
但是,如果DataFrame包含多于一行,则datetimeoffset值会正确上传:
# ...
num_rows = 2
row_data = [(x,'dto'])
print(df)
# id dto
# 0 0 2020-01-01 00:00:00-07:00
# 1 1 2020-01-01 00:00:00-07:00
# ^
df.to_sql(table_name,'2020-01-01 00:00:00.0000000 -07:00'),# (1,'2020-01-01 00:00:00.0000000 -07:00')]
# ^ -- correct
如果您对此确实有强烈的想法,则可能要提出一个pandas issue。
本文链接:https://www.f2er.com/2840665.html