objection creation error with factory-boy and sql server identity columns

Description

Edit - title to reflect possible updated problem

Does factory-boy do anything internally with the db user connecting to the database? I'm getting strange errors when trying to set this up for a new project, when the factory tries to create the object, that looks like permissions errors. But the user I connect with in my db connection string for the test suite is the same as in my regular code. The user has the correct permissions, and can successfully write to the database.

Trying to set up factory-boy with pytest-factoryboy for a new project to test a SQL Server database. I've previously used factory-boy successfully on other projects, with other dbs, e.g. postgres. This is the first project with a SQL Server db. I can't for the life of me get this working correctly for a single table test.

To Reproduce

Running the test_mission pytest below with the setup below results in the error

E       sqlalchemy.exc.ProgrammingError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
E       (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot find the object "Mission" because it does not exist or you do not have permissions. (1088) (SQLExecDirectW)')
E       [SQL: SET IDENTITY_INSERT [Mission] ON]
E       (Background on this error at: http://sqlalche.me/e/13/f405)

Model / Factory code

I'm auto-reflecting my models with automap_base.

# auto-reflected models
Base = automap_base(cls=BaseModel)
Base.prepare(engine, reflect=True)

# my session    
url = get_url('test').     # my db connection string 
engine = sqlalchemy.create_engine(url)
session_factory = sessionmaker(bind=engine, expire_on_commit=True, autocommit=True)
Session = scoped_session(session_factory)

but I also tried a declarative base model. The model only has two columns, an auto-incrementing identity unique key missionID, and a string column label.

# declarative base model
Base = declarative_base(bind=engine)
class Mission(Base):
    __tablename__ = 'Mission'
    __table_args__ = {'schema': 'dbo', 'autoload': True}

    def __repr__(self):
        return f'<Mission id={self.missionID}, label={self.label}>'

Factory

# factory
class MissionFactory(factory.alchemy.SQLAlchemyModelFactory):
    class Meta:
        model = Base.classes.Mission
        sqlalchemy_session = Session
    missionID = factory.Sequence(lambda n: n)
    label = factory.Faker('name')

The issue

I tried the simple test_mission and get the error above, with full traceback below. The table already has an existing row in it (id=1, label="A").

pytest fixtures


@pytest.fixture(scope='session')
def connection():
    url = get_url('test').     # my db connection string 
    engine = sqlalchemy.create_engine(url)
    connection = engine.connect()
    yield connection
    connection.close()

@pytest.fixture(autouse=True)
def session(connection):
    session = Session(bind=connection)
    session.begin()
    yield session
    session.rollback()
    session.close()

Simple test that fails


def test_mission(session, mission_factory):
    missions = session.query(Base.classes.Mission.label).all()
    assert missions == [('A',)]
    mission = mission_factory(label="B")
    assert isinstance(mission, Base.classes.Mission)
    missions = session.query(Base.classes.Mission.label).all()
    assert missions == [('A'), ("B")]

I thought it was a flushing issue, so I turned off autoflush in session_factory = sessionmaker(bind=engine, expire_on_commit=True, autocommit=True, autoflush=False). When I do that, I don't get the error but the object is not saved to the database, probably since only a session.add is happening. I also tried setting on MissionFactory the sqlalchemy_session_persistence to both flush and commit, and I get a similar error.

self = <sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc object at 0x7ff02aeea460>, cursor = <pyodbc.Cursor object at 0x7ff01861b430>, statement = 'SET IDENTITY_INSERT [Mission] ON'
parameters = (), context = <sqlalchemy.dialects.mssql.pyodbc.MSExecutionContext_pyodbc object at 0x7ff018627280>

    def do_execute(self, cursor, statement, parameters, context=None):
>       cursor.execute(statement, parameters)
E       sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot find the object "Mission" because it does not exist or you do not have permissions. (1088) (SQLExecDirectW)')
E       [SQL: SET IDENTITY_INSERT [Mission] ON]
E       (Background on this error at: http://sqlalche.me/e/13/f405)

Notes

Full traceback

session.query(Base.classes.Mission).all()
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _cursor_execute(self, cursor, statement, parameters, context)
   1350             else:
-> 1351                 self.dialect.do_execute(cursor, statement, parameters, context)
   1352         except BaseException as e:

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    607     def do_execute(self, cursor, statement, parameters, context=None):
--> 608         cursor.execute(statement, parameters)
    609

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot find the object "Mission" because it does not exist or you do not have permissions. (1088) (SQLExecDirectW)')

The above exception was the direct cause of the following exception:

ProgrammingError                          Traceback (most recent call last)
<ipython-input-12-8dca229657eb> in <module>
----> 1 session.query(Base.classes.Mission).all()

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/orm/query.py in all(self)
   3371                 :ref:`faq_query_deduplicating`
   3372         """
-> 3373         return list(self)
   3374
   3375     @_generative(_no_clauseelement_condition)

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/orm/query.py in __iter__(self)
   3532         context.statement.use_labels = True
   3533         if self._autoflush and not self._populate_existing:
-> 3534             self.session._autoflush()
   3535         return self._execute_and_instances(context)
   3536

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/orm/session.py in _autoflush(self)
   1631                     "flush is occurring prematurely"
   1632                 )
-> 1633                 util.raise_(e, with_traceback=sys.exc_info()[2])
   1634
   1635     def refresh(

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
    180
    181         try:
--> 182             raise exception
    183         finally:
    184             # credit to

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/orm/session.py in _autoflush(self)
   1620         if self.autoflush and not self._flushing:
   1621             try:
-> 1622                 self.flush()
   1623             except sa_exc.StatementError as e:
   1624                 # note we are reraising StatementError as opposed to

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/orm/session.py in flush(self, objects)
   2538         try:
   2539             self._flushing = True
-> 2540             self._flush(objects)
   2541         finally:
   2542             self._flushing = False

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/orm/session.py in _flush(self, objects)
   2680         except:
   2681             with util.safe_reraise():
-> 2682                 transaction.rollback(_capture_exception=True)
   2683
   2684     def bulk_save_objects(

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback)
     66             self._exc_info = None  # remove potential circular references
     67             if not self.warn_only:
---> 68                 compat.raise_(
     69                     exc_value,
     70                     with_traceback=exc_tb,

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
    180
    181         try:
--> 182             raise exception
    183         finally:
    184             # credit to

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/orm/session.py in _flush(self, objects)
   2640             self._warn_on_events = True
   2641             try:
-> 2642                 flush_context.execute()
   2643             finally:
   2644                 self._warn_on_events = False

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py in execute(self)
    420         else:
    421             for rec in topological.sort(self.dependencies, postsort_actions):
--> 422                 rec.execute(self)
    423
    424     def finalize_flush_changes(self):

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py in execute(self, uow)
    584
    585     def execute(self, uow):
--> 586         persistence.save_obj(
    587             self.mapper,
    588             uow.states_for_mapper_hierarchy(self.mapper, False, False),

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py in save_obj(base_mapper, states, uowtransaction, single)
    237         )
    238
--> 239         _emit_insert_statements(
    240             base_mapper,
    241             uowtransaction,

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py in _emit_insert_statements(base_mapper, uowtransaction, cached_connections, mapper, table, insert, bookkeeping)
   1081             multiparams = [rec[2] for rec in records]
   1082
-> 1083             c = cached_connections[connection].execute(statement, multiparams)
   1084             if bookkeeping:
   1085                 for (

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/engine/base.py in execute(self, object_, *multiparams, **params)
   1009             )
   1010         else:
-> 1011             return meth(self, multiparams, params)
   1012
   1013     def _execute_function(self, func, multiparams, params):

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params)
    296     def _execute_on_connection(self, connection, multiparams, params):
    297         if self.supports_execution:
--> 298             return connection._execute_clauseelement(self, multiparams, params)
    299         else:
    300             raise exc.ObjectNotExecutableError(self)

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params)
   1122             )
   1123
-> 1124         ret = self._execute_context(
   1125             dialect,
   1126             dialect.execution_ctx_cls._init_compiled,

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1209
   1210         if context.compiled:
-> 1211             context.pre_exec()
   1212
   1213         cursor, statement, parameters = (

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/dialects/mssql/pyodbc.py in pre_exec(self)
    310         """
    311
--> 312         super(MSExecutionContext_pyodbc, self).pre_exec()
    313
    314         # don't embed the scope_identity select into an

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/dialects/mssql/base.py in pre_exec(self)
   1492
   1493             if self._enable_identity_insert:
-> 1494                 self.root_connection._cursor_execute(
   1495                     self.cursor,
   1496                     self._opt_encode(

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _cursor_execute(self, cursor, statement, parameters, context)
   1351                 self.dialect.do_execute(cursor, statement, parameters, context)
   1352         except BaseException as e:
-> 1353             self._handle_dbapi_exception(
   1354                 e, statement, parameters, cursor, context
   1355             )

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1508                 util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   1509             elif should_wrap:
-> 1510                 util.raise_(
   1511                     sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   1512                 )

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
    180
    181         try:
--> 182             raise exception
    183         finally:
    184             # credit to

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _cursor_execute(self, cursor, statement, parameters, context)
   1349                     break
   1350             else:
-> 1351                 self.dialect.do_execute(cursor, statement, parameters, context)
   1352         except BaseException as e:
   1353             self._handle_dbapi_exception(

~/anaconda3/envs/jwstdb/lib/python3.8/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    606
    607     def do_execute(self, cursor, statement, parameters, context=None):
--> 608         cursor.execute(statement, parameters)
    609
    610     def do_execute_no_params(self, cursor, statement, context=None):

ProgrammingError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot find the object "Mission" because it does not exist or you do not have permissions. (1088) (SQLExecDirectW)')
[SQL: SET IDENTITY_INSERT [Mission] ON]
(Background on this error at: http://sqlalche.me/e/13/f405)

FactoryBoy / factory_boy