How to insert mm/dd/yyyy strings into a SQL Server datetime column using fast_executemany?

bwitn5fc 于 2023-06-21 发布在 SQL Server

关注(0)|答案(1)|浏览(124)

I am querying data from BQ. I have to insert it to SQL Server 2008.

pseudocode:

# get results from bigquery

client = bigquery.Client()
result = client.query(sql).result()

# create parametrized insert sql statement

sql = 'INSERT INTO [SANDBOX].[dbo].[table_stg] VALUES ((?),(?),(?),(?),(?),(?),(?),(?),(?),(?))'

data = [(2016, 'STRING', 'STRING', 'STRING', 'STRING', 'STRING', INT, 'STRING', '09/28/2015', '09/25/2016'), 
(2016, 'STRING', 'STRING', 'STRING', 'STRING', 'STRING', INT, 'STRING', '09/28/2015', '09/25/2016')]

# target table schema

[varchar](50) NULL,
[varchar](50) NULL,
[varchar](50) NULL,
[varchar](50) NULL,
[varchar](50) NULL,
[varchar](50) NOT NULL,
[int] NULL,
[varchar](50) NULL,
[datetime] NULL,
[datetime] NULL

# insert into sql server with fast_executemany

cursor.fast_executemany = True
cursor.executemany(sql, data)
conn.commit()
cursor.close()
conn.close()

So when I run cursor.executemany(sql, data) with cursor.fast_executemany = True, I receive error:

Invalid character value for cast specification.

String data, right truncation: length 44 buffer 20, HY000

The code works perfectly without cursor.fast_executemany = True, yet it executes each insert with single commit meaning 8 hours to transfer 2 milion rows.

Thanks in advance for help. Also, if there is some filtering mechanism I could sue to split into fast_execute and normal execute I would be super gratefull.

EDIT: edited the question as per comments

sql-server

来源：https://stackoverflow.com/questions/76399675/how-to-insert-mm-dd-yyyy-strings-into-a-sql-server-datetime-column-using-fast-ex

1条答案

按热度按时间

von4xj4u1#

The related question here illustrates how fast_executemany = True affects the way that empty string parameters are interpreted for datetime columns. With fast_executemany = False , empty strings are interpreted as 1900-01-01 00:00:00 . With fast_executemany = True , empty string parameters cause the "Invalid character value for cast specification" error.

The same appears to be true for aa/bb/yyyy string parameters. That format is potentially ambiguous (Is it mm/dd/yyyy or dd/mm/yyyy ?) and SQL Server makes assumptions under fast_executemany = False that it does not make under fast_executemany = True .

This does not throw an error for a column defined as d1 [datetime]

cursor.fast_executemany = False
cursor.executemany(
    "INSERT INTO [SANDBOX].[dbo].[table_stg] (d1) VALUES (?)",
    [("09/28/2015",)]
)

but this fails

cursor.fast_executemany = True
cursor.executemany(
    "INSERT INTO [SANDBOX].[dbo].[table_stg] (d1) VALUES (?)",
    [("09/28/2015",)]
)

However, this works:

cursor.fast_executemany = True
cursor.executemany(
    "INSERT INTO [SANDBOX].[dbo].[table_stg] (d1) VALUES (?)",
    [("2015/09/28",)]
)

So, either reformat the strings to yyyy/mm/dd or parse them into a datetime object

cursor.fast_executemany = True
cursor.executemany(
    "INSERT INTO [SANDBOX].[dbo].[table_stg] (d1) VALUES (?)",
    [(datetime.datetime.strptime("09/28/2015", "%m/%d/%Y"),)]
)

赞(0）回复(0）举报 2023-06-21

我来回答

How to insert mm/dd/yyyy strings into a SQL Server datetime column using fast_executemany?

1条答案

相关问题

热门标签

最新问答