How to Reverse Engineer MYSQL Database with Sqlalchemy?

Question

I asked that question here at Stackoverflow the other day:

How to import database to Django models?

But now, I need something similar to Flask.

I have a bank ready, using the MYSQL.

I want to reverse engineer my database, turning all the data into Models of SqlAlchemy.

How can I do that?

Answer 1

Having tables in memory as Pythonicos objects is something Sqlalchemy does alone.

However, if appropriate, generate a '.py' file with the templates themselves, is something that needs to be done manually - you create the file text as a string, and save it to a file - you can use one of the template Engines, like the ones that are already used in frameworks, or the Python format itself -

So, to introspect a database and have the table objects 'alive':

from sqlalchemy import create_engine
from sqlalchemy.engine import reflection
engine = create_engine('<sua url>')
insp = reflection.Inspector.from_engine(engine)
print (insp.get_table_names())

(code of: http://docs.sqlalchemy.org/en/latest/core/reflection.html)

A skeleton of how to generate Python code from introspection would be

from sqlalchemy import Table, MetaData, create_engine
from sqlalchemy.engine import reflection

engine = create_engine('<sua url>')


def create_schema_source():

    result = """\from app import db\n\n"""
    meta = MetaData()
    for table_name in engine.table_names():
        table_text = """class {}(db.Model):\n""".format(table_name.title().replace("_",""))
        table = Table(table_name, m, autoload_with=engine)
        for column in table.columns:
            col_text = """    {name} = db.Column(db.{type}(), {{extras}})\n""".format(
                name=column.name, 
                type=repr(column.type).strip('()'),
                )
            extras = ""
            # neste ponto inspecionar os atributos de column em que estiver interessado
            # e montar a string extras com alguns 'if' - 
            # ex.: ( column.foreign_keys, column.primary_key)
            table_text += col_text.format(extras=extras)
        result +=  table_text + "\n\n"
    return result

print(create_schema_source())

The above skeleton works, as I do not need at the moment, I just did not lengthen in detailing what enters the 'extras" string that are all other parameters that are used to build a column - the data is available in the object of type "column' - I suggest you use the interactive Python terminal, get a column object of these, see the available attributes with dir and use them to complete the above function.