[Advanced Python] Flask model to DB by SQLAlchemy

Ref: SQLAlchemy 教程 —— 基础入门篇

 

本课程带领大家使用 SQLAlchemy 连接 MySQL 数据库,创建一个博客应用所需要的数据表,并介绍了使用 SQLAlchemy 进行简单了 CURD 操作及使用 Faker 生成测试数据。

[CDH] Cloudera's Distribution including Apache Hadoop【涉及到一点安装】

[MySQL] 01- Basic sql

[MySQL] 02- Optimisation solutions

 

一、准备环境 for MySQL

jeffrey@unsw-ThinkPad-T490:blog$ dpkg --list|grep mysql
ii  libmysqlclient20:amd64                     5.7.32-0ubuntu0.18.04.1                          amd64        MySQL database client library
ii  libqt4-sql-mysql:amd64                     4:4.8.7+dfsg-7ubuntu1                            amd64        Qt 4 MySQL database driver
ii  mysql-client-5.7                           5.7.32-0ubuntu0.18.04.1                          amd64        MySQL database client binaries
ii  mysql-client-core-5.7                      5.7.32-0ubuntu0.18.04.1                          amd64        MySQL database core client binaries
ii  mysql-common                               5.8+1.0.4                                        all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server                               5.7.32-0ubuntu0.18.04.1                          all          MySQL database server (metapackage depending on the latest version)
ii  mysql-server-5.7                           5.7.32-0ubuntu0.18.04.1                          amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-5.7                      5.7.32-0ubuntu0.18.04.1                          amd64        MySQL database server binaries
ii  python-mysqldb                             1.3.10-1build1                                   amd64        Python interface to MySQL
jeffrey@unsw-ThinkPad-T490:blog$ 
jeffrey@unsw-ThinkPad-T490:blog$ 
jeffrey@unsw-ThinkPad-T490:blog$ sudo apt-get remove mysql-common
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages were automatically installed and are no longer required:
  libaio1 libevent-core-2.1-6 linux-hwe-5.4-headers-5.4.0-51 linux-hwe-5.4-headers-5.4.0-52 mysql-client-core-5.7 mysql-server-core-5.7
Use 'sudo apt autoremove' to remove them.
The following packages will be REMOVED:
  libmysqlclient20 libqt4-sql-mysql mysql-client-5.7 mysql-common mysql-server mysql-server-5.7 python-mysqldb
0 to upgrade, 0 to newly install, 7 to remove and 10 not to upgrade.
After this operation, 83.2 MB disk space will be freed.
Do you want to continue? [Y/n] Y
(Reading database ... 289502 files and directories currently installed.)
Removing libqt4-sql-mysql:amd64 (4:4.8.7+dfsg-7ubuntu1) ...
Removing python-mysqldb (1.3.10-1build1) ...
Removing libmysqlclient20:amd64 (5.7.32-0ubuntu0.18.04.1) ...
Removing mysql-server (5.7.32-0ubuntu0.18.04.1) ...
Removing mysql-server-5.7 (5.7.32-0ubuntu0.18.04.1) ...
update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Removing mysql-client-5.7 (5.7.32-0ubuntu0.18.04.1) ...
Removing mysql-common (5.8+1.0.4) ...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
Processing triggers for libc-bin (2.27-3ubuntu1.3) ...
jeffrey@unsw-ThinkPad-T490:blog$ 
jeffrey@unsw-ThinkPad-T490:blog$ 
jeffrey@unsw-ThinkPad-T490:blog$ sudo apt-get autoremove --purge mysql-server-5.7
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages will be REMOVED:
  libaio1* libevent-core-2.1-6* linux-hwe-5.4-headers-5.4.0-51* linux-hwe-5.4-headers-5.4.0-52* mysql-client-core-5.7* mysql-server-5.7* mysql-server-core-5.7*
0 to upgrade, 0 to newly install, 7 to remove and 10 not to upgrade.
After this operation, 218 MB disk space will be freed.
Do you want to continue? [Y/n] Y
(Reading database ... 289342 files and directories currently installed.)
Removing mysql-server-core-5.7 (5.7.32-0ubuntu0.18.04.1) ...
Removing mysql-client-core-5.7 (5.7.32-0ubuntu0.18.04.1) ...
Removing libaio1:amd64 (0.3.110-5ubuntu0.1) ...
Removing libevent-core-2.1-6:amd64 (2.1.8-stable-4build1) ...
Removing linux-hwe-5.4-headers-5.4.0-51 (5.4.0-51.56~18.04.1) ...
Removing linux-hwe-5.4-headers-5.4.0-52 (5.4.0-52.57~18.04.1) ...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
Processing triggers for libc-bin (2.27-3ubuntu1.3) ...
(Reading database ... 253722 files and directories currently installed.)
Purging configuration files for mysql-server-5.7 (5.7.32-0ubuntu0.18.04.1) ...
Processing triggers for systemd (237-3ubuntu10.43) ...
Processing triggers for ureadahead (0.100.0-21) ...
ureadahead will be reprofiled on next reboot
jeffrey@unsw-ThinkPad-T490:blog$ 
jeffrey@unsw-ThinkPad-T490:blog$ 
jeffrey@unsw-ThinkPad-T490:blog$ dpkg -l|grep ^rc|awk '{print$2}'|sudo xargs dpkg -P
(Reading database ... 253709 files and directories currently installed.)
Purging configuration files for libnvidia-compute-440:amd64 (450.66-0ubuntu0.18.04.1) ...
Purging configuration files for linux-image-5.0.0-23-generic (5.0.0-23.24~18.04.1) ...
rmdir: failed to remove '/lib/modules/5.0.0-23-generic': Directory not empty
Purging configuration files for linux-image-5.4.0-47-generic (5.4.0-47.51~18.04.1) ...
Purging configuration files for linux-image-5.4.0-48-generic (5.4.0-48.52~18.04.1) ...
Purging configuration files for linux-image-5.4.0-51-generic (5.4.0-51.56~18.04.1) ...
Purging configuration files for linux-image-5.4.0-52-generic (5.4.0-52.57~18.04.1) ...
Purging configuration files for linux-modules-5.0.0-23-generic (5.0.0-23.24~18.04.1) ...
Purging configuration files for linux-modules-5.4.0-47-generic (5.4.0-47.51~18.04.1) ...
Purging configuration files for linux-modules-5.4.0-48-generic (5.4.0-48.52~18.04.1) ...
Purging configuration files for linux-modules-5.4.0-51-generic (5.4.0-51.56~18.04.1) ...
Purging configuration files for linux-modules-5.4.0-52-generic (5.4.0-52.57~18.04.1) ...
Purging configuration files for linux-modules-extra-5.0.0-23-generic (5.0.0-23.24~18.04.1) ...
Purging configuration files for linux-modules-extra-5.4.0-47-generic (5.4.0-47.51~18.04.1) ...
Purging configuration files for linux-modules-extra-5.4.0-48-generic (5.4.0-48.52~18.04.1) ...
Purging configuration files for linux-modules-extra-5.4.0-51-generic (5.4.0-51.56~18.04.1) ...
Purging configuration files for linux-modules-extra-5.4.0-52-generic (5.4.0-52.57~18.04.1) ...
Purging configuration files for mysql-common (5.8+1.0.4) ...
jeffrey@unsw-ThinkPad-T490:blog$ 
jeffrey@unsw-ThinkPad-T490:blog$ 
jeffrey@unsw-ThinkPad-T490:blog$ dpkg --list|grep mysql
jeffrey@unsw-ThinkPad-T490:blog$ 
jeffrey@unsw-ThinkPad-T490:blog$ sudo apt-get autoremove --purge mysql-apt-config
Reading package lists... Done
Building dependency tree       
Reading state information... Done
E: Unable to locate package mysql-apt-config
jeffrey@unsw-ThinkPad-T490:blog$
删除 mysql
jeffrey@unsw-ThinkPad-T490:blog$ dpkg -l | grep mysql
jeffrey@unsw-ThinkPad-T490:blog$ 
jeffrey@unsw-ThinkPad-T490:blog$ apt install mysql-server
E: Could not open lock file /var/lib/dpkg/lock-frontend - open (13: Permission denied)
E: Unable to acquire the dpkg frontend lock (/var/lib/dpkg/lock-frontend), are you root?
jeffrey@unsw-ThinkPad-T490:blog$ 
jeffrey@unsw-ThinkPad-T490:blog$ sudo apt install mysql-server
[sudo] password for jeffrey: 
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following additional packages will be installed:
  libaio1 libevent-core-2.1-6 mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server-5.7 mysql-server-core-5.7
Suggested packages:
  mailx tinyca
The following NEW packages will be installed:
  libaio1 libevent-core-2.1-6 mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server mysql-server-5.7 mysql-server-core-5.7
0 to upgrade, 8 to newly install, 0 to remove and 10 not to upgrade.
Need to get 19.1 MB of archives.
After this operation, 155 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 http://au.archive.ubuntu.com/ubuntu bionic/main amd64 mysql-common all 5.8+1.0.4 [7,308 B]
Get:2 http://au.archive.ubuntu.com/ubuntu bionic-updates/main amd64 libaio1 amd64 0.3.110-5ubuntu0.1 [6,476 B]
Get:3 http://au.archive.ubuntu.com/ubuntu bionic-updates/main amd64 mysql-client-core-5.7 amd64 5.7.32-0ubuntu0.18.04.1 [6,660 kB]
Get:4 http://au.archive.ubuntu.com/ubuntu bionic-updates/main amd64 mysql-client-5.7 amd64 5.7.32-0ubuntu0.18.04.1 [1,943 kB]                                                       
Get:5 http://au.archive.ubuntu.com/ubuntu bionic-updates/main amd64 mysql-server-core-5.7 amd64 5.7.32-0ubuntu0.18.04.1 [7,455 kB]                                                  
Get:6 http://au.archive.ubuntu.com/ubuntu bionic/main amd64 libevent-core-2.1-6 amd64 2.1.8-stable-4build1 [85.9 kB]                                                                
Get:7 http://au.archive.ubuntu.com/ubuntu bionic-updates/main amd64 mysql-server-5.7 amd64 5.7.32-0ubuntu0.18.04.1 [2,935 kB]                                                       
Get:8 http://au.archive.ubuntu.com/ubuntu bionic-updates/main amd64 mysql-server all 5.7.32-0ubuntu0.18.04.1 [9,944 B]                                                              
Fetched 19.1 MB in 14s (1,340 kB/s)                                                                                                                                                 
Preconfiguring packages ...
Selecting previously unselected package mysql-common.
(Reading database ... 253706 files and directories currently installed.)
Preparing to unpack .../0-mysql-common_5.8+1.0.4_all.deb ...
Unpacking mysql-common (5.8+1.0.4) ...
Selecting previously unselected package libaio1:amd64.
Preparing to unpack .../1-libaio1_0.3.110-5ubuntu0.1_amd64.deb ...
Unpacking libaio1:amd64 (0.3.110-5ubuntu0.1) ...
Selecting previously unselected package mysql-client-core-5.7.
Preparing to unpack .../2-mysql-client-core-5.7_5.7.32-0ubuntu0.18.04.1_amd64.deb ...
Unpacking mysql-client-core-5.7 (5.7.32-0ubuntu0.18.04.1) ...
Selecting previously unselected package mysql-client-5.7.
Preparing to unpack .../3-mysql-client-5.7_5.7.32-0ubuntu0.18.04.1_amd64.deb ...
Unpacking mysql-client-5.7 (5.7.32-0ubuntu0.18.04.1) ...
Selecting previously unselected package mysql-server-core-5.7.
Preparing to unpack .../4-mysql-server-core-5.7_5.7.32-0ubuntu0.18.04.1_amd64.deb ...
Unpacking mysql-server-core-5.7 (5.7.32-0ubuntu0.18.04.1) ...
Selecting previously unselected package libevent-core-2.1-6:amd64.
Preparing to unpack .../5-libevent-core-2.1-6_2.1.8-stable-4build1_amd64.deb ...
Unpacking libevent-core-2.1-6:amd64 (2.1.8-stable-4build1) ...
Setting up mysql-common (5.8+1.0.4) ...
update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Selecting previously unselected package mysql-server-5.7.
(Reading database ... 253874 files and directories currently installed.)
Preparing to unpack .../mysql-server-5.7_5.7.32-0ubuntu0.18.04.1_amd64.deb ...
Unpacking mysql-server-5.7 (5.7.32-0ubuntu0.18.04.1) ...
Selecting previously unselected package mysql-server.
Preparing to unpack .../mysql-server_5.7.32-0ubuntu0.18.04.1_all.deb ...
Unpacking mysql-server (5.7.32-0ubuntu0.18.04.1) ...
Setting up libevent-core-2.1-6:amd64 (2.1.8-stable-4build1) ...
Setting up libaio1:amd64 (0.3.110-5ubuntu0.1) ...
Setting up mysql-client-core-5.7 (5.7.32-0ubuntu0.18.04.1) ...
Setting up mysql-server-core-5.7 (5.7.32-0ubuntu0.18.04.1) ...
Setting up mysql-client-5.7 (5.7.32-0ubuntu0.18.04.1) ...
Setting up mysql-server-5.7 (5.7.32-0ubuntu0.18.04.1) ...
update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Renaming removed key_buffer and myisam-recover options (if present)
Created symlink /etc/systemd/system/multi-user.target.wants/mysql.service → /lib/systemd/system/mysql.service.
Setting up mysql-server (5.7.32-0ubuntu0.18.04.1) ...
Processing triggers for libc-bin (2.27-3ubuntu1.3) ...
Processing triggers for systemd (237-3ubuntu10.43) ...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
Processing triggers for ureadahead (0.100.0-21) ...
jeffrey@unsw-ThinkPad-T490:blog$ 
jeffrey@unsw-ThinkPad-T490:blog$ 
jeffrey@unsw-ThinkPad-T490:blog$ netstat -tap | grep mysql
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)

^C
jeffrey@unsw-ThinkPad-T490:blog$ 
jeffrey@unsw-ThinkPad-T490:blog$ 
jeffrey@unsw-ThinkPad-T490:blog$ sudo netstat -tap | grep mysql
tcp        0      0 localhost:mysql         0.0.0.0:*               LISTEN      5603/mysqld  
安装 mysql

 

一开始没设置密码,直接按回车即可。

sudo mysql -u root -p
Enter password: 

 

运行命令:$ mysql_secure_installation

接下来,为了确保数据库的安全性和正常运转,对数据库进行初始化操作。这个初始化操作涉及下面5个步骤。

(1)安装验证密码插件。

(2)设置root管理员在数据库中的专有密码。

(3)随后删除匿名账户,并使用root管理员从远程登录数据库,以确保数据库上运行的业务的安全性。

(4)删除默认的测试数据库,取消测试数据库的一系列访问权限。

(5)刷新授权列表,让初始化的设定立即生效。

对于上述数据库初始化的操作步骤,在下面的输出信息旁边我做了简单注释。

root@ubuntu-virtual-machine:~# mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?    # 要安装验证密码插件吗?

Press y|Y for Yes, any other key for No: N    # 这里我选择N
Please set the password for root here.

New password:   # 输入要为root管理员设置的数据库密码

Re-enter new password:   # 再次输入密码


By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y     # 删除匿名账户
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : N    # 禁止root管理员从远程登录,这里我没有禁止

... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y   # 删除test数据库并取消对它的访问权限
- Dropping test database...
Success.

- Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y   # 刷新授权表,让初始化后的设定立即生效
Success.

All done!

以上是 MySQL 的安装。其他内容,查看以下链接。

Goto: [SQLAlchemy] Create tables

 

 

二、调用数据库:postgres

也可以直接使用Docker,免去了安装过程。

# pull official base image
FROM postgres:13-alpine

 

定义好 模型,同时也对应了一个表,表名字:'users' 。

 from sqlalchemy.sql import func
 from src import db
 
 
 class User(db.Model):
 
     __tablename__ = 'users'
 
     id           = db.Column(db.Integer, primary_key=True, autoincrement=True)
     username     = db.Column(db.String(128), nullable=False)
     email        = db.Column(db.String(128), nullable=False)
     active       = db.Column(db.Boolean(), default=True, nullable=False)        # 会自动填
     created_date = db.Column(db.DateTime,  default=func.now(), nullable=False)  # 会自动填
 
     def __init__(self, username, email):
         self.username = username    # nullable=False, 所以必填
         self.email    = email       # nullable=False, 所以必填

 

模块化涉及REST API。

Blueprint 是一个存储视图方法的容器,这些操作在这个Blueprint 被注册到一个应用之后就可以被调用,Flask 可以通过Blueprint来组织URL以及处理请求。

 

(1) 给数据库添加一条内容,以及获取数据库某一条内容。

# Here, we define blueprint for this api

from flask import Blueprint, request
from flask_restx import Resource, Api, fields

from src import db
from src.api.models import User


users_blueprint = Blueprint('users', __name__)
api = Api(users_blueprint)


user_param = api.model('User', {
    'id':           fields.Integer(readOnly=True),
    'username':     fields.String(required=True),
    'email':        fields.String(required=True),
    'created_date': fields.DateTime,
})


class UsersList(Resource):

    @api.expect(user_param, validate=True)  # new
    def post(self):
        '''
        创建一条内容:user
        '''
        print("In UsersList.post()")
        post_data = request.get_json()
        username  = post_data.get('username')
        email     = post_data.get('email')

        #-----------------------------------------------

        response_object = {}

        user = User.query.filter_by(email=email).first()
        if user:
            response_object['message'] = 'Sorry. That email already exists.'
            return response_object, 400

        # 添加
        db.session.add( User(username=username, email=email) )
        db.session.commit()

        response_object = {
            'message': f'{email} was added!'
        }
        return response_object, 201


    @api.marshal_with(user_param, as_list=True)
    def get(self):
        return User.query.all(), 200



class Users(Resource):

    @api.marshal_with(user_param)
    def get(self, user_id):
        '''
        查找某用户id是否存在
        url的参数处理:user_id <-- <int:user_id>
        '''
        user = User.query.filter_by(id=user_id).first()
        if not user:
            api.abort(404, f"User {user_id} does not exist")
        return user, 200


api.add_resource(UsersList, '/users')
api.add_resource(Users,     '/users/<int:user_id>')

 

(2) 通过命令行直接操作函数,trigger db operations.

 import sys 
 from flask.cli import FlaskGroup
 
 from src import create_app, db
 from src.api.models import User
 
 app = create_app()
 cli = FlaskGroup(create_app=create_app)
 
 @cli.command('recreate_db')
 def recreate_db():
     print("recreate_db starts.")
     db.drop_all()
     db.create_all()
     db.session.commit()
     print("recreate_db ends.")
 
 
 @cli.command('seed_db')
 def seed_db():
     print("seed_db starts.")
     db.session.add( User(username='michael', email="hermanmu@gmail.com") )
     db.session.add( User(username='michaelherman', email="michael@mherman.org") )
     db.session.commit()
     print("seed_db ends.")
 
 
 if __name__ == '__main__':
     cli()

模拟数据: faker

import random
from faker import Factory
if __name__ == '__main__':

    # creating all tables, done.
    Base.metadata.create_all(engine)


    # create session.
    Session = sessionmaker(bind=engine)
    session = Session()


    faker = Factory.create()

    faker_users = [User(
        username = faker.name(),
        password = faker.word(),
        email    = faker.email(),
    ) for i in range(10)]
    session.add_all(faker_users)


    faker_categories = [Category(name=faker.word()) for i in range(5)]
    session.add_all(faker_categories)

    faker_tags= [Tag(name=faker.word()) for i in range(20)]
    session.add_all(faker_tags)

    for i in range(100):
        article = Article(
            title    = faker.sentence(),
            content  = ' '.join(faker.sentences(nb=random.randint(10, 20))),
            author   = random.choice(faker_users),
            category = random.choice(faker_categories)
        )
        for tag in random.sample(faker_tags, random.randint(2, 5)):
            article.tags.append(tag)
        session.add(article)

    session.commit()
View Code

 

 

三、自动化测试

Ref: [Advanced Python] RESTful Routes for pytest

测试的准备过程,涉及到fixture,一些数据库配置和初始化的操作。

 
 End. 
posted @ 2020-12-08 06:05  郝壹贰叁  阅读(134)  评论(0)    收藏  举报