Python externalise database model file

Question

I'm building a web API in Python flask.

... which needs library SQLAlchemy for db connectivity

... which needs marshmallow to render database results as JSON

Working through a number of examples I now have this code in my app.py file which returns a JSON representation of the table successfully.

from flask import Flask, request
from flask_sqlalchemy import SQLAlchemy
from marshmallow import Schema, fields, ValidationError, pre_load
from flask import jsonify
import pyodbc

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = 'mssql+pyodbc://localhost/database?driver=SQL+Server+Native+Client+11.0'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db=SQLAlchemy(app)

### MODELS ###
class Table1(db.Model):
    __table_args__ = {'schema': 'MySchema'}
    MyId=db.Column(db.Integer, primary_key=True)
    Field1=db.Column(db.Integer)
    Field2=db.Column(db.Integer)

### Schemas ###
class Table1Schema(Schema):
    MyId=fields.Integer(dump_only=True)
    Field1=fields.Integer()
    Field2=fields.Integer()

Table1_schema=Table1Schema(many=True)

#### ROUTES ####

@app.route('/')
def index():
    return 'Index'

@app.route('/Table2')
def Table2():
    rows=Table1.query.all()
    return jsonify(Table1_schema.dump(rows))

Between MODELS and ROUTES I have a LOT of model code

I want to put this model code in a seperate file but I'm not sure how to split it.

I tried moving all of that model code into it's own file and importing after I define db but it doesn't see db.

NameError: name 'db' is not defined

from flask import Flask, request
from flask_sqlalchemy import SQLAlchemy
from marshmallow import Schema, fields, ValidationError, pre_load
from flask import jsonify
import pyodbc

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = 'mssql+pyodbc://localhost/database?driver=SQL+Server+Native+Client+11.0'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db=SQLAlchemy(app)

import mymodelfile

How is this kind of thing normally split? Can I just define my models and schemas with a dummy class? I also tried recoding like this sample https://docs.sqlalchemy.org/en/13/orm/tutorial.html but after a lot of rewrite I get this

AttributeError: 'NoneType' object has no attribute '_autoflush'

There seems to be a lot of different ways to go about the well trodden path of defining database models. Maybe there is an easier way?

The short question is: has anyone used flask+sqlalchemy to make an API? (maybe marshmallow is not required) How did you split your code ?

Answer 1

This is what I did to get this working. I was actually almost there but I got completely sidetracked by Base = declarative_base()

This gave me some clues that I needed

https://flask-sqlalchemy.palletsprojects.com/en/2.x/contexts/

I have to say I'm suprised that flask+sqlalchemy still doesn't have a simple way to return a table as JSON. It took a lot of research into dusty corners to work out how to build a database API that returns JSON

If anyone knows an easier way to get SQLAlchemy (or another library) to easily return JSON, I'm all ears.

app.py

from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy 
import pyodbc
# This is my external model file
import dbmodel

# set up flask app and sqlalchemy
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = 'mssql+pyodbc://MySQLServerHost/MySQLServerDB?driver=SQL+Server+Native+Client+11.0'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db=SQLAlchemy(app)

# This is the actual flask code that defines the route and returns the data as JSON
@app.route('/MyTable')
def MyTable():
    rows=dbmodel.MyTable.query.all()
    return jsonify(dbmodel.mytable_schema.dump(rows))

dbmodel.py

from flask_sqlalchemy import SQLAlchemy 
from marshmallow import Schema, fields, ValidationError, pre_load
from marshmallow_sqlalchemy import SQLAlchemyAutoSchema

# https://flask-sqlalchemy.palletsprojects.com/en/2.x/contexts/

db=SQLAlchemy()

# SQLAlchemy Models
class MyTable(db.Model):
    __table_args__ = {'schema': 'MyDBSchema'}
    Col1=db.Column(db.Integer, primary_key=True)
    Col2=db.Column(db.Integer)
    Col3=db.Column(db.Integer)

# marshmallow schema
# Seems like marshmallow is the easiest way to create a json response from SQLAlchemy
class MyTableSchema(SQLAlchemyAutoSchema):
    class Meta:
        model=MyTable


# last piece of the puzzle to use marshmallow
mytable_schema=MyTableSchema(many=True)