A relational mapping example of the Python ORM framework SQLAlchemy learning notes

Yesterday, I briefly introduced the use of SQLAlchemy, but I failed to cover the most wonderful ORM part. Today, I will briefly explain it. Of course, I will mainly explain the content of the official document.

When we start using ORM, a configurable structure can be used to describe our database tables to which the classes we define later will be mapped. Of course, modern SQLAlchemy (the new version of SQLAlchemy) USES Declarative to do both things together, allowing us to create classes and descriptors that define database tables and the mapping between them all at once.

What does this passage mean? Simply put, SQLAlchemy is divided into Classic (Classic schema) and Modern (Modern schema), which is a more traditional schema for defining database tables that needs to be described first.

1. The Classic mapping

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

CREATE TABLE [users] (
  [id]       INTEGER PRIMARY KEY,
  [name]     TEXT NOT NULL,
  [fullname] TEXT NOT NULL,
  [password] TEXT NOT NULL
);

Here we describe the table:

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, now that our table is described, we need to define our Python class, like this:

class User(object):
    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

How to make the classes we defined map to the table structure described above is what we will do next:

from sqlalchemy.orm import mapper
mapper(User, user)

Notice that the mapper function, the first argument is the name of our class, and the second argument is the table definition we described earlier.

This is the traditional way to define an ORM, and for more information on this method, you can read the Mapper Configuration document and talk about it later.

2. Modern mapping

While everyone was happily defining the description table, defining the classes, and mapping them to implement ORM, the SQLAlchemy team came up with an even simpler mapping method, the Modern pattern, which defined the mapping classes to do everything at once.

In order for the defined classes to be managed by SQLAlchemy, the concept of Declarative is introduced, which means that all our classes must be subclasses of the Declarative base class, which can be obtained by:

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 that it be stored in global variables such as Base for all mapped classes.

Now we have a Base class called Base from which we can define n-plus mapping subclasses that can be managed by the SQLAlchemy Declarative system.

Now let's look at the example of the users table:

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 needed earlier in Classic, and it's much more concise and manageable than it was before, as well as the columns defined in Table in Classic, which represent the columns in the database Table, and of course Integer and String represent the field types of the database Table.

The User class then sets up a mapping to the database table, the name of the real table can be specified with successive tablename__, and then the set of table columns, including id, name, fullname, and password, as you already know, we've already specified the id as the primary key by primary_key=True. While some database tables may not contain primary keys (such as View View, which can also be mapped), ORM requires at least one column to be defined as a primary key column in order to actually map the table. Multiple columns, such as composite multiple primary keys, can also be well mapped.

You may have noticed that also included in the User class has generally Python magic methods, contains the __init__ () to initialize the class (method) and __repr__ () string support methods, of course, these are optional, if you need this class can join the program need any methods or properties, as long as you take this class as a regular Python class is ok.

Of course, the only thing the User class can't be sloppy about is that it must inherit to Base, which is the class we just generated via declarative_base(), through which we can then have the SQLAlchemy Declarative system manage and manipulate the mapped classes and database tables.

This actually includes the inherited Base class, all of which should be Python's new style class, and you can refer to the Python manual for more information about the new style class.

With the success of our User mapping class constructed using the Declarative system, we have the relevant definition information, such as the Table() description in the Classic definition, and also the class that maps to the Table, which is User itself.

>>> 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, if you find the data structure of the description table, you should also be able to find mapper. Our mapper object can be obtained by using the mapper__ property, such as this:

>>> User.__mapper__ 
<Mapper at 0x...; User>

The same MetaData can be found through the.metadata attribute.

Okay, so let's just relax and see if we need to define the entity database and then define the ORM? For SQLAlchemy, these are trivial things that you can do yourself, which means that you can completely ignore the database and hand over to SQLAlchemy, such as by metadata.create_all () and passing in the engine parameter (what is engine? Refer to my note 1), for example, to create our users table in the following way.

>>> 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 we turned on echo=True in the engine, SQLAlchemy printed out the SQL statement under the interactive command, just to verify that it met our requirements.

This simple create_all() makes it easy to set up the table defined earlier by the ORM map.

It's getting late, so I'll stop there for today and talk about the other features of SQLAlchemy next time.