from geoalchemy2 import Geometry
class ShopDetail(db.Model):
__tablename__ = 'tbl_shop_detail'
id = db.Column(BIGINT(unsigned=True), primary_key=True)
location = db.Column(Geometry("POINT")) # 经纬度坐标
def __repr__(self):
return '<Shop %r>' % self.id```
Error when executing database migration statement Report an error
python manager.py db init
python manager.py db migrate
this is error info
Server initialized for threading.
main--->
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.autogenerate.compare] Detected added table 'tbl_shop_detail'
INFO [alembic.autogenerate.compare] Detected added index 'idx_tbl_shop_detail_location' on '['location']'
migrations/env.py:84: SAWarning: Did not recognize type 'point' of column 'location'
context.run_migrations()
Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
self.dialect.do_execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
cursor.execute(statement, parameters)
File "/usr/local/lib/python3.9/site-packages/pymysql/cursors.py", line 158, in execute
result = self._query(query)
File "/usr/local/lib/python3.9/site-packages/pymysql/cursors.py", line 325, in _query
conn.query(q)
File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 549, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 779, in _read_query_result
result.read()
File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 1157, in read
first_packet = self.connection._read_packet()
File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 729, in _read_packet
packet.raise_for_error()
File "/usr/local/lib/python3.9/site-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "/usr/local/lib/python3.9/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1054, "Unknown column 'SRS_ID' in 'field list'")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/root/workspace/DahuaBackend_member/manager.py", line 18, in <module>
sys.exit(main()) # Python的程序退出方式
File "/root/workspace/DahuaBackend_member/manager.py", line 12, in main
manager.run(default_command='runserver')
File "/usr/local/lib/python3.9/site-packages/flask_script/__init__.py", line 417, in run
result = self.handle(argv[0], argv[1:])
File "/usr/local/lib/python3.9/site-packages/flask_script/__init__.py", line 386, in handle
res = handle(*args, **config)
File "/usr/local/lib/python3.9/site-packages/flask_script/commands.py", line 216, in __call__
return self.run(*args, **kwargs)
File "/usr/local/lib/python3.9/site-packages/flask_migrate/__init__.py", line 96, in wrapped
f(*args, **kwargs)
File "/usr/local/lib/python3.9/site-packages/flask_migrate/__init__.py", line 210, in migrate
command.revision(config, message, autogenerate=True, sql=sql,
File "/usr/local/lib/python3.9/site-packages/alembic/command.py", line 236, in revision
script_directory.run_env()
File "/usr/local/lib/python3.9/site-packages/alembic/script/base.py", line 582, in run_env
util.load_python_file(self.dir, "env.py")
File "/usr/local/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
module = load_module_py(module_id, path)
File "/usr/local/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
spec.loader.exec_module(module) # type: ignore
File "<frozen importlib._bootstrap_external>", line 850, in exec_module
File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
File "migrations/env.py", line 90, in <module>
run_migrations_online()
File "migrations/env.py", line 84, in run_migrations_online
context.run_migrations()
File "<string>", line 8, in run_migrations
File "/usr/local/lib/python3.9/site-packages/alembic/runtime/environment.py", line 928, in run_migrations
self.get_context().run_migrations(**kw)
File "/usr/local/lib/python3.9/site-packages/alembic/runtime/migration.py", line 615, in run_migrations
for step in self._migrations_fn(heads, self):
File "/usr/local/lib/python3.9/site-packages/alembic/command.py", line 212, in retrieve_migrations
revision_context.run_autogenerate(rev, context)
File "/usr/local/lib/python3.9/site-packages/alembic/autogenerate/api.py", line 562, in run_autogenerate
self._run_environment(rev, migration_context, True)
File "/usr/local/lib/python3.9/site-packages/alembic/autogenerate/api.py", line 609, in _run_environment
compare._populate_migration_script(
File "/usr/local/lib/python3.9/site-packages/alembic/autogenerate/compare.py", line 59, in _populate_migration_script
_produce_net_changes(autogen_context, upgrade_ops)
File "/usr/local/lib/python3.9/site-packages/alembic/autogenerate/compare.py", line 93, in _produce_net_changes
comparators.dispatch("schema", autogen_context.dialect.name)(
File "/usr/local/lib/python3.9/site-packages/alembic/util/langhelpers.py", line 269, in go
fn(*arg, **kw)
File "/usr/local/lib/python3.9/site-packages/alembic/autogenerate/compare.py", line 132, in _autogen_for_tables
_compare_tables(
File "/usr/local/lib/python3.9/site-packages/alembic/autogenerate/compare.py", line 246, in _compare_tables
sqla_compat._reflect_table(inspector, t)
File "/usr/local/lib/python3.9/site-packages/alembic/util/sqla_compat.py", line 308, in _reflect_table
return inspector.reflect_table(table, None)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py", line 1554, in reflect_table
self._reflect_column(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py", line 1630, in _reflect_column
table.dispatch.column_reflect( # type: ignore[attr-defined]
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/event/attr.py", line 485, in __call__
fn(*args, **kw)
File "/usr/local/lib/python3.9/site-packages/geoalchemy2/admin/__init__.py", line 100, in _reflect_geometry_column
select_dialect(inspector.bind.dialect.name).reflect_geometry_column(
File "/usr/local/lib/python3.9/site-packages/geoalchemy2/admin/dialects/mysql.py", line 41, in reflect_geometry_column
geometry_type, srid, nullable_str = inspector.bind.execute(text(geometry_type_query)).one()
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1412, in execute
return meth(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 483, in _execute_on_connection
return connection._execute_clauseelement(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1635, in _execute_clauseelement
ret = self._execute_context(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1844, in _execute_context
return self._exec_single_context(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1984, in _exec_single_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
self.dialect.do_execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
cursor.execute(statement, parameters)
File "/usr/local/lib/python3.9/site-packages/pymysql/cursors.py", line 158, in execute
result = self._query(query)
File "/usr/local/lib/python3.9/site-packages/pymysql/cursors.py", line 325, in _query
conn.query(q)
File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 549, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 779, in _read_query_result
result.read()
File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 1157, in read
first_packet = self.connection._read_packet()
File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 729, in _read_packet
packet.raise_for_error()
File "/usr/local/lib/python3.9/site-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "/usr/local/lib/python3.9/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1054, "Unknown column 'SRS_ID' in 'field list'")
[SQL: SELECT DATA_TYPE, SRS_ID, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'mt_scale' and COLUMN_NAME = 'location' and table_schema = 'my_test']
How to use longitude and latitude to calculate distance in flask? When I use geoalchemy2, I get an error when merging and migrating the database. How can I correctly migrate and merge the database?
Use Column(Geometry(geometry_type='POINT', srid=4326))
Not good either