1

executemany throws error when I try to insert a list of tuples that is generated using list compression, but works when insert the same list if it’s hard-code. When I try:

a=[(i[0][0],i[0][1],i[0][2],i[1][0],i[1][1],i[0][5]) for i in zipList_updates]
c.executemany('INSERT INTO Households VALUES(?,?,?,?,?,?)',a)

I get: InterfaceError: Error binding parameter 4 - probably unsupported type.

But when I hard-code the list of values as:

b=[('1000000US371830501001017', 'White', 2, '150-200K', 184, 'Renter'),\
('1000000US371830501001017', 'Asian', 2, '125-150K', 250, 'Renter')]

and try:

c.executemany('INSERT INTO Households VALUES(?,?,?,?,?,?)',b)

it works fine. When I check a==b , I get True.

I don't understand how it's possible as a and b seems to be the same thing.

9000
  • 39,899
  • 9
  • 66
  • 104
Reza Amindarbari
  • 53
  • 1
  • 7

1 Answers1

0

I suspect wrapping items in str() may help:

# factor out a bit of copy-paste
def makeTuple(record):
  indexes = ((0, 0), (0, 1), (0, 2), (1, 0), (1, 1), (0, 5))
  # note the str↴
  return tuple(str(record[j][k]) for (j, k) in indexes)

a = [makeTuple(i) for i in zipList_updates]

Try checking the type of items in tuples of a. Definitely in tuples inside b items are strings. I have no idea what can be in zipLiat_updates.

I have seen that objects feign the behavior of strings by clever proxying of attributes, including equality. But the illusion is dispelled if such an object is passed to a C library like sqlite or other database drivers: a type error happens, because they expect a real string.

9000
  • 39,899
  • 9
  • 66
  • 104
  • Casting to string worked. But it seems that the problem is somehow related to integer values, not the string values. I only changed the 3rd and 5th items to string. Although my 3rd and 5th field types are integer, the following works: a=[(i[0][0],i[0][1],str(i[0][2]),i[1][0],str(i[1][1]),i[0][5]) for i in zipList_updates] . Those integers don't cause any problem for b though. The other solution that worked too, was to cast the entire list to an string then using some messy string methods recreated the list and nested tuples from the string. – Reza Amindarbari Dec 29 '15 at 00:09
  • Pretty funny. BTW, you can probably replace the messy string methods with `import json; a_json = json.dumps(a); recreated_a = json.loads(a_json)`. I'd rather avoid both, though, and make sure I understand what's going on with the integer parameters. For instance, the parameters you receive can be [`decimal`](https://docs.python.org/2/library/decimal.html), they print, compare and otherwise behave as normal numbers (only more precise), but a C library won't take them where it expects an integer. – 9000 Dec 29 '15 at 02:54