[alembic] how do I add a many-to-many relation with add_column()

[c.b...@posteo.jp]

I want to add a new column which is a many-to-many relation to a new
created table. I am not sure what is wrong with the code below.

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sqlalchemy as sa
import sqlalchemy.orm as sao
import sqlalchemy.ext.declarative as sad
import sqlalchemy_utils as su

import alembic
import alembic.operations
import alembic.migration

_Base = sad.declarative_base()

# a simple model
class Model(_Base):
__tablename__ = 'Model'
_oid = sa.Column('oid', sa.Integer, primary_key=True)
_simple = sa.Column('simple', sa.Integer)

# engine
engine = sa.create_engine('sqlite:///mig2.db', echo = True)

# create database if needed
if not su.database_exists(engine.url):
su.create_database(engine.url) # create the database
_Base.metadata.create_all(engine) # create the table in the
db

# another model
class Sub(_Base):
__tablename__ = 'Sub'
_oid = sa.Column('oid', sa.Integer, primary_key=True)
_somedata = sa.Column('simple', sa.Integer)

# many-to-many-relation
model_sub_relation = sa.Table('model_sub_relation', _Base.metadata,
sa.Column('model_oid', sa.Integer, sa.ForeignKey('Model.oid')),
sa.Column('sub_oid', sa.Integer, sa.ForeignKey('Sub.oid'))
)

conn = engine.connect()
ctx = alembic.migration.MigrationContext.configure(conn)
op = alembic.operations.Operations(ctx)

op.create_table('Sub',
sa.Column('oid', sa.Integer, primary_key=True),
sa.Column('simple', sa.Integer)
)

op.add_column('Model', sao.relationship('subs',
secondary=model_sub_relation, backref='Model'))



Here you see the error message.

Traceback (most recent call last):
File "./mig2.py", line 54, in <module>
op.add_column('Model', sao.relationship('subs',
secondary=model_sub_relation, backref='Model')) File
"/usr/local/lib/python3.4/dist-packages/alembic/operations.py", line
592, in add_column t = self._table(table_name, column, schema=schema)
File "/usr/local/lib/python3.4/dist-packages/alembic/operations.py",
line 149, in _table t = sa_schema.Table(name, m, *columns, **kw) File
"/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/schema.py", line
416, in __new__ metadata._remove_table(name, schema) File
"/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/langhelpers.py",
line 60, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File
"/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py",
line 182, in reraise raise value File
"/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/schema.py", line
411, in __new__ table._init(name, metadata, *args, **kw) File
"/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/schema.py", line
488, in _init self._init_items(*args) File
"/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/schema.py", line
72, in _init_items item._set_parent_with_dispatch(self) File
"/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/langhelpers.py",
line 833, in __getattr__ return self._fallback_getattr(key) File
"/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/langhelpers.py",
line 811, in _fallback_getattr raise AttributeError(key)
AttributeError: _set_parent_with_dispatch
--
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-----END PGP PUBLIC KEY BLOCK-----

[Mike Bayer]

a relationship() is not a Column. Alembic "add_column()" is intended
to render an "ALTER TABLE <foo> ADD COLUMN <bar>" statement. You need
to pass it a Column object.

[c.b...@posteo.jp]

On 2015-08-11 21:43 Mike Bayer <mik...@zzzcomputing.com> wrote:
> a relationship() is not a Column. Alembic "add_column()" is
> intended to render an "ALTER TABLE <foo> ADD COLUMN <bar>"
> statement. You need to pass it a Column object.

Ok, tnen how can I realize that. How can I add a relationship to an
existing table?
Is alembic able for that or maybe there is another way to do that?

[Simon King]

On Wed, Aug 12, 2015 at 3:13 AM, <c.b...@posteo.jp> wrote:
> On 2015-08-11 21:43 Mike Bayer <mik...@zzzcomputing.com> wrote:
>> a relationship() is not a Column. Alembic "add_column()" is
>> intended to render an "ALTER TABLE <foo> ADD COLUMN <bar>"
>> statement. You need to pass it a Column object.
>
> Ok, tnen how can I realize that. How can I add a relationship to an
> existing table?
> Is alembic able for that or maybe there is another way to do that?

A "relationship" (in SQLAlchemy terms) is not something that exists in
the database. It is purely a python-level construct for connecting
objects. In the database, relationships are normally represented by
foreign keys.

I don't really understand what you are trying to achieve in your
script above. It seems like you are trying to use Alembic to configure
the way you map classes to database tables, as well as how the tables
themselves are constructed, which doesn't really make sense. Normally
you would use alembic in a migration script which is completely
separate from your application, and simply creates and alters tables.
In your main application, you would change the definition of your
Model classes to match the new table structures.

So for example, if your original application had the Model and Sub
classes, but nothing linking them, then your migration script would
create the "model_sub_relation" table. In your main application you
would add the definition of model_sub_relation table, and add
something like this to the Model class:

subs = sao.relationship("Sub", secondary=model_sub_relation,
backref="model")

Hope that helps,

Simon

[Mike Bayer]

On 8/11/15 10:13 PM, c.b...@posteo.jp wrote:
> On 2015-08-11 21:43 Mike Bayer <mik...@zzzcomputing.com> wrote:
>> a relationship() is not a Column. Alembic "add_column()" is
>> intended to render an "ALTER TABLE <foo> ADD COLUMN <bar>"
>> statement. You need to pass it a Column object.
> Ok, tnen how can I realize that. How can I add a relationship to an
> existing table?

database tables don't have relationships. They have columns.
Relationships are only present in Python code, and they act upon a
database schema that happens to have the appropriate columns.

What's missing here is that you need to understand the interaction
between a Python ORM mapping and what the corresponding database schema
looks like. SQLAlchemy doesn't hide or merge together these concepts
like other ORMs do; the fact that object A refers to object B is known
as a "relationship", but the fact that table "a" refers to table "b" is
dealt with using columns and foreign key constraints. The relationship
needs that to be happening underneath but you as the programmer have to
configure for both concepts.

See the examples in
http://docs.sqlalchemy.org/en/rel_1_0/orm/basic_relationships.html where
you can see that all models configure both relationship() *AND* the
Column objects that are represented in the schema.

> Is alembic able for that or maybe there is another way to do that?

Alembic can handle commands for adding the database column and table
objects you need.