pythonsql-serverpyodbcexecutemany

(fast_executemany = True) Error "[ODBC Driver 17 for SQL Server]Invalid character value for cast specification (0) (SQLExecute)')"

I'm using (executemany) function from pyodbc to populate data into an mssql database. This is my code:

def populate_database(conn):
    tuples = [
        ('2020-04-13 00:50:42', 'AirShoppingRQ', 'ALEY', '2020-05-23', '', '', 'BRU-BLQ', ''),     
        ('2020-04-13 00:50:43', 'AirShoppingRQ', 'ALEY', '2021-01-23', '', '', 'LIS-STO', '')
    ]
    query_string = 'INSERT INTO mytable VALUES (?,?,?,?,?,?,?,?)'
    cursor = conn.cursor()
    #cursor.fast_executemany = True
    cursor.executemany(query_string, tuples)
    cursor.commit()

It works fine, but if I uncomment the line cursor.fast_executemany = True, then I got the following error: 

('22018', '[22018] [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification (0) (SQLExecute)')

The following is my table:

CREATE TABLE [dbo].[mytable](
  [field1] [datetime] NULL,
  [field2] [varchar](50) NULL,
  [field3] [varchar](20) NULL,
  [field4] [datetime] NULL,
  [field5] [datetime] NULL,
  [field6] [varchar](20) NULL,
  [field7] [varchar](40) NULL,
  [field8] [varchar](40) NULL
)

Is there something wrong with the formats? What am I missing when using cursor.fast_executemany = True?

Solution

  • You can use this to convert all empty strings to None values in the tuples. This doesn't only apply to datetime fields.

    tuples = [(a, *(b if b else None for b in rest)) for a, *rest in tuples]