SQLAlchemy model primary key column attribute name must match column name

[hartror]

So we've got a set of SQLAlchemy models like so (don't ask why, it makes me sad).

class Test(Base):
    __tablename__ = "test"

    id = sqlalchemy.Column('id_', sqlalchemy.Integer, primary_key=True)

When using a factory for the class we get:

Traceback (most recent call last):
  File "<pyshell#0>", line 16, in <module>
    TestFactory.build()
  File "/usr/local/lib/python2.7/dist-packages/factory/base.py", line 434, in build
    attrs = cls.attributes(create=False, extra=kwargs)
  File "/usr/local/lib/python2.7/dist-packages/factory/base.py", line 316, in attributes
    force_sequence=force_sequence,
  File "/usr/local/lib/python2.7/dist-packages/factory/containers.py", line 263, in build
    sequence = self.factory._generate_next_sequence()
  File "/usr/local/lib/python2.7/dist-packages/factory/base.py", line 287, in _generate_next_sequence
    cls._next_sequence = cls._setup_next_sequence()
  File "/usr/local/lib/python2.7/dist-packages/factory/alchemy.py", line 37, in _setup_next_sequence
    pk = getattr(model, model.__mapper__.primary_key[0].name)
AttributeError: type object 'Test' has no attribute 'id_'

A full example:

from sqlalchemy.ext.declarative import declarative_base
import sqlalchemy
import factory.alchemy

Base = declarative_base()

class Test(Base):
    __tablename__ = "test"

    id = sqlalchemy.Column('id_', sqlalchemy.Integer, primary_key=True)

class TestFactory(factory.alchemy.SQLAlchemyModelFactory):
    FACTORY_FOR = Test
    FACTORY_SESSION = None

TestFactory.build()

[hartror]

SHA: 738e673 removes the primary key functionality and the id from the factory in the tests. This means the database is given responsibility for setting automatic/sequence keys. This avoids the case I reported above as well as other cases where there are multi-column primary keys which would confuse the code I removed. Cases such as where there are multiple integer primary keys where only one of them is serial or where one of them is an integer and the other a non-integer.

A couple of the tests are broken in my example but that is because the intended behaviour has now changed so the assumptions these test make seem wrong to me. However I left them broken because I suspect there might be some concept in factory boy these are covering, specifically the use case of StandardFactory.reset_sequence().

[rbarrois]

Thanks for the PR.

I see two issues around this whole setup_next_sequence code:

1. It tends to perform SQL queries while calling build(), which is not exactly expected behavior: a build() shouldn't touch the database at all
2. Removing it altogether increases the risk of conflicts when setting unique fields from factory.Sequence

I'll try to find a way to introspect the schema so that factory_boy can find out:

• If there's an auto-increment column, use it
• Otherwise, if there is a single column, numerical PK, use it
• In all other cases, don't even try to find a Sequence.

Actually, I have the same kind of issues with DjangoModelFactory.

[hartror]

What are the reasons that this should be factory_boy's responsibility? Shouldn't users be responsible for ensuring they craft and use factories in a way that doesn't create conflicts with unique fields?

[steder]

A colleague and I just ran into this same issue (we have aliased primary keys) and it seems like we're able to work around it with a simple subclass of SQLAlchemyModelFactory, namely:

class OurFactory(SQLAlchemyModelFactory):    
    @classmethod
    def _setup_next_sequence(cls, *args, **kwargs):
        max_id = getattr(cls, "max_id", 0)
        cls.max_id = max_id + 1
        return cls.max_id

This seems to satisfy the requirement in the common case (either an auto-increment or single numerical PK exists).

This seems to mitigate issues 1 and 2 by not issuing unnecessary queries and it helps prevent conflicts.

The downside of this is that it isn't thread-safe, but I don't think the old implementation was thread-safe either so perhaps that's not an issue. That said, wrapping this is a mutex could be easy enough.

If this is a reasonable fix I'd happily create a PR including it.

[amoffat]

@steder and i poked at it some more, this looks more like what you were trying to achieve originally, but this way, it works for columns that have been aliased. @steder and i talked more about the previous solution, and it doesn't really make sense to have the class keep track of max_id, in case records get inserted from somewhere other than factory_boy.

from sqlalchemy.sql import column
from sqlalchemy import func


class BetterThanMikes(SQLAlchemyModelFactory):    
    @classmethod
    def _setup_next_sequence(cls, *args, **kwargs):
        session = cls.FACTORY_SESSION
        model = cls.FACTORY_FOR
        pk = column(model.__mapper__.primary_key[0].name)
        max_pk = session.query(model, func.max(pk)).one()[0]

        if isinstance(max_pk, int):
            return max_pk + 1 if max_pk else 1
        else:
            return 1

[airdrummingfool]

I started using @amoffat's solution above (thanks!), but ran into an error where sometimes func.max(pk) would be ambiguous:

OperationalError: (OperationalError) (1052, "Column 'id' in field list is ambiguous") [... large SELECT statement ...] 

I modified the function to explicitly specify the model's table along with the pk column, and it seems to work:

from factory.alchemy import SQLAlchemyModelFactory
from sqlalchemy.sql import column
from sqlalchemy import func


class PKFixedSQLAlchemyModelFactory(SQLAlchemyModelFactory):
    @classmethod
    def _setup_next_sequence(cls, *args, **kwargs):
        session = cls.FACTORY_SESSION
        model = cls.FACTORY_FOR
        pk = column(model.__mapper__.primary_key[0].name)
        max_pk = session.query(model, func.max('{}.{}'.format(model.__tablename__, pk))).one()[0]

        if isinstance(max_pk, int):
            return max_pk + 1 if max_pk else 1
        else:
            return 1

[rbarrois]

This issue has been fixed in 13d310f, and will be part of the 2.5.0 release.