Python ORM Framework SQLAlchemy The relationship mapping instance of learning notes _python

Yesterday briefly introduced the use of SQLAlchemy, but did not be able to relate to its most exciting ORM part, today I will briefly explain, of course, mainly to explain the content of the official document, because it is learning notes, there may be a simplification or understanding of the part, do not do authoritative basis.

When we start using ORM, a configurable structure can be used to describe our database tables, and the classes we define later will be mapped to those tables. Of course the modern SQLAlchemy (the new version SQLAlchemy, the original modern SQLAlchemy) used declarative to do the two things together, which allows us to create classes and descriptions that define database tables and the mapping relationships between them.

What does this passage mean? To put it simply, SQLAlchemy is divided into classic (Classic mode) and modern (modern mode), classic The pattern of defining database tables is more traditional and needs to be described first.

1. Classic Mapping

For example, in the official document, we have the table structure as follows:

Copy Code code as follows:

CREATE TABLE [Users] (
[id] INTEGER PRIMARY KEY,
[Name] TEXT not NULL,
[FullName] TEXT not NULL,
[Password] TEXT not NULL
);

Below we describe this table:

Copy Code code 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))
)

OK, so our table is complete, and then we need to define our Python class, like this:

Copy Code code as follows:

Class User (object):
def __init__ (self, name, fullname, password):
Self.name = Name
Self.fullname = FullName
Self.password = password

How to get our defined class mapped to the table structure described earlier is what we're going to do next:

Copy Code code as follows:

From Sqlalchemy.orm Import Mapper
Mapper (user, user)

You notice the Mapper function, the first argument is the name of our class, and the second parameter is the table definition we described earlier.

This is the traditional way to define ORM, for more information on this method, you can read the documentation Mapper Configuration, and then have the opportunity to discuss with you.

2. Modern mapping

When everyone is bored with defining a description table, defining classes, and mapping to implement ORM, the SQLAlchemy team comes up with a simpler mapping method, that is, the modern pattern, which is to complete all tasks at once by defining a mapping class.

In order to define a class that can be SQLAlchemy managed, the concept of declarative is introduced, meaning that all of our classes must be subclasses of declarative base classes, and this base class can be obtained by using the following method:

Copy Code code as follows:

From sqlalchemy.ext.declarative import declarative_base
Base = Declarative_base ()

Of course, within a program, this base class is best unique, and it is recommended that it be stored in global variables such as base for all mapping classes to use.

Now we've got the base class named base based on the code, and through the base class we can define N-ary mapping subclasses that can be managed by the SQLAlchemy declarative system.

Let's look at the example of the users table just now:

Copy Code code 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, and the code is simpler and easier to manage than the column defined in classic, which represents the columns in the database table, and of course integers and string represent the field types of the database tables.

This allows the user class to establish a mapping to the database table, the name of the real table can be specified using __tablename__, and then a collection of table columns, including ID, name, fullname, and password, and of course we know that we passed Primary_ Key=true already indicated the ID as the primary key. Of course, some database tables may not contain primary keys (such as view views, and of course the view can be mapped), ORM to be able to actually map the table requires at least one column to be defined as the primary key column. Multiple columns, such as composite multiple primary keys, can also be well mapped to support.

You may notice that the user class also contains the usual Python magic methods, including the __INIT__ () initialization class (construction method) and the __repr__ () string support method, which is optional, If you want this class to be able to add any number of methods or attributes required by the program, you can just think of the class as a generic Python class.

Of course the only thing that the user class can't be sloppy about is having to inherit to base, which is the class we just generated through declarative_base () that we can then let SQLAlchemy The declarative system manages and operates these mapping classes and database tables.

Actually includes the inherited base class, all classes should be Python's new Class (New style Class), and more information about new classes can be referenced in the Python manual.

As our user mapping class succeeds through the declarative system construct, we have the relevant definition information, such as the table () description described in the classic definition, and the class that maps to the table, that is, the user itself, we can pass User.__table_ _ To view our table description:

Copy Code code 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, find the data structure that describes the table, also should be able to find Mapper, our Mapper object can be obtained by __mapper__ attribute, such as:

Copy Code code as follows:

>>> user.__mapper__
<mapper at 0x ...; User>

The same metadata can be found by the. Metadata property.

Okay, let's take it easy, witness the miracle moment, do we need to define creating a good entity database and then defining ORM? For SQLAlchemy, these are trivial, they can give you single-handedly, that is, you can completely ignore the database, to the sqlalchemy can be, such as through the Metadata.create_all () and pass the engine parameter (what is engine?). Refer to my Notes 1, such as creating our users table in the following way.

Copy Code code 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

Because we have opened the engine echo=true, so under the interactive command sqlalchemy The SQL statement also output, just can test whether meet our requirements.

This simple Create_all () makes it easy to create a table with the previous ORM mapping definition.

It's late, today we'll talk about the other features of the SQLAlchemy next time.