Quick Start with Embedded eXtremeSQL in Python

The eXtremeSQL Python wrapper supports a SQL engine and a SQL server that operate on eXtremeDB databases using the Python DB API Specification. Developers are encouraged to view this website for general information on the Python Database classes and methods. eXtremeSQL Python applications use Cursor methods execute() and execute_many() to execute SQL statements and queries. The following sections will walk through the steps for building a simple Python eXtremeSQL application. In addition, note that there are Python SDK samples that demonstrate specific eXtremeSQL features.

Database definition

As for other Python applications, the first step in using the eXtremeDB Python API is to define the classes that you’ll want to manage in the eXtremeDB database. The eXtremeDB Python wrapper has the mcocomp runtime compiled in, so the database schema can be defined in the form of text, just like a regular .mco file. For example, the following is a simple database schema defined with a Python string:

 
    schema = '''
    declare database quotes;
     
    class Quote 
    {
        uint8    ikey;
        char<8> symbol;
        time    stamp;
        float low;
        float high;
        float open;
        float close;
        uint4 volume;
 
        unique tree<ikey> iidx;
        unique tree<symbol> by_sym;
    };
    '''
     

Open the database for SQL access

To open the database for SQL access we first initialize the eXtremeDB runtime and load the database dictionary; then open and connect to the database. For example:

 
    is_disk = False
    tmgr = 'mursiw'
    is_shm = False
    is_debug = False
 
    #Load Runtime configuration specified by parameters
    exdb.init_runtime(is_disk, tmgr, is_shm, is_debug)
 
    dict = exdb.load_dictionary(schema, persistent=is_disk, debug=is_debug)
    db = exdb.open_database(dbname='opendb', dictionary=dict, is_disk=is_disk, db_segment_size=128*1024*1024);
     
    try:
        conn = db.connect()
         

Populate and Query the database

To populate the database we execute some SQL insert statements by calling the Cursor method execute() or execute_many() as follows:

         
    N_QUOTES = 10
     
    cursor = conn.cursor()
    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)
 
    for i in xrange(0, N_QUOTES / 2):
        cursor.execute(sql, (i, 'AA%s' % i, datetime.datetime(2017, 8, 16, 9, 30+i), 1.0, 4.0, 2.0, 3.0, i*1000))
 
    cursor.close()
    conn.commit()
         

Then to query the database we again call execute(). To traverse the result set we use the Cursor method fetchone() or fetchall(). For example:

         
    cursor = conn.cursor()
    sql = "SELECT ikey, symbol, stamp, low, high, open, close, volume FROM Quote ORDER BY ikey"
    cursor.execute(sql)
 
    # Traverse results one at a time using fetchone()
    while True:
        row=cursor.fetchone()
        if row is None:
            break
        print "Row read:", row
         
    # Traverse results using fetchall()
    sql = "SELECT * FROM Quote ORDER BY ikey"
    cursor.execute(sql)
     
    count = 0
    rows = cursor.fetchall()
    for row in rows:
        count +=  1