Defining Indexes in SqlAlchemy with Alembic

Question

I completely agree that this is a pretty basic question and may be it has its answer somewhere, but I somehow can not find it out. (also I am not very proficient with SqlAlchemy)

I have this code --

from sqlalchemy import Column, Integer, Text, String, TIMESTAMP, Boolean, \
    UnicodeText
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.schema import Sequence, ForeignKey, Index
from zope.sqlalchemy import ZopeTransactionExtension
import datetime

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()

class Users(Base):
    __tablename__ = "user_table"

    id = Column(Integer, Sequence('idseq'), primary_key = True)
    email = Column(String, unique=True)
    ip_addr = Column(String)
    created_date = Column(TIMESTAMP(timezone=True), default=datetime.datetime.utcnow)
    modified_date = Column(TIMESTAMP(timezone=True), default=datetime.datetime.utcnow)
    #Index('user_index',Users.c.email)

How can I

1. Define a Single Column Index (say on email) on this? (Probaby index=True will do this. And if so, then it is mostly the next point where I am lost)
2. How can I define a Multi Column Index on this (say, for an example, on email and ip_addr. I am using alembic for Migration and when I define something like - Index('user_index',Users.c.email) [commented line] After the column definitions in the class it does give me an error that "NameError: name 'Users' is not defined" )

In the alembic's env.py, apart from all the normal and default lines, I have these two lines

from tutorial.models import Base
.
.
.
target_metadata = Base.metadata

My app name is tutorial. And if somehow it is needed I am using Pyramid as the framework. and postgres as the db.

I am telling again that, it may be a very basic question, but just I can not figure it out right now and so any help will be great.

Thanks.

Answer 1

For I, Yes, use index=True on your field definition.

For II, just put the Index declaration outside the class definition:

class Users(Base):
    __tablename__ = "user_table"

    id = Column(Integer, Sequence('idseq'), primary_key=True)
    email = Column(String, unique=True)
    ip_addr = Column(String)
    created_date = Column(TIMESTAMP(timezone=True), default=datetime.datetime.utcnow)
    modified_date = Column(TIMESTAMP(timezone=True), default=datetime.datetime.utcnow)

Index('user_index', Users.c.email, Users.c.ip_addr)