I initialized the asynchronous alembic directory for base migrations, specified the base path in the .ini (I created the migration for an unchanged model and loaded it successfully and the file recorded recent changes, so I think the path is correct), and also specified target_metadata in env.py
The problem arose when I changed the column in the model class (added the Optional annotation)
To create the migration I used:
alembic revision --autogenerate -m "message"
To load the migration I used:
alembic upgrade head
The following exception occurred just while executing the last one:
(venv) PS C:\MyFolder\python\discord\logika bot> alembic upgrade head
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade 5fefb216ecaf -> 9bce8e04e73c, make presence column in Mark Optional
Traceback (most recent call last):
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1969, in _exec_single_context
self.dialect.do_execute(
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\engine\default.py", line 922, in do_execute
cursor.execute(statement, parameters)
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\dialects\sqlite\aiosqlite.py", line 146, in execute
self._adapt_connection._handle_exception(error)
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\dialects\sqlite\aiosqlite.py", line 298, in _handle_exception
raise error
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\dialects\sqlite\aiosqlite.py", line 128, in execute
self.await_(_cursor.execute(operation, parameters))
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 130, in await_only
return current.driver.switch(awaitable) # type: ignore[no-any-return]
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 195, in greenlet_spawn
value = await result
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\aiosqlite\cursor.py", line 48, in execute
await self._execute(self._cursor.execute, sql, parameters)
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\aiosqlite\cursor.py", line 40, in _execute
return await self._conn._execute(fn, *args, **kwargs)
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\aiosqlite\core.py", line 133, in _execute
return await future
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\aiosqlite\core.py", line 106, in run
result = function()
sqlite3.OperationalError: near "ALTER": syntax error
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\Users\admin\AppData\Local\Programs\Python\Python310\lib\runpy.py", line 196, in _run_module_as_main
return _run_code(code, main_globals, None,
File "C:\Users\admin\AppData\Local\Programs\Python\Python310\lib\runpy.py", line 86, in _run_code
exec(code, run_globals)
File "C:\MyFolder\python\discord\logika bot\venv\Scripts\alembic.exe\__main__.py", line 7, in <module>
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\alembic\config.py", line 641, in main
CommandLine(prog=prog).main(argv=argv)
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\alembic\config.py", line 631, in main
self.run_cmd(cfg, options)
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\alembic\config.py", line 608, in run_cmd
fn(
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\alembic\command.py", line 403, in upgrade
script.run_env()
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\alembic\script\base.py", line 583, in run_env
util.load_python_file(self.dir, "env.py")
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\alembic\util\pyfiles.py", line 95, in load_python_file
module = load_module_py(module_id, path)
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\alembic\util\pyfiles.py", line 113, 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 "C:\MyFolder\python\discord\logika bot\migrations\env.py", line 91, in <module>
run_migrations_online()
File "C:\MyFolder\python\discord\logika bot\migrations\env.py", line 85, in run_migrations_online
asyncio.run(run_async_migrations())
File "C:\Users\admin\AppData\Local\Programs\Python\Python310\lib\asyncio\runners.py", line 44, in run
return loop.run_until_complete(main)
File "C:\Users\admin\AppData\Local\Programs\Python\Python310\lib\asyncio\base_events.py", line 649, in run_until_complete
return future.result()
File "C:\MyFolder\python\discord\logika bot\migrations\env.py", line 77, in run_async_migrations
await connection.run_sync(do_run_migrations)
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\ext\asyncio\engine.py", line 891, in run_sync
return await greenlet_spawn(fn, self._proxied, *arg, **kw)
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 202, in greenlet_spawn
result = context.switch(value)
File "C:\MyFolder\python\discord\logika bot\migrations\env.py", line 61, in do_run_migrations
context.run_migrations()
File "<string>", line 8, in run_migrations
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\alembic\runtime\environment.py", line 948, in run_migrations
self.get_context().run_migrations(**kw)
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\alembic\runtime\migration.py", line 627, in run_migrations
step.migration_fn(**kw)
File "C:\MyFolder\python\discord\logika bot\migrations\versions\9bce8e04e73c_make_presence_column_in_mark_optional.py", line 23, in upgrade
op.alter_column('marks', 'presence',
File "<string>", line 8, in alter_column
File "<string>", line 3, in alter_column
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\alembic\operations\ops.py", line 1943, in alter_column
return operations.invoke(alt)
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\alembic\operations\base.py", line 445, in invoke
return fn(self, operation)
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\alembic\operations\toimpl.py", line 53, in alter_column
operations.impl.alter_column(
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\alembic\ddl\impl.py", line 241, in alter_column
self._exec(
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\alembic\ddl\impl.py", line 207, in _exec
return conn.execute(construct, multiparams)
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1416, in execute
return meth(
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\sql\ddl.py", line 181, in _execute_on_connection
return connection._execute_ddl(
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1528, in _execute_ddl
ret = self._execute_context(
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1848, in _execute_context
return self._exec_single_context(
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1988, in _exec_single_context
self._handle_dbapi_exception(
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\engine\base.py", line 2344, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1969, in _exec_single_context
self.dialect.do_execute(
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\engine\default.py", line 922, in do_execute
cursor.execute(statement, parameters)
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\sqlalchemy\dialects\sqlite\aiosqlite.py", line 146, in execute
return await self._conn._execute(fn, *args, **kwargs)
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\aiosqlite\core.py", line 133, in _execute
return await future
File "C:\MyFolder\python\discord\logika bot\venv\lib\site-packages\aiosqlite\core.py", line 106, in run
result = function()
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "ALTER": syntax error
[SQL: ALTER TABLE marks ALTER COLUMN presence DROP NOT NULL]
My env.py file:
import asyncio
from logging.config import fileConfig
from sqlalchemy import pool
from sqlalchemy.engine import Connection
from sqlalchemy.ext.asyncio import async_engine_from_config
from alembic import context
from orm.models import Base
# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
# Interpret the config file for Python logging.
# This line sets up loggers basically.
if config.config_file_name is not None:
fileConfig(config.config_file_name)
# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = Base.metadata
# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.
def run_migrations_offline() -> None:
"""Run migrations in 'offline' mode.
This configures the context with just a URL
and not an Engine, though an Engine is acceptable
here as well. By skipping the Engine creation
we don't even need a DBAPI to be available.
Calls to context.execute() here emit the given string to the
script output.
"""
url = config.get_main_option("sqlalchemy.url")
context.configure(
url=url,
target_metadata=target_metadata,
literal_binds=True,
dialect_opts={"paramstyle": "named"},
)
with context.begin_transaction():
context.run_migrations()
def do_run_migrations(connection: Connection) -> None:
context.configure(connection=connection, target_metadata=target_metadata)
with context.begin_transaction():
context.run_migrations()
async def run_async_migrations() -> None:
"""In this scenario we need to create an Engine
and associate a connection with the context.
"""
connectable = async_engine_from_config(
config.get_section(config.config_ini_section, {}),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
async with connectable.connect() as connection:
await connection.run_sync(do_run_migrations)
await connectable.dispose()
def run_migrations_online() -> None:
"""Run migrations in 'online' mode."""
asyncio.run(run_async_migrations())
if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()
My alembic.ini file:
# A generic, single database configuration.
[alembic]
# path to migrations scripts
script_location = migrations
# template used to generate migrations file names; The default value is %%(rev)s_%%(slug)s
# Uncomment the line below if you want the files to be prepended with date and time
# file_template = %%(year)d_%%(month).2d_%%(day).2d_%%(hour).2d%%(minute).2d-%%(rev)s_%%(slug)s
# sys.path path, will be prepended to sys.path if present.
# defaults to the current working directory.
prepend_sys_path = .
# timezone to use when rendering the date within the migrations file
# as well as the filename.
# If specified, requires the python>=3.9 or backports.zoneinfo library.
# Any required deps can installed by adding `alembic[tz]` to the pip requirements
# string value is passed to ZoneInfo()
# leave blank for localtime
# timezone =
# max length of characters to apply to the
# "slug" field
# truncate_slug_length = 40
# set to 'true' to run the environment during
# the 'revision' command, regardless of autogenerate
# revision_environment = false
# set to 'true' to allow .pyc and .pyo files without
# a source .py file to be detected as revisions in the
# versions/ directory
# sourceless = false
# version location specification; This defaults
# to migrations/versions. When using multiple version
# directories, initial revisions must be specified with --version-path.
# The path separator used here should be the separator specified by "version_path_separator" below.
# version_locations = %(here)s/bar:%(here)s/bat:migrations/versions
# version path separator; As mentioned above, this is the character used to split
# version_locations. The default within new alembic.ini files is "os", which uses os.pathsep.
# If this key is omitted entirely, it falls back to the legacy behavior of splitting on spaces and/or commas.
# Valid values for version_path_separator are:
#
# version_path_separator = :
# version_path_separator = ;
# version_path_separator = space
version_path_separator = os # Use os.pathsep. Default configuration used for new projects.
# set to 'true' to search source files recursively
# in each "version_locations" directory
# new in Alembic version 1.10
# recursive_version_locations = false
# the output encoding used when revision files
# are written from script.py.mako
# output_encoding = utf-8
sqlalchemy.url = sqlite+aiosqlite:///C:/MyFolder/python/discord/logika bot/orm/async_database.sqlite3
[post_write_hooks]
# post_write_hooks defines scripts or Python functions that are run
# on newly generated revision scripts. See the documentation for further
# detail and examples
# format using "black" - use the console_scripts runner, against the "black" entrypoint
# hooks = black
# black.type = console_scripts
# black.entrypoint = black
# black.options = -l 79 REVISION_SCRIPT_FILENAME
# lint with attempts to fix using "ruff" - use the exec runner, execute a binary
# hooks = ruff
# ruff.type = exec
# ruff.executable = %(here)s/.venv/bin/ruff
# ruff.options = --fix REVISION_SCRIPT_FILENAME
# Logging configuration
[loggers]
keys = root,sqlalchemy,alembic
[handlers]
keys = console
[formatters]
keys = generic
[logger_root]
level = WARN
handlers = console
qualname =
[logger_sqlalchemy]
level = WARN
handlers =
qualname = sqlalchemy.engine
[logger_alembic]
level = INFO
handlers =
qualname = alembic
[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic
[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S
Model that has been changed:
class Mark(Base):
__tablename__ = "marks"
id: Mapped[int] = mapped_column(primary_key=True)
student_id: Mapped[int] = mapped_column(ForeignKey("students.id"))
lesson_title: Mapped[str] = mapped_column(ForeignKey("lessons.title"))
logiks: Mapped[int]
date: Mapped[str] = mapped_column(default=datetime.now().strftime("%Y-%m-%d"))
presence: Mapped[Optional[bool]] # <- THIS COLUMN WAS CHANGED
student: Mapped["Student"] = relationship(back_populates="marks", lazy="joined")
lesson: Mapped["Lesson"] = relationship(back_populates="marks", lazy="joined")
def __repr__(self) -> str:
return f"Lesson(title={self.lesson_title!r}, logiks={self.logiks!r}, presence={self.presence!r})"
Migration file:
"""make presence column in Mark Optional
Revision ID: 9bce8e04e73c
Revises: 5fefb216ecaf
Create Date: 2024-02-02 15:43:40.855715
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision: str = '9bce8e04e73c'
down_revision: Union[str, None] = '5fefb216ecaf'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column('marks', 'presence',
existing_type=sa.BOOLEAN(),
nullable=True)
# ### end Alembic commands ###
def downgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column('marks', 'presence',
existing_type=sa.BOOLEAN(),
nullable=False)
# ### end Alembic commands ###
I tried to google the error, but did not find how to fix it with alembic. It would be cool if you could suggest a simple solution to this problem so that I can do migrations without any problems
Thx