Is it possible to use a namedtuple with SQLalchemy?

Question

I have been trying to get a namedtuple to work with SQLalchemy, but to no avail.. Web search hasn't been very illuminating and I'm new with Python and SQLalchemy so I'm not really sure if I'm chasing windmills :( The basic idea is that I have a namedtuple, ie:

Point=namedtuple('Point',['x','y'])

which basically creates a class Point(tuple) if I'm correct. This works fine at first and I can create objects like:

p=Point(3,4)

But after I create the engine etc and call mapper , I can't create any more objects without getting this error:

Traceback (most recent call last):
  File "<pyshell#62>", line 1, in <module>
    f=Point(3,4)
TypeError: __init__() takes exactly 1 argument (3 given)

Any ideas why that happens? Does anyone know how to make a namedtuple work with sqlalchemy? Of course I can define my own Point class, but I'm obsessing over making namedtuple work now.. I'm using Python 2.7, SQLalchemy 0.6.6 (sqlite engine)

EXAMPLE:

I'm trying something like this:

from sqlalchemy import *
from sqlalchemy.orm import *
from collections import namedtuple

Point=namedtuple('Point',['x','y'],verbose=True)
p=Point(3,4)


db=create_engine('sqlite:///pointtest.db')
metadata=MetaData()
pointxy=Table('pointxy',metadata,
              Column('no',Integer,primary_key=True),
              Column('x',Integer),
              Column('y',Integer),
              sqlite_autoincrement=True)
metadata.create_all(db)
m=mapper(Point, pointxy)
Session=sessionmaker(bind=db)
session=Session()
f=Point(3,4)

The main idea is that I want a named collection of stuff that can be easily stored in a database. So this:

class Bunch:
    __init__ = lambda self, **kw: setattr(self, '__dict__', kw)

is not going to work with sqlalchemy (I think). I can create a Bunch class but I won't know beforehand how many ints I want to store in my collection.. I will set it before I create my database. I hope I'm making sense..

Answer 1

Namedtuples have a bunch of behaviors that make them unsuitable for mapping with sqlalchemy: Most importantly, namedtuples can't be changed after they are created. This means that you can't use a namedtuple to track the state of a row in the database after the say an insert operation. You would typically want do something like this:

class MyDataHolder(namedtuple('MyDataHolder', ('id', 'my_value')):
    pass

mapper(MyDataHolder, MyDataMeta)

...

newRow = MyDataHolder(None, 'AAA')

...

session.add(newRow)

When the session code executes the SQL to add the new data to the database it's going to want to update newRow so that newRow.id corresponds to id that the database assigned to your row. But because newRow is an immutable tuple, id can't be changed to hold the primary key that came back from the database. This makes namedtuples largely unsuitable for mappers.

The __init__ issues happen because namedtuple get initialized in __new__ and then aren't expected to change. __init__() gets called after the object has been created and so has no effect. Thus __init__ for a namedtuple defines only one argument: self. I'm guessing that The mapper assumes that __init__() is handling class initialization and doesn't know about __new__ and immutable types. It looks like they are calling classname.__init__() with the args that get passed at creation time. You could "fix" this by specifying your own initializer: __init__(self, *args) but then you end up at the weakref problem.

The weakref error happens because namedtuples use __slots__ to store their values rather than mutable __dict__s. I know that the use of __slots__ is a memory optimization so that you can store lots namedtuples efficiently. I'm assuming that it's expected that a namedtuple isn't going to change after it's created. That includes adding attributes so using __slots__ is a worthwhile memory optimization. However, I don't claim to understand why they author of the namedtuple class didn't support weak references. It wouldn't have been particularly difficult but there is probably a really good reason that I'm missing.

I ran into this issue this morning and got around it by defining my own class for data mapping that was initialized with a _fieldset attribute. This specifies the (sub)set of fields that I'm interested in seeing mapped. Then I had some time and read the documentation of how namedtuples are implemented along with a bunch of other python internals. I think that I get most of why this doesn't work but I'm sure that there are python experts out there that have a better grasp of this than I do.

-- Chris