用 Flask 来写个轻博客 (3) — (M)VC_连接 MySQL 和 SQLAlchemy

目录

前文列表

用 Flask 来写个轻博客 (1) — 创建项目
用 Flask 来写个轻博客 (2) — Hello World！

扩展阅读

SQLAlchemy_定义(一对一/一对多/多对多)关系

前言

大多数的应用程序在开发之前都需要先进行数据库设计这一环节，所以本篇就先来记录在 Flask 中如何使用 Models，也就是 MVC 模式中的 M 。

Models 模型

模型 就是对 数据抽象 并且提供一种 统一的通用访问接口 方式。
在大多数的 Web 应用中，都会将数据存储在 关系型数据库 中，EG. Mysql/Oracle/Postgres 。(当然现在也越来越多的在使用 非关系型数据库，但本篇只对前者做记录。) 这就会出现一个问题，怎样才能让关系型数据在面向对象编程结构的应用程序中得到更好的契合? 问了解决这个问题，在 Web 应用程序开发中引入了 Models 的概念 —— 将关系型数据转化为一个对象类型

SQLAlchemy

SQLAlchemy 是一个 Python 包，其最底层包装了对数据库进入操作的统一接口，然后在最上层提供了对象关系映射(ORM)的实现。

ORM 是在基于不同的数据结构和不同的系统类型之间进行传递和转换数据的计数。简而言之，SQLAlchemy-ORM 可以把大量的不同类型的数据库中的数据，转换成 Python 对象的集合。也就是说，SQLAlchemy-ORM 可以将对这些数据对象的操作转化为对数据库的操作。

Flask 为我们提供了 Flask SQLAlchemy，其实就是在 SQLAlchemy 上提供了一层包装，让 SQLAlchemy 可以结合 Flask 的一些特性来使用。

安装 SQLAlchemy

(env) [root@flask-dev JmilkFan-s-Blog]# pip install flask-sqlalchemy
(env) [root@flask-dev JmilkFan-s-Blog]# pip freeze > requirements.txt

flask-sqlalchemy 默认支持 SQLite，但是这里我们会使用 Mysql，所以还需要安装 SQLAlchemy 和 Mysql 之间的连接器。

(env) [root@flask-dev JmilkFan-s-Blog]# pip install PyMySQL
(env) [root@flask-dev JmilkFan-s-Blog]# pip freeze > requirements.txt 

安装 Mysql

wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum install mysql-community-server
service mysqld restart

初始化 MySQL 并设置登陆密码:

[root@flask-dev JmilkFan-s-Blog]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


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? [Y/n] 
 ... 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? [Y/n] 
 ... Success!

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? [Y/n] 
 - Dropping test database...
ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist
 ... Failed!  Not critical, keep moving...
 - 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? [Y/n] 
 ... Success!

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

Cleaning up...

建立 SQLAlchemy 和 Mysql 的连接

SQLAlchemy 通过一个特殊的 URI 字符串来创建与数据库的连接，一般的格式如下：

database_type+driver://user:password@sql_server_ip:port/database_name

我们需要在 config.py 文件中定义这一 URI：

class Config(object):
    """Base config class."""
    pass

class ProdConfig(Config):
    """Production config class."""
    pass

class DevConfig(Config):
    """Development config class."""
    DEBUG = True
    # MySQL connection
    SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://root:fanguiju@127.0.0.1:3306/jmilkfansblog'