Python Learning Note sqlalchemy (eight)

Introduction to ORM and SQLAlchemy

The ORM full Name Object Relational Mapping, which translates to a relational mapping of objects. Simply put, ORM establishes a correspondence between the tables in the database and the classes in the object-oriented language. In this way, we want to manipulate the database, the table in the database or a record in the table can be done directly through the Operation class or class instance.

SQLAlchemy is one of the most well-known ORM tools in the Python community, designed for efficient and high-performance database access, enabling a complete enterprise-class persistence model.

Connect and create

Install SQLAlchemy:

[email protected]:~$ sudo pip3 install sqlalchemyThe directory ‘/home/cq/.cache/pip/http‘ or its parent directory is not owned by the current user and the cache has been disabled. Please check the permissions and owner of that directory. If executing pip with sudo, you may want sudo‘s -H flag.The directory ‘/home/cq/.cache/pip‘ or its parent directory is not owned by the current user and caching wheels has been disabled. check the permissions and owner of that directory. If executing pip with sudo, you may want sudo‘s -H flag.Collecting sqlalchemy  Downloading SQLAlchemy-1.2.2.tar.gz (5.5MB)    100% |████████████████████████████████| 5.5MB 115kB/s Installing collected packages: sqlalchemy  Running setup.py install for sqlalchemy ... doneSuccessfully installed sqlalchemy-1.2.2

Also, you need to install a driver between Python and MySQL:

apt-get install python-mysqldbpip3 install mysqlclient

Connecting to a database
Create a py file to write the following content:

#coding=utf-8from sqlalchemy import create_engineengine = create_engine(‘mysql+mysqldb://root:@localhost:3306/blog‘)engine.execute(‘select * from user‘).fetchall()print(engine)

In the above program, we connected a blog database that runs in MySQL on port 3306 by default.
First, the create_engine is imported, which is used to create an engine instance, and the parameters passed to Create_engine define the access address of the MySQL server in the form of Mysql://<user>:<password >@

Object Relational Mapping

To use ORM, we need to describe the structure of the data table in ORM language. Sqlalchmey provides a set of declarative systems to accomplish this task. Let's take the example of creating a users table and see how it is described in SQLAlchemy language:

#coding=utf-8from sqlalchemy import create_engine,Column,String,Text,Integerfrom sqlalchemy.ext.declarative import declarative_baseengine = create_engine(‘mysql+mysqldb://root:@localhost:3306/blog‘)Base = declarative_base()class User(Base):    __table__ = ‘user‘    id = Column(Integer,primary_key=True)    username = Column(String(64),nullable=False,index=True)    password = Column(String(64),nullable=False)    email = Column(String(64),nullable=False,index=True)    def __repr__(self):        return ‘%s(%r)‘ % (self.__class__.__name__,self.username)Base.metadata.create_all(engine)

If you want to map the Python class to a database table, you need to create the class based on the SQLAlchemy declarative base class, which is the declaration base classes. When you create a Python class based on this base class, it is automatically mapped to the appropriate database table. Create a declaration base class, which you can do with the Declarative_base method

from sqlalchemy.ext.declarative import declarative_baseengine = create_engine(‘mysql+mysqldb://root:@localhost:3306/blog‘)Base = declarative_base()

In the User class, use tablename to specify the name of the table in MySQL. We created three basic fields, each of which represents a column in the database, and in COLUNM, specifies some of the configuration for that column. The first field represents the data type of the class, and above we use String, the two most common types of integers, and other commonly used ones include:

• Text
• Boolean
• Smallinteger
• Datetime
  Nullable=false represents this column cannot be empty, and index=true represents the index that is created in the column.
  Another definition of repr is to facilitate debugging, you can not define, you can define more detailed.
  Run the program, the program will not have output information, but SQLAlchemy has created the users table for us in the MySQL database.
  At this point, the User has a table property that records the defined table information

In [1]: from sql import UserIn [2]: User.__table__Out[2]: Table(‘users‘, MetaData(bind=None), Column(‘id‘, Integer(), table=<users>, primary_key=True, nullable=False), Column(‘username‘, String(length=64), table=<users>, nullable=False), Column(‘password‘, String(length=64), table=<users>, nullable=False), Column(‘email‘, String(length=64), table=<users>, nullable=False), schema=None)

One-to-many relationships

For a common blog application, the user and the article is obviously a one-to-many relationship, an article belongs to a user, a user can write many articles, then the relationship between them can be defined as:

from sqlalchemy import ForeignKeyfrom sqlalchemy.orm import relationshipfrom sqlalchemy import Column, String, Integer, Textclass User(Base):    __tablename__ = ‘users‘    id = Column(Integer, primary_key=True)    username = Column(String(64), nullable=False, index=True)    password = Column(String(64), nullable=False)    email = Column(String(64), nullable=False, index=True)    articles = relationship(‘Article‘)    def __repr__(self):        return ‘%s(%r)‘ % (self.__class__.__name__, self.username)class Article(Base):    __tablename__ = ‘articles‘    id = Column(Integer, primary_key=True)    title = Column(String(255), nullable=False, index=True)    content = Column(Text)    user_id = Column(Integer, ForeignKey(‘users.id‘))    author = relationship(‘User‘)    def __repr__(self):        return ‘%s(%r)‘ % (self.__class__.__name__, self.title)

Each article has a foreign key that points to the primary key ID in the users table, and the relationship describes the relationship in user using the SQLAlchemy provided. The relationship between the user and the article is bidirectional, so we see that the two tables above define the relationship.

The Created articles table has a foreign key userid, which you can use to set the foreign key in SQLAlchemy. ForeignKey. After you set the foreign key, it is convenient to be able to access the records in the corresponding users table directly from the articles instance, which can be achieved through relationship. The above code defines the author property through relationship so that the corresponding user record can be obtained directly from the Articles.author.

SQLAlchemy provides backref so that we can just define a relationship:
articles = relationship(‘Article‘, backref=‘author‘)
Added this can no longer define the relationship in the article!

One-to-one relationship
In user we have defined only a few required fields, but usually the user has a lot of other information, but this information may not be required, we can put them in another UserInfo table, so that user and UserInfo form a one-on relationship. You might wonder why one-to-many relationships aren't in front of a one-to-one relationship? That's because a one-to-many relationship is based on a pair of multiple definitions:

class User(Base):    __tablename__ = ‘users‘    id = Column(Integer, primary_key=True)    username = Column(String(64), nullable=False, index=True)    password = Column(String(64), nullable=False)    email = Column(String(64), nullable=False, index=True)    articles = relationship(‘Article‘, backref=‘author‘)    userinfo = relationship(‘UserInfo‘, backref=‘user‘, uselist=False)    def __repr__(self):        return ‘%s(%r)‘ % (self.__class__.__name__, self.username)class UserInfo(Base):    __tablename__ = ‘userinfos‘    id = Column(Integer, primary_key=True)    name = Column(String(64))    qq = Column(String(11))    phone = Column(String(11))    link = Column(String(64))    user_id = Column(Integer, ForeignKey(‘users.id‘))

Defining the method is the same as a one-to-many, just adding uselist=false.
It is important to note that when you define the Users property, you use the relationship backref parameter, which allows you to access all the user information associated with Userinfos.user in the UserInfo instance.

Many-to-many relationships
Once a blog usually has a category, several tags. There is a many-to-many relationship between tags and blogs. Many-to-many relationships cannot be defined directly and need to be decomposed into two one-to-many relationships, which requires an additional table to assist with, and usually the auxiliary table for this many-to-many relationship does not create a class, but instead uses the SQLAlchemy table class:

  # Imports from sqlalchemy import Tablearticle_tag = table on the original code (# The first parameter is the name of the sheet and the second parameter is metadata, which is required, Base.metad    ATA is a Sqlalchemy.schema.MetaData object that represents all the Table object collections, and Create_all () fires the CREATE TABLE statement creating all the tables. ' Article_tag ', Base.metadata, # for auxiliary tables, the IDs of the two tables to be associated are generally stored, and the foreign key #course_tag is a two-primary key, and the purpose of the dual primary key is to constrain the occurrence of duplicate pairs of primary key records, most of which should be    Used in this many-to-many intermediate table. Column (' article_id ', Integer, ForeignKey (' articles.id '), primary_key=true), column (' tag_id ', Integer, ForeignKey (' Tags.id '), primary_key=true)) class Tag (Base): __tablename__ = ' tags ' id = Column (Integer, primary_key=true) name = Column (String (+), Nullable=false, index=true) articles = relationship (' articles ', secon Dary=article_tag, backref= ' tages ') #secondary refers to the middle table, and backref points to the table Def __repr__ (self ): Return '%s (%r) '% (self.__class__.__name__, self.name)  

Map to Data
The table is already described, using the following command at the end of the file to create the corresponding table in our connected database:

if __name__ == ‘__main__‘:    Base.metadata.create_all(engine)

View MySQL:

mysql> show tables;+----------------+| Tables_in_blog |+----------------+| article_tag    || articles       || tags           || userinfos      || users          |+----------------+5 rows in set (0.00 sec)

Python Learning Note sqlalchemy (eight)