Successful connection to MS SQL DB on Azure using pymssql, INSERT statement executing with no error message but 0 rows inserted

Question

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?

What happens if you try to execute a sample query like this in the console? — Athena
– Athena, Commented Jul 14, 2016 at 20:18
I can successfully insert rows from MS SQL Server Management Studio. — michoco
– michoco, Commented Jul 14, 2016 at 20:23
Does the Python script say there were no rows changed? Are teh changes reflected in the DB? — Athena
– Athena, Commented Jul 14, 2016 at 20:25
cursor.rowcount is indicating 0 rows affected, and there are no changes reflected in the DB. — michoco
– michoco, Commented Jul 14, 2016 at 20:34
What happens if you switch from executemany to just execute, and iterate over to_db? Sorry for all the questions, I'm just trying to narrow down what's going on. — Athena
– Athena, Commented Jul 14, 2016 at 20:35

michoco · Accepted Answer · 2016-07-15 00:41:38Z

3

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)

answered Jul 15, 2016 at 0:41

michoco

931 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Successful connection to MS SQL DB on Azure using pymssql, INSERT statement executing with no error message but 0 rows inserted

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related