How to create a migration script to add a Geometry column in SQLAlchemy-Migrate?

Question

I'm following the Flask mega tutorial. It all worked well until I tried to add a Geometry column to the database. Note it is in the Post class, called location.

#! model.py
from app import db
from geoalchemy2 import Geometry

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    nickname = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), index=True, unique=True)
    posts = db.relationship('Post', backref='author', lazy='dynamic')
    def __repr__(self):
        return '<User %r>' % (self.nickname)

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.String(140))
    timestamp = db.Column(db.DateTime)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    location = db.Column(Geometry('POINT'))

    def __repr__(self):
       return '<Post %r>' % (self.body)

Then I used db_migrate.py given in the tutorial, but got an error saying name "Geometry" is not defined. Here is the code in db_migrate.py:

#!flask/bin/python
import imp
from migrate.versioning import api
from app import db
from config import SQLALCHEMY_DATABASE_URI
from config import SQLALCHEMY_MIGRATE_REPO
v = api.db_version(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)
migration = SQLALCHEMY_MIGRATE_REPO + ('/versions/%03d_migration.py' % (v+1))
tmp_module = imp.new_module('old_model')
old_model = api.create_model(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)
exec(old_model, tmp_module.__dict__)
script = api.make_update_script_for_model(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO,         tmp_module.meta, db.metadata)
open(migration, "wt").write(script)
api.upgrade(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)
v = api.db_version(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)
print('New migration saved as ' + migration)
print('Current database version: ' + str(v))

Then I found in the file "/versions/%03d_migration.py" that generated by the above codes, Geometry was not imported. So I added

from geoalchemy2 import Geometry

manually, then I ran db_update.py and got the following error.

sqlalchemy.exc.OperationalError: (OperationalError) near "POINT": syntax error u'\nALTER TABLE post ADD location geometry(POINT,-1)' ()

Here is the code for db_upgrade.py:

#!flask/bin/python
from migrate.versioning import api
from config import SQLALCHEMY_DATABASE_URI
from config import SQLALCHEMY_MIGRATE_REPO
api.upgrade(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)
v = api.db_version(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)
print('Current database version: ' + str(v))

I have no idea what to do this time.

So my question is: 1. is there any change I can make to db_migrate.py so that "/versions/%03d_migration.py" will import the class Geometry automatically? 2. Regardless of 1, how to add a geometry column and migrate the database?

Thanks a lot~!

Answer 1

If you are following Miguel Grinberg's tutorial you are using SQLite database.

GeoAlchemy2 – if I'm not wrong – supports only PostgreSQL/PostGIS, as @dirn pointed out in the comment.

The solution would be to get a PostgreSQL server running. SQLAlchemy deals fine with PostreSQL.

Once you got it, just edit your config.py pointing SQLALCHEMY_DATABASE_URI to postgres://... and it should work.

UPDATE

Just saw your reply to @dirn. SQLAlchemy-Migrate is kind of abandoned. The right thing to do would be to use Flask-Migrate, with Alembic and Flask-Script. A little bit of burden if you are a beginner, but will worth it.