How to make MYSQL Reverse Bank Engineering with SqlAlchemy?

2

I asked this question on Stackoverflow these days ago:

How to import database to Django models?

But now, I need something similar for Flask .

I have a bank ready, using MYSQL .

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

How can I do this?

    
asked by anonymous 11.05.2016 / 18:35

1 answer

1

Having the tables in memory as Python objects is something that SqlAlchemy does alone.

However, from there, generating a '.py' file with the templates themselves, is something that needs to be done manually - you create the text of the file as a string, and write it to a file - you can use one of the template engines, such as those already used in frameworks, or the Python format itself -

So to introspect a bank 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: link )

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 skeleton above works, as I do not need at the moment, I just did not elaborate on what goes into the string of 'extras' which are all the other parameters that are used to construct a column - the data is available in the object type "column" - I suggest you use the Python interactive terminal, get a column object of those, see the available attributes with dir and use them to complete the above function.

    
12.05.2016 / 16:23