将日期时间值导入 TSQL 时无效的日期格式 (0) [英] Invalid date format (0) when importing datetime values to TSQL

问题描述

我正在尝试将日期时间值从我的 Pandas 数据框中导入到我的 TSQL 表的日期时间列中.

问题是，如果我的任何列包含 NULL 值NaT"，我就会收到错误

见下图:

导入pyodbc将熊猫导入为 pdcnxn = pyodbc.connect(driver='{SQL Server Native Client 10.0}',主机=服务器，数据库=数据库名，可信连接=tcon，用户=uname，密码=pword)游标 = cnxn.cursor()df.head()日期_1 日期_21 2015-07-01 10:53:16 2015-07-01 00:13:092 2015-07-03 10:31:16 2015-07-01 16:39:403 2015-06-26 14:39:19 2015-06-24 13:56:17对于索引，df.iterrows() 中的行:cursor.execute("""插入表(日期 1, 日期 2)""""""VALUES (?,?)""",行['Date1'],行['Date2'])cnxn.commit()

<块引用>

DataError: ('22007', '[22007] [Microsoft][SQL Server Native Client10.0]无效的日期格式 (0) (SQLExecDirectW)')

它与我的日期时间列中的 Null 值有关.当我查看数据框中的空值时，它们显示为 NaT.

我不知道如何向我的 TSQL 表导入任何内容.

解决方案

NaT 表示 not-a-time，即 null 表示为 numpy.datetime64 类型.datetime64 类型不支持 pyodbc 期望的 None 值.

使用 DataFrame.to_dict 方法，它将处理从 datetime64 到 datetime.datetime

>>> df日期_1 日期_21 2016-04-04 15:50:40.794355 无2 2016-04-04 15:50:40.794355 无3 2016-04-04 15:50:40.794355 无>>> 行 = df.to_dict(orient="records")>>> pprint(行)[{'Date_1': Timestamp('2016-04-04 15:50:40.794355'), 'Date_2': None},{'Date_1':时间戳('2016-04-04 15:50:40.794355')，'Date_2':无}，{'Date_1': Timestamp('2016-04-04 15:50:40.794355'), 'Date_2': None}]

然后循环遍历字典列表(rows)并像往常一样使用 pyodbc 插入.不需要任何 SQL 修改.

I am trying to import datetime values from my pandas dataframe in to the datetime columns of my TSQL Table.

The problem is that I get an error if any of my columns contain a NULL value 'NaT'

see below:

import pyodbc
import pandas as pd

cnxn = pyodbc.connect(driver='{SQL Server Native Client 10.0}',
                      host=server,database=dbname,
                      trusted_connection=tcon,
                      user=uname,password=pword)

cursor = cnxn.cursor()

df.head()
            Date_1          Date_2
1   2015-07-01 10:53:16 2015-07-01 00:13:09
2   2015-07-03 10:31:16 2015-07-01 16:39:40
3   2015-06-26 14:39:19 2015-06-24 13:56:17

for index, row in df.iterrows():
cursor.execute("""
INSERT INTO Table(
Date1,Date2)"""
"""VALUES (?,?)""",
row['Date1'],row['Date2'])

cnxn.commit() 

DataError: ('22007', '[22007] [Microsoft][SQL Server Native Client 10.0]Invalid date format (0) (SQLExecDirectW)')

It has to do with Null values in my datetime columns. When I view the null values in my dataframe they appear as NaT.

I don't know how to import nothing to my TSQL Table.

解决方案

NaT represents not-a-time, which is the representation of null as a numpy.datetime64 type. The datetime64 type does not support a value of None, which pyodbc expects.

Export the dataframe to dict using DataFrame.to_dict method, which will handle the type conversion from datetime64 to a datetime.datetime

>>> df
                      Date_1 Date_2
1 2016-04-04 15:50:40.794355   None
2 2016-04-04 15:50:40.794355   None
3 2016-04-04 15:50:40.794355   None
>>> rows = df.to_dict(orient="records")
>>> pprint(rows)
[{'Date_1': Timestamp('2016-04-04 15:50:40.794355'), 'Date_2': None},
 {'Date_1': Timestamp('2016-04-04 15:50:40.794355'), 'Date_2': None},
 {'Date_1': Timestamp('2016-04-04 15:50:40.794355'), 'Date_2': None}]

Then loop through the list of dicts (rows) and insert with pyodbc as normal. Doesn't require any SQL modifications.

这篇关于将日期时间值导入 TSQL 时无效的日期格式 (0)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！