<p>SQLAlchemy中的<code>datetimeoffset</code>处理最近有了一些改进(如“今早”)。它们将包含在下一个版本(可能是1.3.13)中,但在此期间,请尝试从1.3.x分支的最新源安装。。。你知道吗</p>
<pre class="lang-none prettyprint-override"><code>pip install upgrade git+https://github.com/sqlalchemy/sqlalchemy@rel_1_3
</code></pre>
<p>。。。看看这对你是否更有效。你知道吗</p>
<p><strong>编辑:</strong></p>
<p>经进一步调查,问题似乎出在<code>to_sql</code>。如果数据帧包含一行,则时区偏移丢失:</p>
<pre class="lang-py prettyprint-override"><code>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, 1, 0, 0, 0, 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
</code></pre>
<p>但是,如果DataFrame包含多行,则会正确上载datetimeoffset值:</p>
<pre class="lang-py prettyprint-override"><code># ...
num_rows = 2
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
# 1 1 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 -07:00'),
# (1, '2020-01-01 00:00:00.0000000 -07:00')]
# ^ correct
</code></pre>
<p>如果你真的对此有强烈的感觉,你可能想对此提出一个<a href="https://github.com/pandas-dev/pandas/issues" rel="nofollow noreferrer">pandas issue</a>。你知道吗</p>