CODEWITHSUNDEEP
pythonsqlitesqlalchemy
rateldajer
rateldajer

Reputation: 148

Weird type error arising when I add to a database using SQL Alchemy

I've just started using the SQLAlchemy package for python to maintain a simple sqlite3 database for some experiments. This bug took me a while to figure out so I thought I'd post it up. There are probably better approaches to the one I found as well. I'm using Python 2.7.2.

The problem arises when I have an Integer Column in a class matching a table in the database such as this simple Featdata class.

class FeatData(Base):
  __tablename__ = 'featdata'

  id = Column(Integer, primary_key=True)
  name = Column(String)
  value = Column(Integer)
  order = Column(Integer)

When I try to add records to the table I'm doing some other manipulation on the data, not really what is shown here but this code generates the same bug. I'm sorting the entries first and using the output of that sort to fill the column values.

engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)

Session = sessionmaker(autoflush=False, bind=engine)
session = Session()

featnames=['thing','widget','dongle']
featvalues=[87,2,15]
newobjects=[]

for i in numpy.argsort(featvalues):
  newobjects += [FeatData(name=featnames[i], value=featvalues[i], order = i) ]

session.add_all(newobjects)
session.commit()

I get the following error messages, there is something wrong with the types in here somewhere. Any ideas?

Error messages:

2014-03-28 06:50:14,376 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2014-03-28 06:50:14,377 INFO sqlalchemy.engine.base.Engine INSERT INTO featdata (name, value, "order") VALUES (?, ?, ?)

2014-03-28 06:50:14,377 INFO sqlalchemy.engine.base.Engine ('widget', 2, 1)

2014-03-28 06:50:14,378 INFO sqlalchemy.engine.base.Engine ROLLBACK

...

File "/Library/Frameworks/Python.framework/Versions/7.2/lib/python2.7/site-packages/SQLAlchemy-0.9.3-py2.7-macosx-10.5-i386.egg/sqlalchemy/engine/default.py", line 425, in do_execute

cursor.execute(statement, parameters)

sqlalchemy.exc.InterfaceError: (InterfaceError) Error binding parameter 2 - probably unsupported type. u'INSERT INTO featdata (name, value, "order") VALUES (?, ?, ?)' ('widget', 2, 1)

Upvotes: 2

Views: 1368

Answers (2)

rateldajer
rateldajer

Reputation: 148

I figured this but out before I saw misspec's response so I'm posting my own answer anyways. When a Column(Integer) is created in your sqlite3 database using SQLAlchemy you need insert values for that column which are python ints. In my case I was doing something using numpy that returned an array of ints which I used to fill the columns. But in this case i is <type numpy.int32> not <type 'int'> as it needs to be.

A simple way to fix this without changing anything is to wrap the numpy int in int():

for i in numpy.argsort(featvalues):
  newobjects += [FeatData(name=featnames[int(i)], value=featvalues[i], order = i) ]

Upvotes: 1

mattgathu
mattgathu

Reputation: 1167

the type returned by the numpy method is not of Integer type:

I tried the code below on my interpreter

>>import numpy 
>>values = [87, 2, 15]
>>n = numpy.argsort(values) 
>>type(n[0])
numpy.int64

The data type is is numpy.int64.

Try casting the type to integer before making the objects.

newobjects += [FeatData(name=featnames[int(i)], value=featvalues[int(i)], order = int(i)) ]

Upvotes: 1

Related Questions