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 1

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)

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.