I'm using pymssql to connect to a MS SQL DB on Azure and insert records from a CSV file. I've verified that the connection is successful, and that the list used for the executemany parameter contains all of the data in the correct format and with the correct number of values. However, when I run the script 0 rows are inserted - but no error is thrown.
I looked around and it seems like most others that have experienced something similar were missing the commit(), but that isn't the issue here.
Here is the code. Any help is greatly appreciated.
with open('file.csv') as csvfile:
data = csv.reader(csvfile)
next(data)
dicts = ({'col1': line[0], 'col1': line[1], 'col3': line[2], 'col4': int(line[3]), 'col5': int(line[4]), 'col6': float(line[5])} for line in data)
to_db = ((i['col1'], i['col2'], i['col3'], i['col4'], i['col5'], i['col6']) for i in dicts)
cursor.executemany(
'INSERT INTO myTable VALUES (%s, %s, %s, %d, %d, %f)',
to_db)
print str(cursor.rowcount) + " rows inserted"
conn.commit()
Edit: If I execute the query using cursor.execute() and include the values explicitly in the query then I can successfully insert rows into the database (see below for example).
cursor.execute("INSERT INTO myTable VALUES ('4/18/2016','test','test',0,0,0.0)")
But if I user the cursor.executemany(operation,parameters) syntax and pass a list of the values as the parameter then it results in an incorrect syntax error.
cursor.executemany("INSERT INTO myTable VALUES(%s,%s,%s,%d,%d,%f)",list_of_values)
I was just reading in the module reference that only %s and %d are supported. So I'm thinking that might be the issue. But how do I pass a float?
Using the float placeholder (%f) was in fact the issue. Only %s and %d are supported, but are purely placeholders and do not have any impact on formatting the way that they typically do in python, so really only %s is needed. The working code is as follows:
cursor.executemany("INSERT INTO myTable VALUES(%s,%s,%s,%s,%s,%s)",list_of_values)