What's the difference between the two syntax of declaring index?

[Bob Fang]

Hi I have seen two/three ways to declare index on table:

1. use index=True

class Model(Base):

    __tablename__ = "model"

    id = sa.Column(sa.Integer, primary_key=True)

    field_1 = sa.Column(sa.Integer, index=True)

2. use __table_args__:

class Model(Base):

    __tablename__ = "model"

    id = sa.Column(sa.Integer)

    field_1 = sa.Column(sa.Integer)

    __table_args__ = (sa.Index("field_1_index", "field_1"))

3. use Index statement:

class Model(Base):

    __tablename__ = "model"

    id = sa.Column(sa.Integer)

    field_1 = sa.Column(sa.Integer)

Index("field_1_index", "field1") 

My question is what's the difference between these 3? Also if I have mixed two of them, what will happen?  By this I mean consider the following example;

class Model(Base):

    __tablename__ = "model"

    id = sa.Column(sa.Integer, primary_key=True)

    field_1 = sa.Column(sa.Integer)

    __table_args__ = (sa.Index("field_1_index", "field_1"))

Here we are mixing method 1 and 2. From my experiment it seems Postgres can accept this without any problem but Sqlite rejects it outright. Why this happens?

Thanks!

Bob

[Mike Bayer]

On Fri, Jul 17, 2020, at 7:20 AM, Bob Fang wrote:

Hi I have seen two/three ways to declare index on table:

1. use index=True

class Model(Base):

    __tablename__ = "model"

    id = sa.Column(sa.Integer, primary_key=True)

    field_1 = sa.Column(sa.Integer, index=True)

2. use __table_args__:

class Model(Base):

    __tablename__ = "model"

    id = sa.Column(sa.Integer)

    field_1 = sa.Column(sa.Integer)

    __table_args__ = (sa.Index("field_1_index", "field_1"))

3. use Index statement:

class Model(Base):

    __tablename__ = "model"

    id = sa.Column(sa.Integer)

    field_1 = sa.Column(sa.Integer)

Index("field_1_index", "field1") 

My question is what's the difference between these 3? Also if I have mixed two of them, what will happen?  By this I mean consider the following example;

There's no difference between using __table_args__ and the separate Index field, except when you use the separate Index field, you need to put a real Column that's associated with your Table in there, and not a string name, otherwise it will not be able to associate itself with the Table.

the "index=True" is shorthand for Index(None, column), where "None" is the name.  the name is generated from the default naming convention for the metadata which defaults for the index to "ix_tablename_colname".

class Model(Base):

    __tablename__ = "model"

    id = sa.Column(sa.Integer, primary_key=True)

    field_1 = sa.Column(sa.Integer)

    __table_args__ = (sa.Index("field_1_index", "field_1"))

Here we are mixing method 1 and 2. From my experiment it seems Postgres can accept this without any problem but Sqlite rejects it outright. Why this happens?

Thanks!

Bob

--

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/6c7b8117-ff49-4b80-96b9-d4a202d7e475n%40googlegroups.com.