Introduction to data addition and transaction rollback for the Python ORM framework SQLAlchemy learning notes

  • 2020-04-02 13:43:02
  • OfStack

1. Add a new object

If we want to Persist it (Persist), we need to add the object instance created by the User class to the Session instance we created earlier:


ed_user = User('ed', 'Ed Jones', 'edspassword')
session.add(ed_user)

Did the object persist after the above two pieces of code were executed? You might rush off to the database, only to be disappointed -- there's nothing in the database. Why? Because SQLAlchemy follows the Lazyload policy, this means that the object is now marked as Pending, but no SQL statements are executed that might cause the database to change. So when is the SQL statement executed and actually persisted? This waits until SQLAlchemy feels the need, such as when we are now querying the object, an attribute of the object, or explicitly calling the flush method, when SQLAlchemy feels that it is "time" or "forced" to execute an SQL database query in order to write data marked Pending to a database table. If at this point you perform an operation to get an object, an object property, or something similar, SQLAlchemy will feed you the data you want to query after executing the SQL statement.


In order to better illustrate this point, give you an example here, here comes to our first Query examples, we call the Query object to help us achieve these, here we get just the persistence of user Ed, for example, we through the way of "filter (filter by)" to Query the user named Ed users, of course, we only need one Ed, if there are multiple namesake, Ed, the Query will return all call Ed record set list, we will choose the first Ed. (first).


>>> our_user = session.query(User).filter_by(name='ed').first() 
BEGIN (implicit)
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('ed', 'Ed Jones', 'edspassword')
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name = ?
 LIMIT ? OFFSET ?
('ed', 1, 0)
>>> our_user
<User('ed','Ed Jones', 'edspassword')>

You can see that the query above returns an instance of User that we persisted earlier. At the same time as we specify the echo of the engine = True, so again output when performing a query SQL statement, we noticed that in addition to the normal SELECT, there will be additional INSERT statement, and INSERT processing is we just through the session. The add () persistence is marked as the object of the Pending, which means you in the actual operation of the persistent data will only be triggered by delay loading (the lazyload) real database operations.

In fact, the User object that Session query feedback to us is the same object as the object we just persisted, which can be verified by the following code:


>>> ed_user is our_user
True

Here actually ORM operation concept is a bit similar to identity mapping (identity map), that is to say, in the real database before erecting a identity mapping table, can be viewed as a cache table, any store database object will remain in this list in advance, if we want to query an object will query this identity mapping table in advance, if the object is directly taken out, otherwise will query the database entities, I think this is a bit like cache, so to understand.

< img SRC = "border = 0 / / files.jb51.net/file_images/article/201406/2014610105032669.png? 2014510105043 ">
Once an object with a unique primary key is persisted by Session, all objects that are queried on the same Session using that primary key will be the same Python object. Of course, persisting another object with the same primary key in this session will throw an exception error (the primary key cannot be repeated).

If we want to add more than one object to the Session at once, we can call add_all() :


>>> session.add_all([
...     User('wendy', 'Wendy Williams', 'foobar'),
...     User('mary', 'Mary Contrary', 'xxg527'),
...     User('fred', 'Fred Flinstone', 'blah')])

Now let's talk about changing it. If Ed feels that his password is not safe and decides to change it, he can do so directly:

>>> ed_user.password = 'f8s7ccs'

By the same token, this change is not immediately reflected in the database. Of course, the Session is aware that you are going to change the password of Ed, and it will temporarily buffer the change.

>>> session.dirty
IdentitySet([<User('ed','Ed Jones', 'f8s7ccs')>])

Similarly, we can "peek" at the list of objects persisted with add_all() using the new method:

>>> session.new  
IdentitySet([<User('wendy','Wendy Williams', 'foobar')>,
<User('mary','Mary Contrary', 'xxg527')>,
<User('fred','Fred Flinstone', 'blah')>])

Of course, none of these changes are actually fed back into the database and are effectively buffered by the ORM. Next, we can explicitly call commit() to tell the Session, "we have added or changed so much that we can commit to the database" :

>>> session.commit()
UPDATE users SET password=? WHERE users.id = ?
('f8s7ccs', 1)
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('wendy', 'Wendy Williams', 'foobar')
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('mary', 'Mary Contrary', 'xxg527')
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('fred', 'Fred Flinstone', 'blah')
COMMIT

So the buffered data or changes are all flushed to the database as a transaction, which we can also see through the output SQL statement.

After this operation is completed, the database connection resource referenced by the Session will be recycled to the connection pool, and any subsequent operation on this Session will trigger a new Transaction, which will, of course, request the database connection resource again from the connection pool.

As mentioned in the previous article, the id of the User object of Ed is None. Now let's take a look:


>>> ed_user.id 
BEGIN (implicit)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.id = ?
(1,)
1

Except for the SQL statement that is output due to echo=True, see if there is a value, which is 1.

Whether it is immediately (commit, flush) or through "load-on-first-access", all newly generated identifiers and database-generated default values are accessible to the instance after the Session inserts a new record into the database.

When commit() is called, SQLAlchemy will flush all the data for the current transaction into the database.

2. Transaction rollback

This article and the articles in the same series are translated according to their own ideas. Okay, so I'm going to talk a little bit about transaction rollback, which is the same thing as transaction rollback for the database, which is that we undo the change after we've done something wrong.

Because sessions work as transactions, we can roll back previous changes. Next, let's make two changes that will be undone (rolled back) later. The first is to modify ed_user.name:


>>> ed_user.name = 'Edwardo'

The second is to add an "undesired" user, fake_user:

>>> fake_user = User('fakeuser', 'Invalid', '12345')
>>> session.add(fake_user)

By querying the current session, we can see that these two changes are flush with the current transaction:

>>> session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all() 
UPDATE users SET name=? WHERE users.id = ?
('Edwardo', 1)
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('fakeuser', 'Invalid', '12345')
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name IN (?, ?)
('Edwardo', 'fakeuser')
[<User('Edwardo','Ed Jones', 'f8s7ccs')>, <User('fakeuser','Invalid', '12345')>]

Well, here's the miracle moment, rolling back the business:

>>> session.rollback()
ROLLBACK
>>> ed_user.name 
BEGIN (implicit)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.id = ?
(1,)
u'ed'
>>> fake_user in session
False


We can see that the name of ed_user changes back to Ed, and that the user we didn't expect fake_user to be "kicked out" of the Session.

Finally, we can query the users whose username is in the [' Ed ', 'fakeuser'] range to make sure our change is valid:


>>> session.query(User).filter(User.name.in_(['ed', 'fakeuser'])).all()
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name IN (?, ?)
('ed', 'fakeuser')
[<User('ed','Ed Jones', 'f8s7ccs')>]

Ok, that's all for today, today we talked about adding objects and transaction rollbacks, more or less interlaced with some simple queries, and then we will introduce more complex query statements, stay tuned!


Related articles: