1

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~!

Danny Wang
  • 429
  • 9
  • 24
  • What database are you using? GeoAlchemy2 only supports PostgreSQL/PostGIS. – dirn Dec 17 '14 at 19:13
  • 1
    @dim That's a good point. I was using sqlite. But then I tried everything again on PostgreSQL/PostGIS. db_migrate.py still give the error "name "Geometry" is not defined". I found some post saying that sqlalchemy-migrate does not support geoalchemy back in 2012. Is it still true? In that case, what would be a good replacement for sqlalchemy-migrate? Thanks a lot~! – Danny Wang Dec 18 '14 at 09:43

1 Answers1

2

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.

cuducos
  • 730
  • 6
  • 17
  • Very clear answer. Do you think Flask-Migrate or Alembic support geoalchemy? In case others come to the same point as I did, here is the next stop:[Flask-Migrate tutorial](http://blog.miguelgrinberg.com/post/flask-migrate-alembic-database-migration-wrapper-for-flask) – Danny Wang Dec 18 '14 at 10:53
  • I'm really not sure, but I'd say so. The thing is that GeoAlchemy2 doesn't support SQLAlchemy 0.7 (or oder). SQLALchemy today is on version 1.0. Alembic works with these more recent versions of SQLAlchemy (1,0, 0.9, 0.8…). As Geoalchemy is an SQLAlchemy _extention_, I believe they should get along well in most updated versions… – cuducos Dec 18 '14 at 11:01
  • I was going to recommend Flask-Migrate (it's a wrapper around alembic). It won't automatically pull in GeoAlchemy2, though. You're still going to have to add `import geoalchemy2` into each migration that needs it. It's hard for alembic to be aware of all of the possible SQLAlchemy extensions, so anything that uses a type not supported directly by SQLAlchemy needs a little manual intervention. – dirn Dec 18 '14 at 16:38
  • 3
    @dim i put "import geoalchemy2" into the script template file "script.py.mako". so no need to make the change for every migration. everything works now:) – Danny Wang Dec 18 '14 at 20:43