Re: [sqlalchemy] Automatically add Index on same columns in ForeignKeyConstraint in Postgresql using Custom DDL/Compilation
Hi Mike,
Thanks for your feedback. I'll leave your suggestion here if anyone comes
across this:
from sqlalchemy import event, Table
@event.listens_for(Table, 'before_create')
def add_index_on_foreign_key_columns(table, connection, **kwargs):
for foreign_key in table.foreign_key_constraints:
index_name = foreign_key.name.replace('_fk', '_idx')
columns = (c for c in foreign_key.columns)
Index(index_name, *columns)
Best regards,
Matthew Moisen
On Friday, June 25, 2021 at 9:58:56 AM UTC-7 Mike Bayer wrote:
>
>
> On Fri, Jun 25, 2021, at 11:58 AM, mkmo...@gmail.com wrote:
>
>
> When using PostgreSQL, creating a foreign key on a column in a table does
> not automatically index that column, unlike Oracle or MySQL.
>
> I would like to get SQLAlchemy to automatically create an index on the
> same columns that are specified in a ForeingKeyConstraint.
>
> For example, if I have a table like this:
>
> foo = table(
> 'foos', metadata,
> Column('id', BigInteger),
> Column('parent_id', BigInteger),
> ForeignKeyConstraint(('parent_id',), refcolumns=('bar.id',),
> name='foo_parent_id_fk')
> )
>
> I would like it to automatically add an Index like:
>
> Index('foo_parent_id_idx', 'parent_id')
>
> I've made the following, which appears to work:
>
> from sqlalchemy import event, Table
>
> @event.listens_for(Table, 'before_create')
> def add_index_on_foreign_key_columns(table, connection, **kwargs):
> for foreign_key in table.foreign_key_constraints:
> index_name = foreign_key.name.replace('_fk', '_idx')
> columns = (c.name for c in foreign_key.columns)
> Index(index_name, *columns, _table=table)
>
> Is there a better way to accomplish this?
>
>
> that's the perfect way to accomplish this and is just what I'd recommend -
> the only thing I would change is when creating the Index(), it should
> auto-add itself to the Table as you are passing Table-bound Column objects
> to it, so i would not use the private "_table" parameter. Otherwise, you
> can say "table.append_index(my_index)" to ensure it is added to the Table.
>
>
>
>
>
>
> Thanks and best regards,
>
> Matthew Moisen
>
>
> --
> 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 sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/43dba570-41de-4164-82c8-1cecb3dd9b1cn%40googlegroups.com
>
>
> .
>
>
>
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/6885dfaf-8021-49d8-be2d-872448ac86fdn%40googlegroups.com.
Re: [sqlalchemy] Automatically add Index on same columns in ForeignKeyConstraint in Postgresql using Custom DDL/Compilation
On Fri, Jun 25, 2021, at 11:58 AM, mkmo...@gmail.com wrote:
>
> When using PostgreSQL, creating a foreign key on a column in a table does not
> automatically index that column, unlike Oracle or MySQL.
>
> I would like to get SQLAlchemy to automatically create an index on the same
> columns that are specified in a ForeingKeyConstraint.
>
> For example, if I have a table like this:
>
> foo = table(
> 'foos', metadata,
> Column('id', BigInteger),
> Column('parent_id', BigInteger),
> ForeignKeyConstraint(('parent_id',), refcolumns=('bar.id',),
> name='foo_parent_id_fk')
> )
>
> I would like it to automatically add an Index like:
>
> Index('foo_parent_id_idx', 'parent_id')
>
> I've made the following, which appears to work:
>
> from sqlalchemy import event, Table
>
> @event.listens_for(Table, 'before_create')
> def add_index_on_foreign_key_columns(table, connection, **kwargs):
> for foreign_key in table.foreign_key_constraints:
> index_name = foreign_key.name.replace('_fk', '_idx')
> columns = (c.name for c in foreign_key.columns)
> Index(index_name, *columns, _table=table)
>
> Is there a better way to accomplish this?
that's the perfect way to accomplish this and is just what I'd recommend - the
only thing I would change is when creating the Index(), it should auto-add
itself to the Table as you are passing Table-bound Column objects to it, so i
would not use the private "_table" parameter. Otherwise, you can say
"table.append_index(my_index)" to ensure it is added to the Table.
>
> Thanks and best regards,
>
> Matthew Moisen
>
>
>
> --
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/43dba570-41de-4164-82c8-1cecb3dd9b1cn%40googlegroups.com
>
> .
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/f423d1c0-682d-439b-8d04-74daaf498778%40www.fastmail.com.
[sqlalchemy] Automatically add Index on same columns in ForeignKeyConstraint in Postgresql using Custom DDL/Compilation
When using PostgreSQL, creating a foreign key on a column in a table does
not automatically index that column, unlike Oracle or MySQL.
I would like to get SQLAlchemy to automatically create an index on the same
columns that are specified in a ForeingKeyConstraint.
For example, if I have a table like this:
foo = table(
'foos', metadata,
Column('id', BigInteger),
Column('parent_id', BigInteger),
ForeignKeyConstraint(('parent_id',), refcolumns=('bar.id',),
name='foo_parent_id_fk')
)
I would like it to automatically add an Index like:
Index('foo_parent_id_idx', 'parent_id')
I've made the following, which appears to work:
from sqlalchemy import event, Table
@event.listens_for(Table, 'before_create')
def add_index_on_foreign_key_columns(table, connection, **kwargs):
for foreign_key in table.foreign_key_constraints:
index_name = foreign_key.name.replace('_fk', '_idx')
columns = (c.name for c in foreign_key.columns)
Index(index_name, *columns, _table=table)
Is there a better way to accomplish this?
Thanks and best regards,
Matthew Moisen
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/43dba570-41de-4164-82c8-1cecb3dd9b1cn%40googlegroups.com.
