SQLAlchemy index

Introduction to SQLAlchemy index

SQLAlchemy Index is used for assigning the identifiers for each of the particular row getting stored inside a table. We can have indexing based on the single column or collection of two or more columns together acting as an index to the table rows. In this article, we will have a look at what is sqlalchemy index, constraints sqlalchemy index, sqlalchemy index examples, and a conclusion about the same.

What is SQLAlchemy index?

SQLalchemy index is used for getting the individual rows and makes the retrieval of particular rows easy for us. We can go for creating the index anonymously for a particular single individual column of the table by making the use of the keyword auto-generated such as ix_<label of column> or by making the use of the inline keyword index in the name of the column while describing table of a column of the table.

Making the use of the index keywords also automatically implies that the UNIQUE constraint is applied internally on that particular column so that all the rows of the table contain a different unique value in that column which helps in indexing and accessing individual-specific rows. Here, we don’t need to separately go for external mention of UNIQUE constraint.

We can even go for having an index that is made up of more than one column which can be created by using the Index construct which will require the name of the index to be specified. The same process of having the index with specific names can be followed using the index construct.

constraints sqlalchemy index

You need to read out the below pointers for clearing your concept on constraints on table columns and the indexing –

• There are various constraints that you can apply to the columns of the table to define their characteristics or features. Some of them include primary key, foreign key, and unique constraint.
• The index is applied to the table so that the data can be retrieved in a faster way if the filters are applied on the columns which are part of the index or are the index itself.
• Applying the index makes the retrieval process faster and increases the performance of the application while having the data manipulations and retrieval, manipulation, and storage of data in the database.
• We can apply more than one index to the table.
• Each index can be made up of a single column or an association of more than one column.
• We can assign an index name while creating the index and specify the names of columns that will be forming them.
• The index doesn’t necessarily need to have a primary key column in it.
• Whenever you want to retrieve the data from the table in your application code, it’s necessary to have a look at what are indexes defined on that table in the database and accordingly include the where clause filtering and scrutinizing only that data that have specified values in the columns madding up the index.
• When huge data is present in the table, the use of indexes substantially affects the retrieval time and performance of the application and eventually user experience, especially in the case of report generation.

SQLAlchemy index Examples

There are three methods using which we can create the index for a particular table and define it by using DDL statements. This includes –

• Creating an external construct of the index on the table with the help of column objects to define it.
• Inline definition of the index on the table which will involve the use of string names in order to recognize the columns.
• Using the create() method of the index.

We will have a look at some examples using all the above three methodologies –

Method 1 – Creating an external construct of index on the table with the help of column objects to define it.

objectFrMetadata = MetaData()
educbaSampleTable = Table('educbaSampleTable', objectFrMetadata,
# "ix_educbaSampleTable_article_number" index for the indexed column article_number
Column('article_number', Integer, index=True),
# "ix_educbaSampleTable_article_writer_id" index for uniquely indexed article_writer_id column
Column('article_writer_id', Integer, index=True, unique=True),
Column('article_pages', Integer),
Column('article_starting_page_no', Integer),
Column('article_price', Integer),
Column('article_viewers', Integer),
)

#  article_pages and article_starting_page_no are considered for placing the index

Index('idx_article_pages4', educbaSampleTable.c.article_pages, educbaSampleTable.c.article_starting_page_no)

#  article_price are article_viewers considered for unique indexing

Index('myindex', educbaSampleTable.c.article_price, educbaSampleTable.c.article_viewers, unique=True)
educbaSampleTable.create(engine)

The above code converts into the following statements in SQL which are to be executed and run on the database –

CREATE TABLE educbaSampleTable (
article_number INTEGER,
article_writer_id INTEGER,
article_pages INTEGER,
article_starting_page_no INTEGER,
article_price INTEGER,
article_viewers INTEGER
)

The above statement gives the following output –

CREATE INDEX ix_educbaSampleTable_article_number ON educbaSampleTable (article_number)

Which gives the following output –

CREATE UNIQUE INDEX ix_educbaSampleTable_article_writer_id ON educbaSampleTable (article_writer_id)

Giving the following output in SQLite Studio –

CREATE UNIQUE INDEX myindex ON educbaSampleTable (article_price, article_viewers)

Giving below resultant –

CREATE INDEX idx_article_pages4 ON educbaSampleTable (article_pages, article_starting_page_no)

This results in  –

In the above example, we made use of the DDL statement named CREATE INDEX which helps us to define the constraint on where should we put the index on which column. This statement is followed right after the statement of creating a table so that as soon as the table creates, it will set the index on the table. The output of the below code is as shown below –

Method 2 – Inline the definition of the index on the table which will involve the use of string names in order to recognize the columns.

objectFrMetadata = MetaData()
educbaSampleTable = Table('educbaSampleTable', objectFrMetadata,
Column('article_number', Integer),
Column('article_writer_id', Integer),
Column('article_pages', Integer),
Column('article_starting_page_no', Integer),

# article_number and article_writer_id are indexed

Index('idx_article_number2', 'article_number', 'article_writer_id'),

# article_pages and article_starting_page_no are added with unique index on them

Index('idx_article_pages4', 'article_pages', 'article_starting_page_no', unique=True)
)

The output of above code is as shown below –

Method 3 – Using the create() method of the index.

sampleIndexObj = Index('someindex', educbaSampleTable.c.article_price)
sampleIndexObj.create(engine)

Which looks as shown below after transforming to the SQL statement –

CREATE INDEX someindex ON educbaSampleTable (article_price)

After executing the above code, we get the following output as a result in the educba sample table in SQLite studio after going for watching its indexes –

Conclusion

SQLAlchemy index is used for applying the index on one or more than one column of the table which makes the searching, retrieving, and scrutinizing of the data easy, faster, and efficient in matters of performance.

Recommended Articles

This is a guide to SQLAlchemy index. Here we discuss the Introduction, What is SQLAlchemy index, examples with code implementation. You may also have a look at the following articles to learn more –

1. PL/SQL varray
2. SQL WAITFOR
3. PL/SQL LIKE
4. PL/SQL TO_CHAR