Python ORM Framework SQLAlchemy The relationship map instance of learning notes

Yesterday briefly introduced the use of SQLAlchemy, but did not involve its most exciting ORM part, today I will briefly explain, of course, mainly to explain the contents of official documents, because it is a learning note, there may be a simplification or self-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 (new version SQLAlchemy, the original modern SQLAlchemy) used declarative to do the two things together, allowing us to define the database tables for creating classes and descriptions and mapping relationships between them once.

What do you mean by this passage? To put it simply, SQLAlchemy is divided into classic (classical) and modern (modern), classic defines the schema of the database table is more traditional, need to describe this table first.

1. Classic Mapping

For example in the official documentation, we have the table structure as follows:
Copy the 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 the 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 described as finished, and then we need to define our Python class, like this:
Copy CodeThe code is as follows:

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

How we define the class that is mapped to the table structure we described earlier is what we will do next:
Copy CodeThe code is as follows:

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

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

This is the traditional method of defining ORM, more information about this method, you can read the document Mapper Configuration, later have the opportunity to discuss with you.

2. Modern mapping

When everyone is tired of defining the description table, defining the class, and then mapping to implement the ORM, the SQLAlchemy team has made a simpler mapping method, which is the modern pattern, that is, by defining the mapping class to complete all tasks at once.

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

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

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

Now we get the base class named base from the code we've just got, and through this base class we can define N-Multiple mapping subclasses, which can be managed by the SQLAlchemy declarative system.

Let's look at the example of the users table just now:
Copy the 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 " "% (Self.name, self.fullname, Self.password)

This code completes our previous three steps in classic, the code is simpler and easier to manage than it was originally, and the column defined by table in the classic, which represents the columns in the database table, and of course the integer and string represent the field type of the database table.

So the user class establishes 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 the ID, name, fullname, and password, of course, as we all know, we pass the Primary_ Key=true has indicated that the ID is the primary key. Of course some database tables may not contain primary keys (such as view views, of course the view can also be mapped), ORM in order to be able to actually map the table requires at least one column is defined as the primary key column. Multiple columns, such as composite multi-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 (constructor) and the __repr__ () string support method, which are optional, of course, If you need this class to join any of the many methods or properties required by the program, you can just think of this class as a normal Python class.

Of course the only thing the user can not be careless is that it must inherit to base, which is the class that we just generated by declarative_base (), through which we can then let SQLAlchemy The declarative system manages and operates these mapping classes and database tables.

In fact, including the inherited base class, all classes should be Python's modern class (New style Class), and more information about the new class can be found in the Python manual.

As our user mapping class is constructed successfully through the declarative system, we have the relevant definition information, such as the table () description in the classic definition, and the class mapped to the table, which is the user itself, which we can user.__table_ _ To view the description of our table:
Copy the Code code as follows:

>>> user.__table__
Table (' Users ', MetaData (None),
Column (' id ', Integer (), table= , Primary_key=true, Nullable=false),
Column (' name ', String (), table= ),
Column (' FullName ', String (), table= ),
Column (' Password ', String (), table= ), Schema=none)

Of course find the data structure of the description table, also should be able to find Mapper, our Mapper object can be obtained through __mapper__ properties, such as:
Copy CodeThe code is as follows:

>>> user.__mapper__

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

OK, let's take a moment to witness the miracle, do we need to define a good entity database and then define ORM? For SQLAlchemy, these are trivial, it can give you handedly, that is, you can completely ignore the database, to sqlalchemy on it, such as through the Metadata.create_all () And the engine parameters can be passed in (what is the engine?) Refer to my note 1), such as creating our users table in the following way. The
copy Code 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)
) br> ()
COMMIT

Because we opened the echo=true of the engine, we sqlalchemy the SQL statement out of the interactive command, just to verify that we are in compliance with our requirements.

This simple create_all () makes it easy to build a table of previous ORM mapping definitions.

It's not too early, let's talk about it today, next time we talk about other features of SQLAlchemy.

