# sqlalchemy Now, we can use the class to create a new record on our database. We can user the `User` class to create a new user and `session.add(...)` to add the instance to our database. Now, even though we said we needed a primary key, I didn’t pass one for the model. And if I try to print the id of the user I just created, it will return `None`. This is because `session.add` just register the transactions we want it to do, but it doesn’t actually do it [[3](https://stackoverflow.com/a/4202016/3538098)]. As explained on [this link](https://stackoverflow.com/a/4202016/3538098), we have two operations that can be done here: * `session.flush()` communicates a series of operations to the database (insert, update, delete). The database maintains them as pending operations in a transaction. The changes aren’t persisted permanently to disk, or visible to other transactions until the database receives a COMMIT for the current transaction (which is what `session.commit()` does). or * `session.commit()` commits (persists) those changes to the database. `session.commit()` always calls for `session.flush()` as part of it. ### Session, commit, flush, expire, etc 1. As an ORM, SQLAlchemy enables you to manipulate database records as Python objects. For example, a row in your users table would be represented as a `<User>` object, which has attributes, methods, and so on. 2. These objects are held in memory and need to be synchronised with its representation in your database at some interval, otherwise the in-memory representation differs from your persistent database record. 3. Sessions are a scope or context within which you can change these objects. Note that this does not necessarily mean any changes you make to the objects are (yet) synchronised back to the database. 4. Sessions have a natural lifecyle in which objects are first instantiated from the database record, changes are made to these objects, and then the changes are either persisted back to the database or discarded. ### 1. Expire * I've made some changes to an object and don't need it immediately but don't want any subsequent methods to use stale values. ### 2. Refresh * I've made some changes to an object and need its updated values immediately. * Costs extra database call as it expires and reads from database immediately. ### 3. Flush * Push changes from memory to your database's transaction buffer. No database statements are issued yet. * If Session has autocommit: False, must still call commit() to persist the changes or rollback() to discard changes. * If Session has autocommit: True and you are not explicitly in a transaction, commit() will be automatically called. ### 4. Commit * Persist changes in your database's transaction buffer to the database. Database statements are issued. * Automatically expires objects. ### 5. Merge * Used when you may have more than 1 in-memory objects which map to the same database record with some key. * Merging causes the in-memory objects to be synchronised with each other, does not necessarily persist to the database. --- Date: 20210622 Links to: References: * [Solid tutorial](https://leportella.com/sqlalchemy-tutorial/) * [Introduction to SQLAlchemy 2020 (Tutorial)" by: Mike Bayer](https://www.youtube.com/watch?v=sO7FFPNvX2s&t=3620s) * [SQLAlchemy commit(), flush(), expire(), refresh(), merge() - what's the difference?](https://michaelcho.me/article/sqlalchemy-commit-flush-expire-refresh-merge-whats-the-difference) * [What is currently in the session (new, dirty)](https://stackoverflow.com/questions/31928520/sqlalchemy-is-there-a-way-to-see-what-is-currently-in-the-session) * [Refreshing sqlalchemy session](https://stackoverflow.com/questions/19143345/about-refreshing-objects-in-sqlalchemy-session) * [SQL Alchemy session, by mike bayer](https://www.youtube.com/watch?t=806&v=uAtaKr5HOdA&feature=youtu.be)