[Advanced Python] Flask model to DB by SQLAlchemy
本课程带领大家使用 SQLAlchemy 连接 MySQL 数据库，创建一个博客应用所需要的数据表，并介绍了使用 SQLAlchemy 进行简单了 CURD 操作及使用 Faker 生成测试数据。
[CDH] Cloudera's Distribution including Apache Hadoop【涉及到一点安装】
[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$
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
一开始没设置密码，直接按回车即可。
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()
三、自动化测试
Ref: [Advanced Python] RESTful Routes for pytest
测试的准备过程，涉及到fixture，一些数据库配置和初始化的操作。
End.
浙公网安备 33010602011771号