I want to do something like this:
f = Foo(bar='x')
session.add(f)
session.flush()
# do additional queries using f.id before commit()
print f.id # should be not None
session.commit()
But f.id is None when I try it. How can I get this to work?
Thanks for everybody. I solved my problem by modifying the column mapping. For me, autoincrement=True is required.
origin:
id = Column('ID', Integer, primary_key=True, nullable=False)
after modified:
id = Column('ID', Integer, primary_key=True, autoincrement=True, nullable=True)
then
session.flush()
print(f.id)
is ok!
The core solution has been mentioned in other much older answers, but this uses newer async API.
with sqlalchemy==1.4 (2.0 style), following seems to work:
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.ext.asyncio import create_async_engine
engine = create_async_engine(
"postgresql+asyncpg://user:pass@localhost/db",
echo=False,
)
# expire_on_commit=False will prevent attributes from being expired
# after commit.
async_session = sessionmaker(
engine, expire_on_commit=False, class_=AsyncSession,
)
# default kwarg autoflush=True
async with async_session() as session:
async with session.begin():
f = Foo(bar='x')
session.add(f)
print(f.id)
# None
await session.flush()
print(f.id)
# not None
# commits transaction, closes session
The last couple of hours/days/whatever, I was trying to get the above suggestions to work. Initially, I wrote all my insert functions like so:
_add = User(id, user_name, email, ...)
Where all the items between the round brackets are variables for None, "user a", "a@example.com", ...
This is my User table:
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, autoincrement=True)
user_name = Column(String(50), unique=True, nullable=False)
email = Column(String(100), unique=True, nullable=False)
SQLAlchemy handles the _add query correctly, as it inserts the record with an autoincremented ID. Also, as should be, no default value is set for the id column.
I have tried all the above options in various ways (with/without commit, with/without flush, with/without refresh, the one before the other, timeouts in between statements, you name it). I even changed the whole app/database interaction a couple of times. But in all occasions, "_add.id" would either return 0, or something like "Instance '' has been deleted, or its row is otherwise not present."
Just now I thought "maybe I should write my _add query a bit different, by also defining the column names for the specified table" like so:
_add = User(id=None, user_name=user_name, email=email, etc)
To emphasize, note: id=, user_name=, email=, in the _add query. Now, with the following statements in this order, SQLAlchemy does return the inserted ID!
session.add(_add)
print(_add.id) <-- returns None
session.flush() <-- does **not** insert record into database, but does increment id,
waiting to be committed. Flush may be omitted, because
session.commit() unconditionally issues session.flush()*
print(_add.id) <-- returns incremented id
session.commit() <-- commit is needed to actually insert record into database
print(_add.id) <-- returns incremented id
Although the answer has been provided, it wasn't clear for me the missing column names in the _add query, and thus my laziness, were the cause of my problems. I hope this can help someone avoid the same troubleshoot...
my code works like that:
f = Foo(bar="blabla")
session.add(f)
session.flush()
session.refresh(f, attribute_names=[columns name that you want retrieve]
# so now you can access the id inserted, for example
return f.id # id inserted will be returned