Introduction to data addition and transaction rollback in PythonORM framework SQLAlchemy learning notes

This article describes how to add data and roll back transactions in the PythonORM framework SQLAlchemy learning notes. For more information, see 1. Add a new object

Previously, we introduced the User ing Class User to object tables. If we want to Persist ), then we need to add the object instance created by the User class to the Session instance we created earlier:

The Code is as follows:

Ed_user = User ('ed', 'ed Jones ', 'edspassword ')
Session. add (ed_user)

After the above two pieces of code are executed, is the object persistent? You may rush to the database to view the results, but you are disappointed with the results-there is nothing in the database. Why? Because SQLAlchemy adopts the Lazyload policy, that is, the object is marked as Pending preparation, but no SQL statements that may cause database changes are executed. So when will the SQL statement be executed and truly persistent? This should be done when SQLAlchemy thinks it is necessary. For example, we can query an attribute of this object or explicitly call the flush method, at this time, SQLAlchemy thinks it is "time" or "has to" Execute SQL database queries to facilitate writing data marked as Pending into the database table. If you get the object, object attributes, or similar operations, SQLAlchemy will report the data you want to query after executing the SQL statement.

To better illustrate this point, here is an example, which involves our first Query example. We call the Query object to help us complete this, for example, here we get the persistent user ed. We use the filter by method to query the user whose user name is ed. Of course, we only need one ed, if there are multiple duplicate names of ed, the query will return a list of all records called ed. We will select the first ed (first ).

The Code is as follows:

>>> 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

We can see that the preceding query statement returns a User instance, which is exactly what we previously persisted. At the same time, because echo = True of the engine is specified, the SQL statement is output when the query is executed. We note that there are additional INSERT statements in addition to normal SELECT statements, the INSERT operation is just passed the session. add () Objects persistently marked as Pending, that is, when you operate on persistent data, lazyload will trigger database operations.

In fact, the User object returned by the Session query is the same as the object we just persisted. The following code can be used to test the problem:

The Code is as follows:

>>> Ed_user is our_user
True

In fact, the concept of ORM operations is a bit similar to identity map. That is to say, an identity ing table is set up before the real database and can be considered as a cache table, any objects stored in the database will stay on this table in advance. If we want to query an object, we will query this ID ing table in advance. If this object exists, we will retrieve it directly, otherwise, the database will be queried. I think this is a bit like a cache, so I can understand it.

Once an object with a unique primary key is persistent by the Session, all objects that use the primary key to query on the same Session will be the same Python object. Of course, if another object with the same primary key persists in this session, an exception will be thrown (the primary key cannot be repeated ).

If we want to add multiple objects to the Session at a time, we can call add_all ():

The Code is as follows:

>>> Session. add_all ([
... User ('wendy ', 'wendy Williams', 'foobar '),
... User ('Mary ', 'Mary Contrary', 'xxg527 '),
... User ('fred ', 'fred Flinstone', 'blah')])

Next, let's talk about the change. If Ed thinks his password is not safe and decides to change it, he can do this directly:

The Code is as follows:

>>> Ed_user.password = 'f8s7ccs'

In the same way, this change will not be immediately reflected in the database. Of course, the Session realizes that you want to modify the Ed password, and it will temporarily buffer this change, we can learn about our changes through the dirty method:

The Code is as follows:

>>> Session. dirty
IdentitySet ([ ])

Similarly, we can use the new method to "View" the list of objects that were previously persisted with add_all:

The Code is as follows:

>>> Session. new
IdentitySet ([ ,
,
])

Of course, these changes are not actually fed back to the database, which is equivalent to being buffered by the ORM. Next, we can explicitly call commit () to tell the Session: "We have added or changed so many times that we can submit the database ":

The Code is as follows:

>>> 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

As a result, all the buffered data or changes are flushed to the database as a transaction at a time. We can also see through the output SQL statement.

After this operation is completed, the database connection resources referenced by the Session will be recycled to the connection pool. Any subsequent operations on this Session will trigger a new Transaction ), of course, I will apply for database connection resources again with the connection pool.

The previous article introduced that the id of the User object of Ed is None. Now let's take a look:

The Code is as follows:

>>> 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

Besides the SQL statement output due to echo = True, check whether the value is 1.

Whether it is immediate (commit, flush) or by "load-on-first-access", after the Session inserts a new record in the database, all newly generated identifiers and default values generated by the database can be accessed by the instance.

After commit () is called, SQLAlchemy refreshes all data of the current transaction to the database.

2. Transaction rollback

This article and the articles in the same series are translated based on your own ideas. If you are not doing anything about it, please correct it and make no authoritative reference. Now, let me briefly introduce transaction rollback. In fact, this is the same as Database Transaction rollback, that is, we need to cancel the previous change after doing something wrong.

Because the Session works as a transaction, we can roll back the previous changes. Next, let's make two changes that will be undone (rolled back) later. The first one is to modify ed_user.name:

The Code is as follows:

>>> Ed_user.name = 'edwardo'

The second is to add an "unexpected" User fake_user:

The Code is as follows:

>>> Fake_user = User ('fakeuser', 'invalid', '123 ')
>>> Session. add (fake_user)

Query the current session. We can see that these two changes have been flushed into the current transaction:

The Code is as follows:

>>> 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', '20140901 ')
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 ')
[ , ]

Okay, next is the time to witness the miracle. Let's roll back the transaction:

The Code is as follows:

>>> 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 is changed back to ed, and the unexpected user fake_user is "kicked out" Session.

Finally, we can query users whose user names are in the range of ['ed', 'fakeuser'] to ensure that our changes are valid:

The Code is as follows:

>>> 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 ')
[ ]

Now, we are here today. Today we have explained how to add objects and roll back transactions, and more or less interspersed with some simple queries. Next we will introduce more complex query statements, so stay tuned!