CODEWITHSUNDEEP
sqlalchemy
cp2587
cp2587

Reputation: 523

SQLAlchemy - create table statement with indexes

I am trying to produce a CREATE TABLE statement using postgresql and sqalchemy. Columns are correctly created but indexes are missing.

What am i missing ?

Here is the snipper of code i am using...

    table = Table('test', metadata, 
                  Column('id', Integer), 
                  Column('booking', Integer, index=True))
    create_stmt = CreateTable(cls.table).compile(connection)
    print(create_stmt)
    # This print CREATE TABLE test ( id INTEGER, booking INTEGER)
    # Index on column booking is missing

Upvotes: 3

Views: 3079

Answers (1)

lrnzcig
lrnzcig

Reputation: 3947

You need to use CreateIndex over the indexes of the table.

    from sqlalchemy import Table, MetaData
    from sqlalchemy.schema import CreateTable
    
    metadata=MetaData()
    ​
    table = Table('test', metadata, 
                  Column('id', Integer), 
                  Column('booking', Integer, index=True))

    
    create_stmt = CreateTable(table).compile(session.connection())
    print(create_stmt)
    
    CREATE TABLE test (
        id INTEGER, 
        booking INTEGER
    )

    
    from sqlalchemy.schema import CreateIndex
    for index in table.indexes:
        create_stmt2 = CreateIndex(index).compile(session.connection())
        print(create_stmt2)

    CREATE INDEX ix_test_booking ON test (booking)

This solution is not so clean and a bit annoying, honestly, but I wonder if there is any reason why you are not using the full schema creation, something like Base.metadata.create_all(engine).

Hope it helps.

Upvotes: 5

Related Questions