SQLAlchemy InvalidRequestError when inserting to automap generated ORM

Question

I'm trying to use the SQLAlchemy automap extension to generate an ORM for an existing database, and am getting an InvalidRequestError exception ("Instance cannot be refreshed - it's not persistent and does not contain a full primary key.") whenever I try to insert into a table that uses a composite primary key consisting of a timestamp and a foreign key.

Here's some minimal example code that reproduces the problem:

from sqlalchemy import create_engine, func, select
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql.expression import text
from sqlalchemy.ext.automap import automap_base

db_schema_cmds = [
    '''CREATE TABLE users
    (
        u_id INTEGER NOT NULL,
        name TEXT NOT NULL,
        CONSTRAINT Key1 PRIMARY KEY (u_id)
    );''',
    '''CREATE TABLE posts
    (
        timestamp TEXT NOT NULL,
        text TEXT NOT NULL,
        u_id INTEGER NOT NULL,
        CONSTRAINT Key2 PRIMARY KEY (timestamp,u_id),
        CONSTRAINT users_have_posts FOREIGN KEY (u_id) REFERENCES users (u_id) ON DELETE CASCADE
    );''']

# Create a new in-memory SQLite DB and execute the schema SQL commands.
db_engine = create_engine('sqlite://')
with db_engine.connect() as db_conn:
    for cmd in db_schema_cmds:
        db_conn.execute(text(cmd))

# Use automap to reflect the DB schema and generate ORM classes.
Base = automap_base()
Base.prepare(db_engine, reflect=True)

# Create aliases for the table classes generated.
User = Base.classes.users
Post = Base.classes.posts

session_factory = sessionmaker()
session_factory.configure(bind=db_engine)

# Add a user and a post to the DB.
session = session_factory()
new_user = User(name="John")
session.add(new_user)
session.commit()
new_post = Post(users=new_user, text='this is a test', timestamp=func.now())
session.add(new_post)
session.commit()

# Verify that the insertion worked.
new_user_id = session.execute(select([User])).fetchone()['u_id']
new_post_fk_user_id = session.execute(select([Post])).fetchone()['u_id']
assert new_user_id == new_post_fk_user_id

session.close()

Running this gives the following traceback:

Traceback (most recent call last):
  File "reproduce_InvalidRequestError.py", line 67, in <module>
    session.commit()
  File "C:\Python\Python35\lib\site-packages\sqlalchemy\orm\session.py", line 801, in commit
    self.transaction.commit()
  File "C:\Python\Python35\lib\site-packages\sqlalchemy\orm\session.py", line 392, in commit
    self._prepare_impl()
  File "C:\Python\Python35\lib\site-packages\sqlalchemy\orm\session.py", line 372, in _prepare_impl
    self.session.flush()
  File "C:\Python\Python35\lib\site-packages\sqlalchemy\orm\session.py", line 2019, in flush
    self._flush(objects)
  File "C:\Python\Python35\lib\site-packages\sqlalchemy\orm\session.py", line 2137, in _flush
    transaction.rollback(_capture_exception=True)
  File "C:\Python\Python35\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Python\Python35\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value
  File "C:\Python\Python35\lib\site-packages\sqlalchemy\orm\session.py", line 2107, in _flush
    flush_context.finalize_flush_changes()
  File "C:\Python\Python35\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 395, in finalize_flush_changes
    self.session._register_newly_persistent(other)
  File "C:\Python\Python35\lib\site-packages\sqlalchemy\orm\session.py", line 1510, in _register_newly_persistent
    instance_key = mapper._identity_key_from_state(state)
  File "C:\Python\Python35\lib\site-packages\sqlalchemy\orm\mapper.py", line 2417, in _identity_key_from_state
    for col in self.primary_key
  File "C:\Python\Python35\lib\site-packages\sqlalchemy\orm\mapper.py", line 2417, in <listcomp>
    for col in self.primary_key
  File "C:\Python\Python35\lib\site-packages\sqlalchemy\orm\attributes.py", line 578, in get
    value = state._load_expired(state, passive)
  File "C:\Python\Python35\lib\site-packages\sqlalchemy\orm\state.py", line 474, in _load_expired
    self.manager.deferred_scalar_loader(self, toload)
  File "C:\Python\Python35\lib\site-packages\sqlalchemy\orm\loading.py", line 647, in load_scalar_attributes
    "contain a full primary key." % state_str(state))
sqlalchemy.exc.InvalidRequestError: Instance <posts at 0x45d45f8> cannot be refreshed - it's not  persistent and does not contain a full primary key.

If I add an echo=True parameter to the create_engine call, I see it is generating the following SQL for the insertion. This SQL works fine when I run it in DB Browser for SQLite.

INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO sqlalchemy.engine.base.Engine SELECT users.u_id AS users_u_id, users.name AS users_name FROM users WHERE users.u_id = ?
INFO sqlalchemy.engine.base.Engine (1,)
INFO sqlalchemy.engine.base.Engine INSERT INTO posts (timestamp, text, u_id) VALUES (CURRENT_TIMESTAMP, ?, ?)
INFO sqlalchemy.engine.base.Engine ('this is a test', 1)
INFO sqlalchemy.engine.base.Engine ROLLBACK

I also tried removing the users parameter from Post() and instead adding the line new_user.posts_collection.append(new_post) before calling session.add(new_post), but that resulted in the same SQL being generated and the same error occurring.

If I replace the composite key with a new integer PK column, everything works fine. (Not an ideal solution though, as the reason I'm using automap is to reflect an existing DB, so it's preferable not to have to modify that DB's schema.)

I found a similar question, SQLAlchemy InvalidRequestError when using composite foreign keys, however that seemed to be related to the use of inheritance in the table ORM classes, and the solution depended on defining the ORM table classes, rather than reflecting a DB to generate them.

Edit: I had originally assumed that this problem was related to the fact that my composite primary key contained a foreign key. The accepted answer shows that the foreign key was not actually a contributing factor to the problem.

Answer 1

The problem is not actually the composite primary key with the foreign key, but the func.now() passed as timestamp, which is part of the primary key. As the value is not known to SQLAlchemy, since it is generated during insert in the database, it cannot perform the post-fetch; it has no idea what to fetch. If the DB in question supported RETURNING or similar, you'd be able to do this. See the note on triggered columns, which describes this exact situation. Pre-executing SQL for primary key values is also covered in Defaults / SQL Expressions.

The reason why it works with an integer surrogate primary key is that SQLite does have a mechanism for fetching the last inserted row id (an integer primary key column), which SQLAlchemy is able to use.

To remedy this you can use a timestamp generated in Python

In [8]: new_post = Post(users=new_user, text='this is a test',
   ...:                 timestamp=datetime.utcnow())
   ...: session.add(new_post)
   ...: session.commit()
   ...: 

The other solution would be to override the timestamp column during reflection and to provide func.now() as the default. This would trigger the pre-execution of func.now().

   ...: # Use automap to reflect the DB schema and generate ORM classes.
   ...: Base = automap_base()
   ...: 
   ...: # Override timestamp column before reflection
   ...: class Post(Base):
   ...:     __tablename__ = 'posts'
   ...:     timestamp = Column(Text, nullable=False, primary_key=True,
   ...:                        default=func.now())
   ...: 
   ...: Base.prepare(db_engine, reflect=True)
   ...: 
   ...: # Create aliases for the table classes generated.
   ...: User = Base.classes.users
   ...: # Post has already been declared
   ...: #Post = Base.classes.posts

With the default in place, you do not need to (and shouldn't) provide timestamp when creating new instances

In [6]: new_post = Post(users=new_user, text='this is a test')
   ...: session.add(new_post)
   ...: session.commit()
   ...: