1. A brief introduction

Recently I learned Tornado and found no ORM model. The TornDB module that comes with it does not support Python3. Learn about SQLAlchemy for future work.

In this article, I learned how to use SQLAlchemy to connect to MySQL database, create a data table needed by a blog application, and introduced how to use SQLAlchemy to do simple CURD operation and use Faker to generate test data.

1.1 Knowledge points

  • Learn to use SQLAlchemy to connect to database (MySQL, SQLite, PostgreSQL), create data tables;
  • Mastering the one-to-one, one-to-many and many-to-many relationships among table data and translating them into corresponding SQLAlchemy descriptions;
  • Master SQLAlchemy for CURD operation;
  • Learn to use Faker to generate test data

2. ORM and SQLAlchemy are briefly introduced

Object Relational Mapping (ORM) is an ORM Relational Mapping. In a nutshell, ORM matches tables in a database to classes in an object-oriented language. Thus, if we want to manipulate a database, a table in the database or a record in the table can be manipulated directly by manipulating a class or an instance of the class.

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

Next we will use SQLAlchemy and MySQL to build an experimental library for blogging applications.

3. Connection and creation

Installed SQLAlchemy

pip install sqlalchemy

Database we use MySQL, the installation process here omitted. Refer to my LNMP installation steps at http://www.jianshu.com/p/1e51985b46dd

Mysql > start mysql

systemctl start mysqld

Enter the database command line

mysql

Change database authorization so that remote hosts can access it

update mysql.user set Host='%' where HOST='localhost' and User='root';

Next we use the graphical database manipulation tool (Navicat Premium) to manipulate the database to create a database for the blog

Installing a database driver

pip install pymysql

3.1 Connection to database

Create a new file in db.py and write the following:

#! /usr/bin/env python # _*_ coding:utf-8 _*_ __author__ = 'junxi' from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base engine = create_engine('mysql+pymysql://blog:123456@localhost:3306/blog?charset=utf8') Base = declarative_base() print(engine)

Run:

Engine(mysql+pymysql://blog:***@localhost:3306/blog? charset=utf8)

3.2 Describe the table structure

To use ORM, we need to describe the structure of the data table in ORM language. SQLAlchmey provides a Declarative system to accomplish this task. Let’s take the example of creating a users table and see how it is described in SQLAlchemy language:

Edit the db. Py:

#! /usr/bin/env python # _*_ coding:utf-8 _*_ __author__ = 'junxi' from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, String, Integer, Text, Boolean, DateTime, ForeignKey, Table from sqlalchemy.orm import relationship, sessionmaker engine = create_engine('mysql+pymysql://blog:123456@localhost:3306/blog?charset=utf8') Base = declarative_base() 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) def __repr__(self): return "%s(%r)" % (self.__class__.__name__, self.username) if __name__ == '__main__': Base.metadata.create_all(engine)

We see that in the User class, __tablename__ is used to specify the name of the table in MySQL. We created three basic fields, each Column in the class representing a Column in the database, and in Colunm, specifying some configuration for that Column. The first field represents the data type of the class. Above we used String and Integer, two of the most commonly used types. Others commonly used include:

Text Boolean SmallInteger dateTime foreignKey nullable=False means that the column cannot be null, index=True means to create an index on the column.

In addition, __repr__ is defined to facilitate debugging. You can not define __repr__, or you can define it in more detail.

Run the py

To run the program, let’s see how the table is created on the MySQL command line:

C:\Windows\system32>mysql -ublog -p123456

mysql> use blog;
Database changed

mysql> show create table users\G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL,
  `password` varchar(64) NOT NULL,
  `email` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_users_username` (`username`),
  KEY `ix_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4. Relationship definition

4.1 One-to-many relationships

In an ordinary blog application, users and articles are obviously a one-to-many relationship. An article belongs to a user, and a user can write many articles, so the relationship between them can be defined as follows:

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') 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, name=" name ") 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 pointing to the primary key ID in the USERS table, while in USER the relationship description relationship provided by SQLAlchemy is used. The relationship between the user and the article is bidirectional, so we can see that relationship is defined in both tables above.

SQLAlchemy provides backref so that we can define only one relationship: Articles = relationship(‘Article’, backref=’author’) added this to eliminate the need to define relationship in Article!

4.2 One-to-one relationship

In User we have only defined a few required fields, but usually the User has a lot of other information, but this information may not be necessary. We can put it into another UserInfo table, so that User and UserInfo have a one-to-one relationship. You might wonder why the one-to-one relationship doesn’t come before the one-to-many relationship. That’s because one-to-one relationships are defined on the basis of one-to-many:

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') 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'))

The definition works the same way as one-to-many, except that you need to add userList =False.

4.3 The many-to-many relationship

A blog post usually has a category and several tabs. The relationship between tags and blogs is a many-to-many relationship. A many-to-many relationship cannot be defined directly. It needs to be decomposed into two one-to-many relationships. For this, an additional table is needed to assist:

Article_tag = Table('article_tag', base.metadata, Column('article_id', Integer) ForeignKey('articles.id')), Column('tag_id', Integer, ForeignKey('tags.id')) ) """ class ArticleTag(Base): __tablename__ = 'article_tag' id = Column(Integer, primary_key=True) article_id = Column(Integer, ForeignKey('articles.id')) tag_id = Column(Integer, ForeignKey('tags.id')) class Tag(Base): __tablename__ = 'tags' id = Column(Integer, primary_key=True) name = Column(String(64), nullable=False, index=True) def __repr__(self): return "%s(%r)" % (self.__class__.__name__, self.name)

4.4 Mapping to a database

#! /usr/bin/env python # _*_ coding:utf-8 _*_ __author__ = 'junxi' """ mysql://username:password@hostname/database postgresql://username:password@hostname/database sqlite:////absolute/path/to/database sqlite:///c:/absolute/path/to/database """ from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, String, Integer, Text, Boolean, DateTime, ForeignKey, Table from sqlalchemy.orm import relationship, sessionmaker engine = create_engine('mysql+pymysql://blog:123456@localhost:3306/blog?charset=utf8') Base = declarative_base() # print(engine) 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') 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')) 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) "" Base.metadata, Column('article_id', Integer, ForeignKey('articles.id')), Column('tag_id', Integer, ForeignKey('tags.id')) ) """ class ArticleTag(Base): __tablename__ = 'article_tag' id = Column(Integer, primary_key=True) article_id = Column(Integer, ForeignKey('articles.id')) tag_id = Column(Integer, ForeignKey('tags.id')) class Tag(Base): __tablename__ = 'tags' id = Column(Integer, primary_key=True) name = Column(String(64), nullable=False, index=True) def __repr__(self): return "%s(%r)" % (self.__class__.__name__, self.name) if __name__ == '__main__': Base.metadata.create_all(engine)

MySQL > select * from mysql. MySQL >

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

All tables are already created!