Python 如何围绕现有数据库构建烧瓶应用程序？

How to build a flask application around an already existing database?

提问by Indradhanush Gupta

I already have an existing Database that has a lot of tables and a lot of data in MySQL. I intend to create a Flaskapp and use sqlalchemy along with it. Now I asked out on irc and looked around on google and tried the following ideas:

我已经有一个现有的数据库，它有很多表和MySQL. 我打算创建一个Flask应用程序并与它一起使用 sqlalchemy。现在我在 irc 上询问并在谷歌上环顾四周并尝试了以下想法：

FirstI used sqlacodegento generate the models from my DB. But then I was confused about it a little and looked some more. And I found this.

首先，我使用sqlacodegen从我的DB. 但后来我对它有点困惑，又看了一些。我发现了这个。

This looked like an elegant solution.

这看起来是一个优雅的解决方案。

So Second, I rewrote my models.pyaccording to the solution there and now I am even more confused. I am looking for the best approach to build this flask app along with the already existing DB.

所以第二，我models.py根据那里的解决方案重写了我的，现在我更加困惑。我正在寻找构建此 Flask 应用程序以及现有数据库的最佳方法。

I looked into the flask documentation but didnt really get any help for a project with an already existing db. There is a lot of good stuff for creating something from scratch, creating the db and all. But I am really confused.

我查看了烧瓶文档，但并没有真正为已经存在数据库的项目提供任何帮助。从头开始创建东西，创建数据库和所有东西有很多好东西。但我真的很困惑。

Please Note that its my first day with Flask, but I have experience with Django, so the basic concepts are not a hurdle. I need some guidance in choosing the best approach for this usecase. A detailed explanation would be greatly appreciated. By detailed I definitely do not expect someone to write all the code and spoon feed me on this, but just enough to get me started, that is integrate this db seamlessly into flaskvia sqlalchemy. Note my DB is in MySQL.

请注意，这是我第一天使用Flask，但我有使用经验Django，因此基本概念不是障碍。我需要一些指导来为这个用例选择最佳方法。详细的解释将不胜感激。详细地说，我绝对不希望有人写出所有的代码并用勺子喂我，但这足以让我开始，那就是将这个数据库无缝集成到flaskvia sqlalchemy. 注意我的数据库在MySQL.

采纳答案by HolgerSchurig

I'd say your question has nothing to do with flask at all. For example, you don't have a problem with the templates, routes, views or logon decorators.

我会说你的问题根本与烧瓶无关。例如，模板、路由、视图或登录装饰器没有问题。

Where you struggle at is at SQLAlchemy.

您在 SQLAlchemy 中挣扎的地方。

So my suggestion is to ignore Flask for a while and get used to SQLAlchemy first. You need to get used to your existing database and how to access it from SQLAlchemy. Use some MySQL documentation tool to find your way around this. The start with something like this (note that it has nothing to do with Flask ask all ... yet):

所以我的建议是暂时忽略 Flask 并先习惯 SQLAlchemy。您需要习惯现有的数据库以及如何从 SQLAlchemy 访问它。使用一些 MySQL 文档工具来解决这个问题。以这样的事情开始（请注意，它与 Flask ask all ... 无关）：

#!/usr/bin/python
# -*- mode: python -*-

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///webmgmt.db', convert_unicode=True, echo=False)
Base = declarative_base()
Base.metadata.reflect(engine)


from sqlalchemy.orm import relationship, backref

class Users(Base):
    __table__ = Base.metadata.tables['users']


if __name__ == '__main__':
    from sqlalchemy.orm import scoped_session, sessionmaker, Query
    db_session = scoped_session(sessionmaker(bind=engine))
    for item in db_session.query(Users.id, Users.name):
        print item

In the line "engine =" you need to provide your path to your MySQL database, so that SQLAlchemy finds it. In my case I used a pre-existing sqlite3 database.

在“ engine =”行中，您需要提供 MySQL 数据库的路径，以便 SQLAlchemy 找到它。在我的例子中，我使用了一个预先存在的 sqlite3 数据库。

In the line "class Users(Base)" you need to use one of existing tables in your MySQL database. I knew that my sqlite3 database had a table named "users".

在“ class Users(Base)”行中，您需要使用 MySQL 数据库中的现有表之一。我知道我的 sqlite3 数据库有一个名为“users”的表。

After this point, SQLalchemy knows how to connect to your MySQL database and it knows about one of the tables. You need now to add all the other tables that you care for. Finally, you need to specify relationships to SQLalchemy. Here I mean things like one-to-one, one-to-many, many-to-many, parent-child and so on. The SQLAlchemy web site contains a rather lenghty section about this.

在这一点之后，SQLalchemy 知道如何连接到您的 MySQL 数据库并且它知道其中一个表。您现在需要添加您关心的所有其他表。最后，您需要指定与 SQLalchemy 的关系。这里我指的是一对一、一对多、多对多、亲子等。SQLAlchemy 网站包含有关此内容的相当长的部分。

After the line "if __name__ == '__main__'" just comes some test code. It will be executed if I don't import my python script, but run. Here you see that I create a DB session and is that for a very simply query.

在“ if __name__ == '__main__'”行之后只是一些测试代码。如果我不导入我的 python 脚本，它将被执行，而是运行。在这里您可以看到我创建了一个数据库会话，并且是用于非常简单的查询。

My suggestion is that you first read about the important parts of SQLAlchemy's documentation, for example the descriptive table definition, the relationship model and how to query. Once you know this, you can change the last part of my example into a controller (e.g. using Python's yieldmethod) and write a view that uses that controller.

我的建议是，您首先阅读 SQLAlchemy 文档的重要部分，例如描述性表定义、关系模型和如何查询。一旦你知道了这一点，你就可以将我示例的最后一部分更改为一个控制器（例如使用 Python 的yield方法）并编写一个使用该控制器的视图。

回答by Chris McKinnel

I recently went through the same thing, with the additional challenge of linking the models across two databases.

我最近经历了同样的事情，面临着跨两个数据库链接模型的额外挑战。

I used Flask-SQLAlchemyand all I had to do was define my models in the same way as my database tables looked. What I found difficult was figuring out exactly what my project structure should look like.

我使用了Flask-SQLAlchemy，我所要做的就是以与我的数据库表相同的方式定义我的模型。我发现困难的是弄清楚我的项目结构应该是什么样子。

My project was a Restful API, and this is what I ended up with:

我的项目是一个 Restful API，这就是我最终的结果：

conf/
    __init__.py
    local.py
    dev.py
    stage.py
    live.py
deploy/
    #nginx, uwsgi config, etc
middleware/
    authentication.py
app_name/
    blueprints/
        __init__.py
        model_name.py #routes for model_name
        ...
    models/
        __init.py
        model_name.py
    __init__.py
    database.py
tests/
    unit/
        test_etc.py
        ...
run.py

Files of note:

注意文件：

conf/xxx.py

配置文件/xxx.py

This is how we tell Flask-SQLAlchemy what to connect to, plus you can put any other config items in here (like log location, debugging config, etc).

这就是我们告诉 Flask-SQLAlchemy 连接什么的方式，此外您还可以在此处放置任何其他配置项（例如日志位置、调试配置等）。

SQLALCHEMY_DATABASE_URI = 'mysql://username:password@host:port/db_name'

app_name/__init__.py

app_name/__init__.py

This is where I create my app and initialise the db. This db object will be imported and used across the entire app (i.e., in the models, tests, etc). I also set my logger, initialise my APIs and blueprints and attach my middleware in here (not shown).

这是我创建我的应用程序并初始化数据库的地方。这个 db 对象将被导入并在整个应用程序中使用（即，在模型、测试等中）。我还设置了我的记录器，初始化了我的 API 和蓝图，并在此处附加了我的中间件（未显示）。

from app_name.database import db
from flask import Flask

def create_app(*args, **kwargs):
    env = kwargs['env']
    app = Flask(__name__)
    app.config.from_object('conf.%s' % env)
    db.init_app(app)
    return app

app_name/database.py

app_name/database.py

from flask.ext.sqlalchemy import SQLAlchemy
db = SQLAlchemy()

app_name/models/model_name.py

app_name/models/model_name.py

from services.database import db


class Bar(db.Model):

    __tablename__ = 'your_MySQL_table_name'

    id = db.Column('YourMySQLColumnName', db.Integer, primary_key=True)
    name = db.Column('WhateverName', db.String(100))
    foo = db.Column(db.ForeignKey('another_MySQLTableName.id'))

class Foo(db.Model):

    __tablename__ = 'another_MySQLTableName'

    id = db.Column('FooId', db.Integer, primary_key=True)
    ...

run.py

运行文件

#! /usr/bin/env python

from app_name import create_app

app = create_app(env='local')

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

I use run.pyto run the app locally, but I use nginx + uWSGI to run the app in the dev/stage/live environments.

我曾经run.py在本地运行应用程序，但我使用 nginx + uWSGI 在开发/阶段/实时环境中运行应用程序。

I'm guessing you'll have a views/directory in there in addition to this though.

我猜views/除了这个之外你还会有一个目录。

回答by xysun

The key to connecting Holger's answer to a flask context is that db.Modelis a declarative_baseobject like Base. Took me a while to notice this important sentence in flask-sqlalchemy's documentation

将 Holger 的答案与烧瓶上下文联系起来的关键是它db.Model是一个declarative_base像Base. 我花了一段时间才注意到flask-sqlalchemy文档中的这个重要句子

Below are the steps I used for my app:

以下是我用于我的应用程序的步骤：

1. initiate a dbobject in the usual flask-alchemy manner:db = SQLAlchemy(app). Note you'll need to set app.config['SQLALCHEMY_DATABASE_URI'] = 'connection_string'before that.

2. bind the declarative base to an engine: db.Model.metadata.reflect(db.engine)

3. Then you can use existing tables easily (eg. I have a table called BUILDINGS):

   class Buildings(db.Model):
       __table__ = db.Model.metadata.tables['BUILDING']
   
       def __repr__(self):
           return self.DISTRICT
   

1. 发起db在通常烧瓶-炼金术方式对象：db = SQLAlchemy(app)。请注意，您需要在此app.config['SQLALCHEMY_DATABASE_URI'] = 'connection_string'之前进行设置。

2. 将声明性基础绑定到引擎： db.Model.metadata.reflect(db.engine)

3. 然后您可以轻松使用现有表（例如，我有一个名为 BUILDINGS 的表）：

   class Buildings(db.Model):
       __table__ = db.Model.metadata.tables['BUILDING']
   
       def __repr__(self):
           return self.DISTRICT
   

Now your Buildingsclass will follow the existing schema. You can try dir(Buildings)in a Python shell and see all the columns already listed.

现在您的Buildings班级将遵循现有模式。您可以尝试dir(Buildings)在 Python shell 中查看已列出的所有列。

回答by sequoia

This is an alternative way to set up the engine path described in Holger's answer. Convenient if there are special characters in your user name or password.

这是设置 Holger 回答中描述的引擎路径的另一种方法。如果您的用户名或密码中有特殊字符很方便。

from sqlalchemy.engine.url import URL
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

engine_URL = URL('mssql+pymssql',
                 username='DOMAIN\USERNAME', 
                 password="""p@ssword'!""", 
                 host='host.com', 
                 database='database_name')

engine = create_engine(engine_URL)
Base = declarative_base()
Base.metadata.reflect(engine)

回答by droidmad

I think the easiest way to use an existing database with sqlalchemy is to use AutomapBaseclass . A sample code from docs is as follows:

我认为将现有数据库与sqlalchemy一起使用的最简单方法是使用AutomapBase类。来自 docs 的示例代码如下：

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine

Base = automap_base()

# engine, suppose it has two tables 'user' and 'address' set up
engine = create_engine("sqlite:///mydatabase.db")

# reflect the tables
Base.prepare(engine, reflect=True)

# mapped classes are now created with names by default
# matching that of the table name.
User = Base.classes.user
Address = Base.classes.address

session = Session(engine)

# rudimentary relationships are produced
session.add(Address(email_address="[email protected]", user=User(name="foo")))
session.commit()

# collection-based relationships are by default named
# "<classname>_collection"
print (u1.address_collection)

Refer SqlAlchemy-Automapfor details and more complicated usages

有关详细信息和更复杂的用法，请参阅SqlAlchemy-Automap

回答by droidmad

This solution worked for me

这个解决方案对我有用

"""Example for reflecting database tables to ORM objects

This script creates classes for each table reflected
from the database.

Note: The class names are imported to the global namespace using
the same name as the tables. This is useful for quick utility scripts.
A better solution for production code would be to return a dict
of reflected ORM objects.
"""

from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base


def reflect_all_tables_to_declarative(uri):
"""Reflects all tables to declaratives

Given a valid engine URI and declarative_base base class
reflects all tables and imports them to the global namespace.

Returns a session object bound to the engine created.
"""

# create an unbound base our objects will inherit from
Base = declarative_base()

engine = create_engine(uri)
metadata = MetaData(bind=engine)
Base.metadata = metadata

g = globals()

metadata.reflect()

for tablename, tableobj in metadata.tables.items():
    g[tablename] = type(str(tablename), (Base,), {'__table__' : tableobj })
    print("Reflecting {0}".format(tablename))

Session = sessionmaker(bind=engine)
return Session()


# set to database credentials/host
CONNECTION_URI = "postgres://..."

session = reflect_all_tables_to_declarative(CONNECTION_URI)

# do something with the session and the orm objects
results = session.query(some_table_name).all()

回答by mcolak

I try to use autogenerated but nothing works or I couldn't run it. When I searching generate code using sqlacodegen I find https://github.com/ksindi/flask-sqlacodegen, you can generate the code just

我尝试使用自动生成，但没有任何效果，或者我无法运行它。当我使用 sqlacodegen 搜索生成代码时，我发现https://github.com/ksindi/flask-sqlacodegen，您可以生成代码

flask-sqlacodegen  mysql://username:password@host:port/db_name --schema yourschema --tables table1,table2 --flask

I tried and it works perfectly

我试过了，效果很好

回答by Adam Greenhall

alembic (the tool behind flask-sqlalchemy) can be configured to ignore tables. The configuration isn't too hard to setup. see: https://gist.github.com/utek/6163250

alembic（flask-sqlalchemy 背后的工具）可以配置为忽略表。配置并不难设置。见：https: //gist.github.com/utek/6163250

回答by Sree Bhargava

I'd suggest using dynamic class

我建议使用动态类

from flask import Flask
from sqlalchemy import Table, MetaData
from flask_sqlalchemy import SQLAlchemy
import os

app = Flask(__name__)

class Config(Object):
    basedir = os.path.abspath(os.path.dirname(__file__))
    SQLALCHEMY_DATABASE_URI = 'sqlite:///' +  + os.path.join(basedir, 'db.sqlite') 
    SQLALCHEMY_TRACK_MODIFICATIONS = False

app.config.from_object(config)
db = SQLAlchemy(app)
metadata = MetaData()

table_reflection = Table("table_name", metadata, autoload=True, autoload_with=db.engine)
attrs = {"__table__": table_reflection}
TableModel = type("table_name", (db.Model,), attrs)

Now, TableModel can be used as models class

现在，TableModel 可以用作模型类

# Querying
TableModel.query.all()
TableModel.query.get(1)
TableModel.query.filter_by(id=2).first()

# insertion
new_row = TableModel()
db.session.add(new_row)
db.session.commit()