pymssql: executemany value error - expected a simp

问题:

grpidx_data=[]
for i in range(0,len(data1)):
    grpidx_data.append((data1.loc[i,'price'],data1.loc[i,'id']))
cur.executemany("insert into grpidx values (%s,%s)",grpidx_data)

I use python3.3 and pymssql. I want to import data from python to MSSQL. grpidx_data's type is list(tuple),like[(12,1),(34,2),...], I run the code above then got the error:

ValueError: expected a simple type, a tuple or a list

If I just use the data which type is list(tuple), the code works fine. But when I use for loop got the data, even its type also list(tuple),its not working.

So How to solve this problem?

Thanks!

回答1:

I had the same problem, actually having one script that ran and one that didn't. I eventually figured out that the one that was working was all strings and the other had numbers. Digging in further I was finding that the bad type was the numpy.int64 type that numpy uses for integers. Looks like you are using pandas, which also has those number types. I forced it to a string, ran the code again and everything worked.

I wrote a special cleaning function to clean out the tuple before running it:

def tupleGetter(x):
    for i in range(len(x)):
        if str(type(x[i])) == "<class 'numpy.int64'>":
            x[i] = str(x[i])
    return tuple(x)    

Then I ran my code, almost exactly like what you have above:

myValues = (tupleGetter(df.ix[i]) for i in range(len(df)))
cursor.executemany("""INSERT INTO dbo.MyTable([A],[B],[C],[C],[D],[E],[F])
 VALUES (%s, %s, %s, %s, %s, %s)""",
    myValues
    )

conn.commit()

Ran no problem.

Be sure to dropna() as well because that is also a strange type.

回答2:

As billmanH pointed out, using a type that is not expected by pymssql can generate this error.

Converting to strings works when you want to store strings in the database but I wanted numbers. By passing all as strings and converting on the SQL side, I had total control on the type of the data.

cursor.execute("INSERT INTO my_table(my_int_column, my_numeric_column) VALUES (CAST(%s AS INTEGER),  CAST(%s AS NUMERIC(19, 9)))",
my_tuple_containing_strings)