Python ORM framework SQLAlchemy learning notes ing instance

Yesterday, I briefly introduced the use of SQLAlchemy, but it did not cover its most exciting ORM part. Today I will briefly explain it. Of course, I will mainly explain the content of the official document, there may be some simplified or self-understood parts without authoritative justification.

When we start to use ORM, a configurable structure can be used to describe our database tables. The classes we define will be mapped to these tables later. Of course, the modern SQLAlchemy (the new version of SQLAlchemy, the original version is modern SQLAlchemy) uses Declarative to do these two things together, this allows us to create classes, describe database tables, and map between them at one time.

What does this passage mean? To put it simply, SQLAlchemy can be divided into Classic (Classic mode) and Modern (Modern mode). Classic defines the traditional database table mode and needs to be described first.

1. Classic ing

For example, in the official document, we have the following table structure:
Copy codeThe Code is as follows:
Create table [users] (
[Id] integer primary key,
[Name] text not null,
[Fullname] text not null,
[Password] TEXT NOT NULL
);

The following table is described:
Copy codeThe Code is as follows:
From sqlalchemy import Table, MetaData, Column, Integer, String

Metadata = MetaData ()

User = Table ('users', metadata,
Column ('id', Integer, primary_key = True ),
Column ('name', String (50 )),
Column ('fullname', String (50 )),
Column ('Password', String (12 ))
)
Well, our table is completely described. Next we need to define our Python class, for example:
Copy codeThe Code is as follows:
Class User (object ):
Def _ init _ (self, name, fullname, password ):
Self. name = name
Self. fullname = fullname
Self. password = password
The following describes how to map the defined class to the previously described table structure:
Copy codeThe Code is as follows:
From sqlalchemy. orm import mapper
Mapper (User, user)
We have noticed that the first parameter of the mapper function is the name of our class, and the second parameter is the table definition we described earlier.

This is the traditional method for defining ORM. For more information about this method, read the Mapper Configuration document and discuss it with you later.

2. Modern ing

When everyone is happy to define the description table, define classes, and map to implement the ORM, The SQLAlchemy team has developed a simpler ing method, that is, the Modern mode, that is, all tasks are completed at one time by defining the ing Class.

To ensure that the defined classes can be managed by SQLAlchemy, the Declarative concept is introduced. That is to say, all our classes must be subclasses of the Declarative base classes, the base class can be obtained through the following method:
Copy codeThe Code is as follows:
From sqlalchemy. ext. declarative import declarative_base
Base = declarative_base ()
Of course, this Base class should be unique in a program. We recommend that you store it in global variables such as Base for all ing classes.

Now we get the Base class named Base through the Code just now. With this Base class, we can define N-plus ing subclasses, which can be managed by the SQLAlchemy Declarative system.

The following is an example of the users table:
Copy codeThe Code is as follows:
From sqlalchemy import Column, Integer, String
Class User (Base ):
_ Tablename _ = 'users'

Id = Column (Integer, primary_key = True)
Name = Column (String)
Fullname = Column (String)
Password = Column (String)

Def _ init _ (self, name, fullname, password ):
Self. name = name
Self. fullname = fullname
Self. password = password

Def _ repr _ (self ):
Return "<User ('% s',' % s', '% s')>" % (self. name, self. fullname, self. password)
This code completes the three steps we previously needed in Classic. The code is simpler and easier to manage than previously, just like the Column defined in Table in Classic, this represents the columns in the database table. Of course, Integer and String represent the field types in the database table.

In this way, the User class establishes a ing with the database table. The real table name can be indicated by _ tablename _, followed by a set of table columns, including id, name, fullname, and password, of course, we already know that we have specified id as the primary key through primary_key = True. Of course, some database tables may not contain primary keys (for example, View, and View can also be mapped). In order to be able to map tables, at least one column needs to be defined as a primary key column. Multiple columns, such as Composite multiple primary keys, can be well mapped and supported.

You may notice that the User class also contains Python magic methods in the general sense, including _ init _ () initialization class (constructor) and _ repr __() string-based methods are optional. If you need this class, you can add any number of methods or attributes required by the program, you only need to regard this class as a normal Python class.

Of course, the only thing that the User class cannot be careless is that it must be inherited to the Base. This Base is the class we just generated through declarative_base, through this, we can manage and operate these ing classes and database tables in the SQLAlchemy Declarative system.

In fact, it includes inherited Base classes. All classes should be new style classes of Python. For more information about new classes, see the Python manual.

As our User ing Class is successfully constructed through the Declarative system, we have the relevant definition information, such as the Table () Description introduced in the Classic definition, it also contains the class mapped to the table, that is, the User itself. We can use the User. _ table _ to view the table description:
Copy codeThe Code is as follows:
>>> User. _ table __
Table ('users', MetaData (None ),
Column ('id', Integer (), table = <users>, primary_key = True, nullable = False ),
Column ('name', String (), table = <users> ),
Column ('fullname', String (), table = <users> ),
Column ('Password', String (), table = <users>), schema = None)
Of course, you can also find mapper to describe the table's data structure. Our Mapper object can be obtained through the _ mapper _ attribute, for example:
Copy codeThe Code is as follows:
>>> User. _ mapper __
<Mapper at 0x...; User>
The same MetaData can be found through the. metadata attribute.

Well, let's just take a look at the miracle. Do we need to define the creation of an object database before defining an ORM? For SQLAlchemy, these are all minor issues. They can all be done for you. That is to say, you can leave the database alone and hand it over to SQLAlchemy, for example through MetaData. create_all () and pass in engine parameters (what is engine? Refer to my note 1). For example, create an users table using the following method.
Copy codeThe Code is as follows:
>>> Base. metadata. create_all (engine)
PRAGMA table_info ("users ")
()
Create table users (
Id integer not null,
Name VARCHAR,
Fullname VARCHAR,
Password VARCHAR,
Primary key (id)
)
()
COMMIT
Since echo = True of engine is enabled, SQLAlchemy outputs SQL statements in interactive commands to check whether the SQL statements meet our requirements.

With this simple create_all (), we can easily create a table defined by the previous ORM ing.

It's not too early. Let's talk about it today and talk about other features of SQLAlchemy next time.