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~!
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
pointingSQLALCHEMY_DATABASE_URI
topostgres://...
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.