用Flask的SQLAlchemy从SQLite切换到MySQL[英] Switching from SQLite to MySQL with Flask SQLAlchemy

本文是小编为大家收集整理的关于用Flask的SQLAlchemy从SQLite切换到MySQL的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有一个我使用烧瓶Sqlalchemy和Sqlite构建的网站,需要切换到MySQL.我已经迁移了数据库本身,并在MySQL下运行,但是

  1. 无法弄清楚如何连接到MySQL数据库(即SQLALCHEMY_DATABASE_URI应该是什么)和
  2. 尚不清楚我现有的SQLalchemy Sqlite代码是否可以与MySQL一起使用.

我怀疑(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', 'admin@example.com')

db.session.add(admin)

User.query.all()

User.query.filter_by(username='admin').first()
除了对数据库URI的适当更改以外,

无需进行任何修改;但是我发现的示例 用于与mysql一起使用sqlalchemy使用完全不同的API.

我(2)可以通过简单地更改数据库URI来迁移烧瓶SQLALCHEMY代码与MySQL数据库一起使用,如果是的话(1)URI应该是什么?

推荐答案

您指向的教程显示了使用sqlalchemy连接到mySQL的正确方法.以下是您的代码,几乎没有更改:

我的假设是您的MySQL Server正在运行烧瓶运行的同一台计算机上,数据库名称为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', '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)使用的默认驱动程序在我的虚拟环境中不会为我编译.因此,我选择了具有完整Python实现的MySQL驱动程序pymysql.使用pip install pymysql安装它后,sqlalchemy_database_uri将更改为:

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://username:password@localhost/db_name'

使用ORM之类的目的是Sqlalchemy,您可以在大多数情况下使用不同或没有更改的不同数据库.所以,我的答案是肯定的.您应该能够使用SQLite代码与MySQL一起使用,如上述代码所示的URI.

其他推荐答案

当时接受的答案是正确的,但是导入语句中的语法已被弃用.

这个:

from flask.ext.sqlalchemy import SQLAlchemy

应替换为:

import flask_sqlalchemy

由于有关数据库连接的问题往往会引起流量并保持长期相关,因此值得记录.

弃用在 flask版本1.0 ChandElog 实际上使用此模块在示例中:

flask.ext-直接通过其名称导入扩展名而不是 通过烧瓶.ext名称空间.例如,导入 flask.ext.sqlalchemy成为导入烧瓶flask_sqlalchemy.

本文地址:https://www.itbaoku.cn/post/597496.html

问题描述

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

  1. Can't figure out how to connect to the MySQL database (that is, what the SQLALCHEMY_DATABASE_URI should be) and
  2. Am unclear if any of my existing SQLAlchemy SQLite code will work with 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

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()

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.

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?

推荐答案

The tutorial pointed by you shows the right way of connecting to MySQL using SQLAlchemy. Below is your code with very little changes:

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()

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'

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.

其他推荐答案

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.ext - import extensions directly by their name instead of through the flask.ext namespace. For example, import flask.ext.sqlalchemy becomes import flask_sqlalchemy.