pythonflasksqlalchemyflask-sqlalchemygeoalchemy2

How to use an existing PostGIS database in a Flask web app

I'm building a web app using Flask and a PostGIS database I already created. I'm struggling to get Flask-SQLAlchemy to accept the geom column of my existing database. I declare db in an init.py file:

from flask import Flask, request, current_app
from config import Config
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

import os, logging

db = SQLAlchemy()
migrate = Migrate()

def create_app(config_class=Config):
    app = Flask(__name__)
    app.config.from_object(config_class)

    db.init_app(app)
    migrate.init_app(app, db)

My code for my models.py file looks like this:

from app import login, db
from datetime import datetime
from geoalchemy2 import Geometry
from time import time
from flask import current_app

class Streets(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    street = db.Column(db.String(50))
    geom = db.GeometryColumn(db.LineString(2))

The error I get is: AttributeError: 'SQLAlchemy' object has no attribute 'GeometryColumn'

And if I try to remove db. from the geom line, I get this error: NameError: name 'GeometryColumn' is not defined

Because Flask-SQLAlchemy wants you to declare a column using db.Column, it seems to override geoalchemy2. Has anyone found a solution to this?

Solution

  • It does not override GeoAlchemy2. You could use GeometryColumn as a column, if you were using the previous version of GeoAlchemy.

    The first error is caused by the fact that the SQLAlchemy object from Flask-SQLAlchemy gives you access to functions etc. from sqlalchemy and sqlalchemy.orm. It does not include stuff from GeoAlchemy2 or such.

    The second error is due to not having the name GeometryColumn in scope. You do import Geometry from geoalchemy2, but don't use it.

    Reading the GeoAlchemy2 ORM tutorial you'd notice that geometry columns are defined as

    geom = Column(Geometry('POLYGON'))

    or in your case

    geom = db.Column(Geometry('LINESTRING'))  # dimension defaults to 2

    Note that db.Column is sqlalchemy.schema.Column, just in another namespace.