1. Add a new Object
The mapping class user mapped to the Entity table is described earlier, and if we want to persist it (Persist), then we need to add the object instance created by the user class to the session instance of our previous creation:
Copy Code code as follows:
Ed_user = User (' Ed ', ' Ed Jones ', ' Edspassword ')
Session.add (Ed_user)
Does the last two pieces of code finish after the object is persisted? You may be excitedly running to the database to view, the result is disappointed and return--there is nothing in the database. Why, then? Because SQLAlchemy takes the lazyload policy, that is, the object is now marked as pending ready, but it does not execute any SQL statements that might cause the database to change. So when do you execute SQL statements and really persist? This has to wait for SQLAlchemy to feel the need, such as we now query this object, object of a property or explicit call flush method, this time sqlalchemy think it "is" or "have to" Execute SQL database queries to write data that is marked as pending to a database table. If you do get objects, object attributes, or similar operations at this time, SQLAlchemy will feed you the data you want to query after executing the SQL statement.
To better illustrate this point, here's an example of our first query example, where we call the query object to help us do this, for example, we get the just-persisted user Ed, and we query the user named Ed by the "Filter By" method, Of course we only need an ED, if there are more than one name of Ed, the query will return all the list of records called Ed, we choose the first Ed bar (a).
Copy Code code as follows:
>>> Our_user = session.query (user). Filter_by (name= ' Ed ').
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 above query statement returns an instance of user, which is exactly what we had previously persisted. And since we've specified the engine's echo=true, so we output the SQL statement when we execute the query, we notice that there's an extra INSERT statement in addition to the normal select, and the insert process is that we just passed Session.add () Persisting objects that are marked as pending, which means that you will actually trigger the database operation by deferred loading (lazyload) when you actually manipulate persisted data.
In fact, the session query feeds our user object and the object we just persisted is the same object, which can be verified by the following code:
Copy Code code as follows:
>>> Ed_user is Our_user
True
In fact, the ORM operation concept is somewhat similar to the identity map (identity map), which means that an identity mapping table is set up before the entity database, which can be considered as a cache table, and any object that stores the database will stay on this table beforehand, if we want to query an object, This identification map will be queried in advance, if the object exists to be taken directly, 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 that use the primary key to query on the same sessions will be the same Python object. Of course, if you persist another object with the same primary key in this session, you will throw an exception error (the primary key cannot be repeated).
If we want to add multiple objects at once to the session you can call Add_all ():
Copy Code code as follows:
>>> Session.add_all ([
... User (' Wendy ', ' Wendy Williams ', ' foobar '),
... User (' Mary ', ' Mary contrary ', ' xxg527 '),
... User (' Fred ', ' Fred Flinstone ', ' blah ')]
Next to talk about changes, if Ed felt his password is not very safe, decided to modify, you can do this directly:
Copy Code code as follows:
>>> Ed_user.password = ' F8s7ccs '
In the same way, this change is not immediately reflected in the database, of course, the session realized that you want to modify Ed's password, it will temporarily buffer the change, we can through the dirty method to understand our changes:
Copy Code code as follows:
>>> Session.dirty
Identityset ([<user (' Ed ', ' Ed Jones ', ' F8s7ccs ')]
Again, we can "peek" through the new method to the previously persisted list of objects using Add_all ():
Copy Code code as follows:
>>> 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, which is equivalent to being ORM buffered. We can then explicitly invoke commit () to tell the session: "We are currently adding or altering so much to submit a database":
Copy Code code 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
So just buffered data or changes are all as transactions one-time flush to the database, through the output of the SQL statement we can also see.
The database connection resources that are referenced by the conversation (session) After this operation will be reclaimed into the connection pool, and any subsequent actions for this sessions will trigger a new transaction (Transaction), and of course, the connection pool request to obtain the database connection resources.
Before the article introduced to Ed's user object ID is none, now let's take a look at it:
Copy Code code 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 due to echo=true, see if there is a value, the value is 1.
Either immediately (commit, flush) or through "first-access Load (load-on-first-access)", after the session inserts a new record in the database, all newly generated identities and database-generated defaults are accessible to the instance.
When a commit () is invoked, SQLAlchemy refreshes all the data in the current transaction into the database.
2. Transaction rollback
This article as well as the same series of articles are translated by their own ideas, improper place also please correct, do not do authoritative basis. Well, I'd like to briefly introduce the transaction rollback, in fact this and the database transaction rollback a meaning, we do wrong to undo before the change.
Because the session works as a transaction (transaction), we can rollback (roll back) previous changes. Next, let's do two changes that will be undone (rolled back), the first of which is to modify Ed_user.name:
Copy Code code as follows:
>>> ed_user.name = ' Edwardo '
The second is to add an "fake_user" User:
Copy Code code 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:
Copy Code code 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 ')
[<user (' Edwardo ', ' Ed Jones ', ' F8s7ccs '), <user (' Fakeuser ', ' Invalid ', ' 12345 ')
Well, here's the moment to witness the miracle, we roll back (rolling) transaction:
Copy Code code 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 Ed_user's name Turn back to Ed, and we don't expect the user fake_user to be "kicked out" session.
Finally, we can query the user name in the [' Ed ', ' Fakeuser '] range of users to ensure that our changes are valid:
Copy Code code 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 ')
[<user (' Ed ', ' Ed Jones ', ' F8s7ccs ')]
Well, today is here, today we explained the Add object and transaction rollback, more or less interspersed with some simple query, next we will introduce more complex query statements, please look forward to!
