SQLAlchemy InvalidRequestError when inserting to automap generated ORM
<p>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.</p>

<p>Here's some minimal example code that reproduces the problem:</p>

<pre><code>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()
</code></pre>

<p>Running this gives the following traceback:</p>

<pre><code>Traceback (most recent call last):
  File "reproduce_InvalidRequestError.py", line 67, in &lt;module&gt;
    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 &lt;listcomp&gt;
    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 &lt;posts at 0x45d45f8&gt; cannot be refreshed - it's not  persistent and does not contain a full primary key.
</code></pre>

<p>If I add an <code>echo=True</code> parameter to the <code>create_engine</code> 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.</p>

<pre><code>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
</code></pre>

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

<p>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 <code>automap</code> is to reflect an <em>existing</em> DB, so it's preferable not to have to modify that DB's schema.)</p>

<p>I found a similar question, <a href="http://stackoverflow.com/questions/29238260/sqlalchemy-invalidrequesterror-when-using-composite-foreign-keys">SQLAlchemy InvalidRequestError when using composite foreign keys</a>, 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.</p>

<p>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.</p>

<p>The problem is not actually the composite primary key with the foreign key, but the <code>func.now()</code> passed as <code>timestamp</code>, 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 <code>RETURNING</code> or similar, you'd be able to do this. See the note on <a href="http://docs.sqlalchemy.org/en/latest/core/defaults.html#triggered-columns" rel="nofollow">triggered columns</a>, which describes this exact situation. Pre-executing SQL for primary key values is also covered in <a href="http://docs.sqlalchemy.org/en/latest/core/defaults.html#sql-expressions" rel="nofollow">Defaults / SQL Expressions</a>.</p>

<p>The reason why it works with an integer surrogate primary key is that SQLite does have a mechanism for <a href="http://www.sqlite.org/c3ref/last_insert_rowid.html" rel="nofollow">fetching the last inserted row id</a> (an integer primary key column), which SQLAlchemy is able to use.</p>

<p>To remedy this you can use a timestamp generated in Python</p>

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

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

<pre><code>   ...: # 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
</code></pre>

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

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

python sqlalchemy