simplified pyodbc bug repro script

pyodbc_4022_repro_script_2.py

# Python 2.7.12 |Anaconda 4.1.1 (64-bit)| (default, Jun 29 2016, 11:07:13) [MSC v.1500 64 bit (AMD64)]



import pyodbc
import traceback
import sys

assert pyodbc.version == '4.0.22'

stmt = 'INSERT INTO test_insert (val, id) VALUES (?,?)'

conn = pyodbc.connect(
    r'DRIVER={SQL Server Native Client 11.0};'
    r'SERVER=localhost;'
    r'DATABASE=test;'
    r'UID=sa;'
    r'PWD=sa;'
    )


curs = conn.cursor()

curs.execute("SELECT * FROM sys.tables where name = 'test_insert'")
if curs.fetchone():
    curs.execute('DROP TABLE test_insert')


curs.execute('''CREATE TABLE test_insert (
    id smallint not null,
    val smalldatetime not null
)''')

curs.commit()

del(curs)

curs = conn.cursor()

data = (u'2005-01-01 00:00:00', 1)

try:
    curs.execute(stmt, data)
except Exception, e:
    print "**** First Input:\n"
    print stmt
    print data
    print "**** First Exception: \n\n"
    print e
    exc_info = sys.exc_info()
    print traceback.format_exc()
    print "\n\n**** End First Exception \n\n"

import json
json_text = '''[
    {
        "id": 1,
        "val": "2005-01-01 00:00:00"
    }
]'''

data = json.loads(json_text)

tablename = 'test_insert'

columns = data[0].keys()
column_sentence = ','.join(columns)

params = ','.join('?' * len(columns))
stmt = 'INSERT INTO {} ({}) VALUES ({})'.format(tablename, column_sentence, params)

try:
    for row in data:
         curs.execute(stmt, row.values())
except Exception, e:
    print "**** Second Input:\n"
    print stmt
    print row.values()
    print "**** Second Exception: \n\n"
    exc_info = sys.exc_info()
    print traceback.format_exc()
    print e
    print "\n\n**** End Second Exception \n\n"