For adding a column to existing table created by sqlchemy, I used the Alembic migration tool.
Environment
- MacOS Catalina 10.15.7
- Python 3.9.7
- SQLte3 3.28.0
- sqlalchemy 1.4.23
- alembic 1.7.7
Problem
As per a document of sqlalchemy,
While SQLAlchemy directly supports emitting CREATE and DROP statements for schema constructs, the ability to alter those constructs, usually via the ALTER statement as well as other database-specific constructs, is outside of the scope of SQLAlchemy itself
Solution
There are tools for substitution of function to alter table: the Alembic and the SQLAlchemy-Migrate. As Alembic supersedes the SQLAlchemy-Migrate project, I used Alembic.
Install Alembic
I used pyenv, pip-tools and pip for installing Alembic.
Ref. alembic.sqlalchemy.org | Installation
1
2
3
|
$ pyenv versions
system
* 3.9.7 (set by /Users/hoge/hoge/.python-version)
|
requiremens.in
1
2
3
4
5
6
7
8
|
--index-url https://pypi.org/simple
requests
requests-toolbelt
sqlalchemy
flask
flask-socketio
pytest
alembic
|
Install by pip
1
2
3
4
|
$ pip-compile requirements.in
$ pip install -r requirements.txt -e .
$ alembic --version
alembic 1.7.7
|
Creating an Environment
alembic init creates an environment using the “generic” template.
1
2
3
4
5
6
7
8
9
|
$ alembic init alembic
Creating directory /path/to/yourproject/alembic...done
Creating directory /path/to/yourproject/alembic/versions...done
Generating /path/to/yourproject/alembic.ini...done
Generating /path/to/yourproject/alembic/env.py...done
Generating /path/to/yourproject/alembic/README...done
Generating /path/to/yourproject/alembic/script.py.mako...done
Please edit configuration/connection/logging settings in
'/Users/hoge/hoge/alembic.ini' before proceeding.
|
Editing the .ini File
For starting up with just a single database and the generic configuration, setting up the SQLAlchemy URL is all that’s needed:
alembic.ini
1
|
sqlalchemy.url = sqlite:////absolute/path/to/poc_client.db
|
Note that above is for sqlite. You should specify the database url for each database
Ref.
Create a Migration Script
As I want to add a column that names is_complete with String type to event_history table, I created a revision file by:
1
2
3
|
$ alembic revision -m "Add a column"
Generating /Users/hoge/hoge/alembic/versions/792dee633f82_add_a_column.py ... done
|
Then edit functions of upgrade() and downgrade() in the file and add a new column.
alembic/versions/792dee633f82_add_a_column.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
"""Add a column
Revision ID: 792dee633f82
Revises:
Create Date: 2022-03-23 10:56:12.681890
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = '792dee633f82'
down_revision = None
branch_labels = None
depends_on = None
def upgrade():
op.add_column('event_history', sa.Column('is_complete', sa.String(5)))
def downgrade():
op.drop_column('event_history', 'is_complete')
|
Upgrade Operation
1
2
3
4
5
|
$ 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 -> 792dee633f82, Add a column
|
This proceeds from the current database revision(in above example: None) to the given target revision(792dee633f82).
Then you would be able to find the added column is_complete in event_history table.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
$ sqlite3 /hoge/hoge/poc_client.db
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
sqlite> .tables
event_history
sqlite> .schema event_history
CREATE TABLE event_history (
id INTEGER NOT NULL,
interacted_time VARCHAR(30),
event_type VARCHAR(30),
account_id VARCHAR(64),
is_correct VARCHAR(5),
created_at DATETIME,
updated_at DATETIME, is_complete VARCHAR(5),
PRIMARY KEY (id)
);
sqlite> .quit
|
References