使用Flask SQLAlchemy从SQLite切换到MySQL [英] Switching from SQLite to MySQL with Flask SQLAlchemy
问题描述
我有一个网站，我用Flask SQLAlchemy和SQLite构建，并需要切换到MySQL。我已迁移数据库本身，并在MySQL下运行，但
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
- 无法找出如何连接到MySQL数据库is，
SQLALCHEMY_DATABASE_URI应该是）和 - 不清楚我现有的SQLAlchemy SQLite代码是否可以与MySQL配合使用。 li>
- 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.
我怀疑（1）很简单，只是显示如何映射连接对话框的内容我在我的MySQL数据库工具中使用适当格式的URL。但我担心（2），我假设SQLAlchemy提供了一个抽象层，以便简单SQLAlchemy代码如
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
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', 'admin@example.com')
db.session.add(admin)
User.query.all()
User.query.filter_by(username='admin').first()
除了对数据库URI进行适当的更改之外，还可以对其进行任何修改;但使用SQLAlchemy与MySQL的示例似乎使用完全不同的API。
wold work without any modifications other than an appropriate change to the database URI; but the examples I've found for using SQLAlchemy with MySQL seem to use a completely different API.
我可以（2）通过简单地更改数据库URI来迁移我的Flask SQLAlchemy代码以使用MySQL数据库，如果是这样如果该URI是？
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?
推荐答案
您指向的教程显示了使用SQLAlchemy连接MySQL的正确方法。下面是你的代码，只有很少的改动：
The tutorial pointed by you shows the right way of connecting to MySQL using SQLAlchemy. Below is your code with very little changes:
我的假设是你的MySQL服务器运行在同一台机器上，Flask正在运行，数据库名是db_name。如果您的服务器不是同一台计算机，请将服务器IP替换为 localhost 。
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.
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', 'admin@example.com')
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()
我发现 SQLAlchemy （ mqsqldb ）使用的默认驱动程序，不会在我的虚拟环境中为我编译。所以我选择了一个MySQL驱动程序完全python实现 pymysql 。使用 pip install pymysql 安装它后，SQLALCHEMY_DATABASE_URI将更改为：
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:
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://username:password@localhost/db_name'
使用ORM像SQLAlchemy的目的是，你可以使用不同的数据库，在大多数情况下很少或没有变化。所以，我的答案是肯定的。您应该能够使用您的sqlite代码与MySQL一起使用URI映射为上面的代码。
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.
这篇关于使用Flask SQLAlchemy从SQLite切换到MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
