Flask连接数据库流程（flask_migrate）

因为每次都报错，一写数据库连接就忘记，所以记录一下全部流程：

流程

首先写一个exts.py和models.py文件，exts.py文件中

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

models.py里面以类的方式写每一个数据库的关系表，不赘述。

创建config.py和manager.py

config.py文件内容如下：

DEBUG = True

# dialect+driver://username:password@host:port/database
DIALECT = 'mysql'
DRIVER = 'mysqldb'
USERNAME = 'root'
PASSWORD = 'xxxxxxxxx'
HOST = '127.0.0.1'
PORT = '3306'
DATABASE = 'xxxxx_system' # 前面都可以沿用，但是这里记得修改

SQLALCHEMY_DATABASE_URI = "{}+{}://{}:{}@{}:{}/{}?charset=utf8".format(DIALECT,DRIVER,USERNAME,PASSWORD,HOST,PORT,DATABASE)
SQLALCHEMY_TRACK_MODIFICATIONS = False

manager.py文件内容如下：

from flask_script import Manager
from exts import db
from models import User # 这里一定记得要把models.py文件中的类都导入！！！
from app import app
from flask_migrate import Migrate,MigrateCommand

manager = Manager(app)
# 1. 要使用Migrate，必须先将它和app和db绑定，直接用实例化Migrate的形式
migrate = Migrate(app,db)
# 2. 添加命令，MigrateCommand是一个命令的‘集合’，所以只要一次性将MigrateCommand添加，增加一个别名 'mc'即可
manager.add_command('mc',MigrateCommand)

if __name__ == '__main__':
    manager.run()

app.py中添加

from flask import Flask,render_template
# 以下这三项一定要注意
from exts import db
import config
import os

# 以下初始化的顺序不要随意改变
app = Flask(__name__)
app.config.from_object(config)
db.init_app(app)
app.config['SECRET_KEY'] = os.urandom(24)

进入cmd，进入python的虚拟环境！！

> cd C:\User\ashinier\Virtualenv\flask-env\Scripts
> activate

进入虚拟环境后，再进入到项目的目录下：(执行init、migrate、upgrade三部曲)

(flask-env) D:\pycharm\HelloWorld>python manager.py mc init
Creating directory D:\pycharm\HelloWorld\migrations ...  done
Creating directory D:\pycharm\HelloWorld\migrations\versions ...  done
Generating D:\pycharm\HelloWorld\migrations\alembic.ini ...  done
Generating D:\pycharm\HelloWorld\migrations\env.py ...  done
Generating D:\pycharm\HelloWorld\migrations\README ...  done
Generating D:\pycharm\HelloWorld\migrations\script.py.mako ...  done
Please edit configuration/connection/logging settings in 'D:\\pycharm\\HelloWorld\\migrations\\alembic.ini' before proceeding.

(flask-env) D:\pycharm\HelloWorld>python manager.py mc migrate
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'administrator'
INFO  [alembic.autogenerate.compare] Detected added table 'group'
INFO  [alembic.autogenerate.compare] Detected added table 'user'
INFO  [alembic.autogenerate.compare] Detected added table 'contend'
INFO  [alembic.autogenerate.compare] Detected added table 'groupsubscription'
INFO  [alembic.autogenerate.compare] Detected added table 'contendsubscription'
Generating D:\pycharm\HelloWorld\migrations\versions\aedea7cc73c3_.py ...  done

(flask-env) D:\pycharm\HelloWorld>python manager.py mc upgrade
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> aedea7cc73c3, empty message

以下是可能出现的报错：

• 如果出现ImportError看一下是不是没有进入到正确的虚拟环境路径下面去，详细可以看我的另一篇BUG记录 ImportError: cannot import name ‘MigrateCommand‘
• 没有连接SQLAlchemy()的实例与app，会有如下报错。报错的解决方式：进行连接即可，连接可以写在app.py也可以写在manager.py，我这里SQLAlchemy()的实例名称为db，所以连接语句db.init_app(app)

AssertionError: The sqlalchemy extension was not registered to the current application.  Please make sure to call init_app() first.

• 如果没有app.config.from_object(config)将配置文件与app实例相连接就在执行python manager.py mc migrate时报错，或者是配置文件本身内容出错。

C:\Users\ashinier\Virtualenv\flask-env\lib\site-packages\flask_sqlalchemy\__init__.py:813: UserWarning: Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BINDS is set. Defaulting SQLALCHEMY_DATABASE_URI to "sqlite:///:memory:".
  warnings.warn(
C:\Users\ashinier\Virtualenv\flask-env\lib\site-packages\flask_sqlalchemy\__init__.py:834: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  warnings.warn(FSADeprecationWarning(

• 如果执行完三件套后，show tables;数据库没有想要的表格，可能是manager.py文件没有导入关系表的类造成的

(flask-env) D:\pycharm\HelloWorld>python manager.py mc upgrade
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.

没有出现最后一句INFO [alembic.runtime.migration] Running upgrade -> 4e5cf0a72ba4, empty message，数据库实际上并没有变化。
解决方法：（是一个比较蠢的解决办法，因为暂时没搞明白这个bug是怎么造成的，如果有大佬看到这里，欢迎指教）

1. 删除migrations文件夹
2. 删除数据库中的alembic_version表，drop table alembic_version
3. 重新来一次mc三件套