I have installed the following packages -
SQLAlchemy==1.4.41
sqlalchemy-spanner==1.2.2
alembic==1.8.1
Then I created a file main.py where I am creating a SQLAlchemy engine for cloud spanner.
from sqlalchemy import create_engine, MetaData
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine(
"spanner+spanner:///projects/spanner-project/instances/spanner-instance/databases/spanner-database"
)
metadata = MetaData()
Base = declarative_base(metadata=metadata)
Then in alembic.ini I have used the same url as shown above for engine.
sqlalchemy.url = spanner+spanner:///projects/spanner-project/instances/spanner-instance/databases/spanner-database
Also in env.py I have set the base's matadata as shown below -
from app.main import Base
target_metadata = Base.metadata
I also have a models.py which has just one model as shown below -
from app.main import Base
from sqlalchemy import (
Column,
Integer,
String,
)
class Users(Base):
__tablename__ = "users"
__table_args__ = {
'extend_existing': True,
}
id = Column(Integer, primary_key=True)
name = Column(String(50))
When I run the alembic command to generate migrations I see an exception of KeyError: 'spanner+spanner'.
CMD -
alembic revision --autogenerate -m "first migrations"
Exception -
Traceback (most recent call last):
File "/usr/local/bin/alembic", line 8, in <module>
sys.exit(main())
File "/usr/local/lib/python3.10/site-packages/alembic/config.py", line 590, in main
CommandLine(prog=prog).main(argv=argv)
File "/usr/local/lib/python3.10/site-packages/alembic/config.py", line 584, in main
self.run_cmd(cfg, options)
File "/usr/local/lib/python3.10/site-packages/alembic/config.py", line 561, in run_cmd
fn(
File "/usr/local/lib/python3.10/site-packages/alembic/command.py", line 229, in revision
script_directory.run_env()
File "/usr/local/lib/python3.10/site-packages/alembic/script/base.py", line 569, in run_env
util.load_python_file(self.dir, "env.py")
File "/usr/local/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
module = load_module_py(module_id, path)
File "/usr/local/lib/python3.10/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 883, in exec_module
File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
File "/code/app/alembic/env.py", line 79, in <module>
run_migrations_online()
File "/code/app/alembic/env.py", line 68, in run_migrations_online
context.configure(
File "<string>", line 8, in configure
File "/usr/local/lib/python3.10/site-packages/alembic/runtime/environment.py", line 822, in configure
self._migration_context = MigrationContext.configure(
File "/usr/local/lib/python3.10/site-packages/alembic/runtime/migration.py", line 268, in configure
return MigrationContext(dialect, connection, opts, environment_context)
File "/usr/local/lib/python3.10/site-packages/alembic/runtime/migration.py", line 196, in __init__
self.impl = ddl.DefaultImpl.get_by_dialect(dialect)(
File "/usr/local/lib/python3.10/site-packages/alembic/ddl/impl.py", line 123, in get_by_dialect
return _impls[dialect.name]
I tried to by changing the URL in alembic.ini from spanner+sapnner:///..... to spanner:///... but then I got this exception -
Traceback (most recent call last):
File "/usr/local/bin/alembic", line 8, in <module>
sys.exit(main())
File "/usr/local/lib/python3.10/site-packages/alembic/config.py", line 590, in main
CommandLine(prog=prog).main(argv=argv)
File "/usr/local/lib/python3.10/site-packages/alembic/config.py", line 584, in main
self.run_cmd(cfg, options)
File "/usr/local/lib/python3.10/site-packages/alembic/config.py", line 561, in run_cmd
fn(
File "/usr/local/lib/python3.10/site-packages/alembic/command.py", line 229, in revision
script_directory.run_env()
File "/usr/local/lib/python3.10/site-packages/alembic/script/base.py", line 569, in run_env
util.load_python_file(self.dir, "env.py")
File "/usr/local/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
module = load_module_py(module_id, path)
File "/usr/local/lib/python3.10/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 883, in exec_module
File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
File "/code/app/alembic/env.py", line 79, in <module>
run_migrations_online()
File "/code/app/alembic/env.py", line 61, in run_migrations_online
connectable = engine_from_config(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 743, in engine_from_config
return create_engine(url, **options)
File "<string>", line 2, in create_engine
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py", line 309, in warned
return fn(*args, **kwargs)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 522, in create_engine
entrypoint = u._get_entrypoint()
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/url.py", line 655, in _get_entrypoint
cls = registry.load(name)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 343, in load
raise exc.NoSuchModuleError(
sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:spanner
Also when I use spanner+spanner:///... to creating and engine and then further use it to create db tables then I am able to do that successfully.
from app.main import engine, Base
from sqlalchemy import (
Column,
Integer,
String,
)
class Users(Base):
__tablename__ = "users"
__table_args__ = {
'extend_existing': True,
}
id = Column(Integer, primary_key=True)
name = Column(String(50))
Base.metadata.create_all(engine)
Just by running this model I am able to create the table.
python models.py
What do I do to use alembic with spanner?
You're using SQLAlchemy 1.4, which means it must be everywhere spanner+spanner. Just spanner will not work. As the problem appears only for Alembic, reinstall will also not help - the problem is in configurations somewhere.
Can you show your env.py file? There must be written something like this:
class SpannerImpl(DefaultImpl):
__dialect__ = "spanner+spanner"
Looking at your traceback, I suspect, in the env.py the dialect is written as just spanner, which causes the error.