Python 使用 Flask SQLAlchemy 从 SQLite 切换到 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27766794/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Switching from SQLite to MySQL with Flask SQLAlchemy
提问by orome
I have a site that I've built with Flask SQLAlchemy and SQLite, and need to switch to MySQL. I have migrated the database itself and have it running under MySQL, but
我有一个使用 Flask SQLAlchemy 和 SQLite 构建的站点，需要切换到 MySQL。我已经迁移了数据库本身并让它在 MySQL 下运行，但是
- Can't figure out how to connect to the MySQL database (that is, what the
SQLALCHEMY_DATABASE_URIshould be) and - Am unclear if any of my existing SQLAlchemy SQLite code will work with MySQL.
- 无法弄清楚如何连接到 MySQL 数据库（即
SQLALCHEMY_DATABASE_URI应该是什么）和 - 不清楚我现有的任何 SQLAlchemy SQLite 代码是否适用于 MySQL。
I suspect that (1) is fairly simple and just a matter of being shown how to map, for example, the contents of the connection dialog I use in my MySQL database tool to an appropriately formatted URL. But I'm worried about (2), I had assumed that SQLAlchemy provided an abstraction layer so that simple SQLAlchemy code such as
我怀疑 (1) 相当简单，只是展示了如何将我在 MySQL 数据库工具中使用的连接对话框的内容映射到适当格式的 URL 的问题。但我担心（2），我曾假设 SQLAlchemy 提供了一个抽象层，以便简单的 SQLAlchemy 代码如
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
email = db.Column(db.String(120), unique=True)
def __init__(self, username, email):
self.username = username
self.email = email
def __repr__(self):
return '<User %r>' % self.username
admin = User('admin', '[email protected]')
db.session.add(admin)
User.query.all()
User.query.filter_by(username='admin').first()
wold work without any modifications other than an appropriate change to the database URI; but the examples I've foundfor using SQLAlchemy with MySQL seem to use a completely different API.
除了对数据库 URI 进行适当的更改外，无需任何修改即可工作；但是我发现的将 SQLAlchemy 与 MySQL 一起使用的示例似乎使用了完全不同的 API。
Can I (2) migrate my Flask SQLAlchemy code to work with a MySQL database by simply changing the database URI and if so (1) what should that URI be?
我可以 (2) 通过简单地更改数据库 URI 来迁移我的 Flask SQLAlchemy 代码以使用 MySQL 数据库，如果可以，(1) 该 URI 应该是什么？
采纳答案by skjoshi
The tutorial pointed by you shows the right way of connecting to MySQL using SQLAlchemy. Below is your code with very little changes:
您指向的教程展示了使用 SQLAlchemy 连接到 MySQL 的正确方法。以下是您的代码，更改很少：
My assumptions are your MySQL server is running on the same machine where Flask is running and the database name is db_name. In case your server is not same machine, put the server IP in place of localhost.
我的假设是您的 MySQL 服务器在运行 Flask 的同一台机器上运行，并且数据库名称是 db_name。如果您的服务器不是同一台机器，请将服务器 IP 放在localhost.
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://username:password@localhost/db_name'
db = SQLAlchemy(app)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
email = db.Column(db.String(120), unique=True)
def __init__(self, username, email):
self.username = username
self.email = email
def __repr__(self):
return '<User %r>' % self.username
admin = User('admin', '[email protected]')
db.create_all() # In case user table doesn't exists already. Else remove it.
db.session.add(admin)
db.session.commit() # This is needed to write the changes to database
User.query.all()
User.query.filter_by(username='admin').first()
It happened to me that the default driver used by SQLAlchemy(mqsqldb), doesn't get compiled for me in my virtual environments. So I have opted for a MySQL driver with full python implementation pymysql. Once you install it using pip install pymysql, the SQLALCHEMY_DATABASE_URI will change to:
我碰巧在我的虚拟环境中没有为我编译SQLAlchemy( mqsqldb)使用的默认驱动程序。所以我选择了一个带有完整 python 实现的 MySQL 驱动程序pymysql。使用 安装后pip install pymysql，SQLALCHEMY_DATABASE_URI 将更改为：
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://username:password@localhost/db_name'
The purpose of using ORM like SQLAlchemy is that , you can use different database with little or no change in most cases. So, my answer is yes. You should be able to use your sqlite code to work with MySQL with the URI mapped as in above code.
像 SQLAlchemy 一样使用 ORM 的目的是，在大多数情况下，您可以使用不同的数据库而几乎没有变化。所以，我的答案是肯定的。您应该能够使用您的 sqlite 代码来处理 MySQL，并按照上面的代码映射 URI。
回答by Christopher Waldeck
The accepted answer was correct at the time, but the syntax in the import statement has been deprecated.
接受的答案当时是正确的，但导入语句中的语法已被弃用。
This:
这个：
from flask.ext.sqlalchemy import SQLAlchemy
Should be replaced with:
应替换为：
import flask_sqlalchemy
Since questions regarding database connections tend to get traffic and stay relevant for a long time, it's worth having on the record.
由于有关数据库连接的问题往往会引起流量并长时间保持相关性，因此值得记录下来。
The deprecation is in the Flask Version 1.0 Changelog, which actually uses this module in the example:
弃用在Flask Version 1.0 Changelog 中，它实际上在示例中使用了这个模块：
flask.ext - import extensions directly by their name instead of through the flask.ext namespace. For example, import flask.ext.sqlalchemy becomes import flask_sqlalchemy.
flask.ext - 直接按名称导入扩展，而不是通过 flask.ext 命名空间。例如，import flask.ext.sqlalchemy 变为 import flask_sqlalchemy。
