querying data from sqlalchemy database
Question: 
    engine = create_engine('sqlite:///nwtopology.db', echo=False)
    Base = declarative_base()
    
    class SourcetoPort(Base):
        """"""
        __tablename__ = 'source_to_port'
        id = Column(Integer, primary_key=True)
        port_no        = Column(Integer)
        src_address    = Column(String)
    
        #----------------------------------------------------------------------
        def __init__(self, src_address,port_no):
            """"""
            self.src_address = src_address
            self.port_no     = port_no
    
    
    
    
    Session = sessionmaker(bind=engine)
    session = Session()
    self.mac_to_port[packet.src]=packet_in.in_port
    if(self.matrix.get((packet.src,packet.dst))==None):
           self.matrix[(packet.src,packet.dst)]=0
           print "found a new flow"
           #create an databse entry with address and port
           entry = SourcetoPort(src_address=str(packet.src) , port_no=packet_in.in_port)
           #add the record to the session object
           session.add(entry)
           #add the record to the session object
           session.commit()
    self.matrix[(packet.src,packet.dst)]+=1
    print "incrementing flow count"
    #if self.mac_to_port.get(packet.dst)!=None:
    if session.query(SourcetoPort).filter_by(src_address=str(packet.dst)).count():
    #do stuff if the flow information is already in the databaase.
    

I am very new to python and sql alchemy and stuff.the above code is releavnt
portions of a network controller.The above piece of code gets called whenever
a new packet comes in.My question is

    
    
    if session.query(SourcetoPort).filter_by(src_address=str(packet.dst)).count():
    

is this the correct/most efficient way to know if the src_address is already
in the databse.?Can someone suggest some better method.relying on positive
count doesn't seem too rigid.


Answer: Couple suggestions

1) make sure that your table has index created on src_address field. If you
are using SQLAlchemy to create schema index can be added with this simple
change to table definition. (see more at [Describing Databases with MetaData:
Indexes part of SQLAlchemy
manual](http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html?highlight=index#indexes))

    
    
    class SourcetoPort(Base):
        """"""
        __tablename__ = 'source_to_port'
        id = Column(Integer, primary_key=True)
        port_no        = Column(Integer)
        src_address    = Column(String, index=True)
    

2) To check if there any records with src_address=str(packet.dst) there is
another way by using EXISTS. So it won't have to scan all records what has
such src_address but return results as soon as it finds first records with
such field value.

    
    
    if session.query(SourcetoPort).filter_by(src_address=str(packet.dst)).count():
    #do stuff if the flow information is already in the databaase.
    

Replace count query with exists query

    
    
    from sqlalchemy.sql.expression import exists
    if session.query(exists().where(SourcetoPort.src_address == '123')).scalar() is not None:
        #do stuff if the flow information is already in the database.
    

3) I'm not sure what task your are solving by programming this script. I just
hope you are not going to start new python script every time new network
packet arrives to network interface. Starting new python interpreter process
takes seconds and there can be thousands of packets per second arriving. In
the latter case I would consider running such network controller program as a
daemon and would cache all information in memory. Also I would implement
database operations to run asynchronously in separate thread or even thread
pool, so they won't block main thread controlling network flow.

