On Sun, Aug 4, 2019, at 1:14 PM, Abdeali Kothari wrote:
> I am using the sqlalchemy Table class to load up a data schema I am provided.
>
> Once loaded into sqlalchemy, I want to use some of the existing extensions
> like:
> - ERD plots - eralchemy
> - Auto finding relationships using automap
> etc.
>
> I am not using any actual DB connection under the table though - i.e. not
> really going to be generating SQL statements (as of now)
>
> My schema comes from different operations that are fed into the system - like
> create table, add column, rename column, delete column.
> While I was trying to mimic these operations in sqlalchemy, I realized that I
> need to:
> 1. Create a MetaData
> 2. Create the table with a name and add columns to the arguments
> 3. To add a new column - I can use the extend_existing kwarg in
> sqlalchemy.Table
>
> But I could not find a way to "*drop*" or "remove" a column I have added.
>
> *To add some code examples*:
> >>> import sqlalchemy as sqla
> >>> mytable = sqla.Table("mytable", sqla.MetaData(), sqla.Column('col1',
> >>> sqla.String()))
> >>> mytable
> Table('mytable',
> MetaData(bind=None),
> Column('col1', String(), table=<mytable>), schema=None)
>
> >>> # Mutating the table to add a column:
> >>> sqla.Table("mytable", mytable.metadata, sqla.Column('col2',
> >>> sqla.String()), extend_existing=True)
> Table('mytable',
> MetaData(bind=None),
> Column('col1', String(), table=<mytable>),
> Column('col2', String(), table=<mytable>), schema=None)
>
> >>> # Mutating the table to drop a column:
> >>> # What do I do here ?
> Table('mytable',
> MetaData(bind=None),
> Column('col1', String(), table=<mytable>), schema=None)
So "mutating" a Table like that would correspond to emitting ALTER statements
on the database. There is a tool called sqlalchemy-migrate which works this
way; you have a Table object, you say, "table.remove_column(col)", and it emits
an "ALTER TABLE" which drops that column. To do this, they altered SQLAlchemy
itself by monkeypatching the Table class. This design ultimately proved too
rigid for its purpose, as it required the construction of Table models in order
to emit simple commands and therefore led to a lot of overly complex and
inefficient migration scripts.
To alleviate this, I wrote a new migrations tool called Alembic that instead
allows you to emit commands like "ALTER TABLE" without actually mutating Table
objects. Today, Alembic has superseded sqlalchemy-migrate and as luck would
have it, I was put in charge of maintaining sqlalchemy-migrate anyway even
though it is pretty much a legacy project.
The point is, I don't think you should be trying to change the
structure of a Table object once you create it. The extend_existing()
method is only there to suit the purpose of being able to reflect
database metadata into an existing set of Table objects that include
some existing state already. But otherwise, the use case for Table /
MetaData / Column etc. is that they represent a fixed database schema
structure that is expressed at once. They allow for additive creation,
that is, adding new tables, adding new columns, only as a means to help
construct a particular schema from different parts, such as certain ORM
mixins that add new columns to a table, or being able to reflect
additional columns / tables to complement a set of columns / tables
that are explicitly declared in the application. But in all cases this
additive construction is intended to represent a schema that is
unchanging. Schema changes are considered to be out of scope for the
runtime of the application itself.
If your program is provided with "a data schema" to start with, there should be
no need to rename or delete columns. Your actual use case is likely not quite
like this so feel free to share more details of why you are seeking these
operations.
>
>
> *I can think of 2 approaches*:
> # Recreate the entire table
> mytable.metadata.remove(mytable)
> mytable = sqla.Table(
> mytable.name,
> mytable.metadata,
> *[i for i in mytable.columns if i.name != 'col2])
>
> # Modify the private _columns collection
> mytable._columns.remove(mytable.c['col2'])
>
> I was wondering if there is a correct/compliant way to do this.
>
> PS: I cannot avoid doing a drop column - have to do it as that is an
> operation that we want to support.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CA%2BBKPDXffNsB9LaBqz4yiUa_%2BmFAcKbYEnXRbrL6pdohr5DZxg%40mail.gmail.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/CA%2BBKPDXffNsB9LaBqz4yiUa_%2BmFAcKbYEnXRbrL6pdohr5DZxg%40mail.gmail.com?utm_medium=email&utm_source=footer>.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/e8f6621d-b4af-4c53-9415-39b9a65282f0%40www.fastmail.com.
