Python ORM Framework SQLAlchemy Learn notes data addition and transaction rollback INTRODUCTION

1. Add a new Object

The mapping class mapped to the entity table is described earlier, user, if we want to persist it (Persist), then we need to add this object instance created by the user class to the session instance that we create earlier:

The code is as follows:

Ed_user = User (' Ed ', ' Ed Jones ', ' Edspassword ')
Session.add (Ed_user)

Is the object persisted after the above two pieces of code have been executed? You may be excitedly running to the database to see, but the result is disappointed--nothing in the database. Why is it? Because SQLAlchemy takes the lazyload policy, which means that the object is now marked as pending ready, but does not perform any SQL statements that could cause changes to the database. So when will the SQL statement be executed and really persisted? This is to wait for SQLAlchemy to feel the need, such as we now query the object, a property of the object, or explicitly call the Flush method, when SQLAlchemy think it "is the time" or "have to" Executes a SQL database query to write data marked as pending to the database table. If you do get objects, object properties, or similar operations at this time, SQLAlchemy will give you the data you want to query after executing the SQL statement.

To better illustrate this, here's an example of our first query example, we called the query object to help us do this, like here we get the user Ed who just persisted, and we use "Filter by" to query the user named Ed, Of course we only need an ED, if there are multiple Ed with the same name, the query will return all the list of recordsets called Ed, and we'll choose the first Ed bar.

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

You can see that the above query statement returns an instance of user, and this instance is exactly what we persisted previously. At the same time, because we have specified the engine's echo=true, we then output the SQL statement when we execute the query, and we notice that there are additional INSERT statements in addition to the normal select, and the insert process is that we just passed Session.add () Persisting an object marked as pending, meaning that you actually trigger a database operation by a lazy load (lazyload) when the actual operation persists data.

In fact, the session query feedback to our user object and the object we just persisted is the same object, which can be verified by the following code:

The code is as follows:

>>> Ed_user is Our_user
True

In fact, the ORM operation concept is somewhat similar to the identity map, that is, to set up an identity mapping table before the entity database, can be regarded as one kind of cache table, any object that stores the database will stay on this table beforehand, if we want to query an object, Will query this identity map in advance, if the object exists directly out, otherwise it will query the entity database, I think this is a bit like the role of caching, you can understand it.

Once an object with a unique primary key is persisted by the session, all objects queried on the same session using that primary key will be the same Python object. Of course, the persistence of another object with the same primary key in this session will throw an exception (the primary key cannot be duplicated).

If we want to add multiple objects at once to the session you 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 talk about the changes, if Ed think his password is not too safe, decided to modify, you can do this directly:

The code is as follows:

>>> Ed_user.password = ' F8s7ccs '

Similarly, this change will not be immediately reflected in the database, of course, the session realizes that you want to change the Ed's password, it will temporarily buffer the change, we can use the dirty method to understand our changes:

The code is as follows:

>>> Session.dirty
Identityset ([ ])

Similarly, we can "peek" through the new method to a list of objects that were previously persisted with Add_all ():

The code is as follows:

>>> session.new
Identityset ([ ,
,
])

Of course, these changes are not really fed back into the database, equivalent to the ORM buffer. We can then explicitly call commit () to tell the session: "We are adding or altering so much that we can commit the database now":

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, the buffered data or changes are all flush to the database once as a transaction, and we can see the output SQL statement.

After this operation is completed, the database connection resource referenced by the session will be recycled into the connection pool, and any subsequent actions on this session will trigger a new transaction (Transaction), and will of course be used again to obtain the database connection resources from the connection pool request.

The previous article describes the ID of the user object for Ed to none, so let's take a look at it:

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

In addition to the SQL statement that caused the output by echo=true, see if there is a value of 1.

Either immediately (commit, flush) or through "First access load (load-on-first-access)", after the session inserts a new record into the database, all newly generated identities and database-generated defaults are accessible to the instance.

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

2. Transaction rollback

This article and the same series of articles are translated with their own ideas, the wrong place also please correct, do not do authoritative basis. OK, let me briefly introduce the transaction rollback, in fact, this and the database transaction rollback one meaning, is that we do wrong to undo the previous changes.

Because the session is working as a transaction (transaction), we can rollback the previous changes. Next, let's do two changes that will be undone (rolled back) later, and the first one is to modify Ed_user.name:

The code is as follows:

>>> ed_user.name = ' Edwardo '

The second one is to add an "unwanted" user fake_user:

The code is as follows:

>>> fake_user = User (' Fakeuser ', ' Invalid ', ' 12345 ')
>>> Session.add (Fake_user)

Querying the current session, we can see that these two changes have been flush 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 ', ' 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 ')
[ , ]

Well, here's the moment to witness the miracle, and we'll roll back (rolling) 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 Ed_user's name changes back to Ed, and we don't expect the user fake_user to be "kicked out" of the session.

Finally, we can check the user name in the [' Ed ', ' Fakeuser '] range of users, 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 ')
[ ]

Well, here today, today we explain the addition of objects and transaction rollback, more or less interspersed with some simple query, next we will introduce more complex query statements, please look forward to!

