Index Searches in Python

Search operations in Python are performed using the Cursor methods find() or search(). The find() method locates an object in the database. It returns an object instance or None if not found. The search() method locates an object in the cursor or None if not found. Then the Cursor navigation methods first(), last(), next() and prev() can be used to scroll through the cursor.

The following code snippet demonstrates a find and search operation:

     
    conn = db.connect()
    conn.startTransaction(exdb.Transaction.MCO_READ_WRITE)
    # Perform simple index search: locate Record by id
    cursor = conn.cursor()
    # find record to update
    rec = cursor.find("Record", "by_i4", 2)
    ...
    cursor.close() #release cursor
    conn.commit() # commit changes
     
    conn.startTransaction()
    cursor = conn.cursor()
    cursor.search("Security", "by_SIC", exdb.Operation.Equals, SIC)
    ...
    cursor.close()
    conn.rollback()
     

SQL Queries

The Cursor can also be used to execute SQL queries with methods execute() and execute_many() . The result sets from SQL queries can then be processed using the methods fetchone(), fetchmany() or fetchall(). For example:

     
    conn = db.connect()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM Metatable WHERE TableName='%s'" % clsname)
    res = cursor.fetchall()
    ...
     
    sql = "INSERT INTO Quote(ikey, symbol, stamp, low, high, open, close, volume) VALUES (?,?,?,?,?,?,?,?)"
    params = []
    for i in xrange(N_QUOTES / 2, N_QUOTES):
        params.append((i, 'AA%s' % i, datetime.datetime(2017, 8, 16, 9, 30+i), 1.0, 4.0, 2.0, 3.0, i*1000))
    cursor.execute_many(sql, params)
    ...
    cursor.close()
    conn.rollback()
     
    cursor = conn.cursor()
    sql = "SELECT ikey, symbol, stamp, low, high, open, close, volume FROM Quote ORDER BY ikey"
    cursor.execute(sql)
 
    count = 0
    while True:
        row=cursor.fetchone()
        if row is None:
            break
        print "Row read:", row
        count +=  1
    ...
    cursor.close()
    conn.rollback()