9) Database Adapters

A Database adapter object can be obtained from an APyaX session by calling the session object's getDbAdapter() method.

9.1) Database Adapter Methods

class dbadapter
VelociKit database adapter
Provide extra services around a DBAPI2 database connection.
Currently the extra services focus on configuring the connection attributes.
method configure
def configure(self,attributes)

Configure (or reconfigure) database adapter

Adapter configuration settings are specific to each type of database adapter These settings enable the adapter to open a connection to the database. For example, settings might specify the host and port number of the database server.

Parameters
attributesa dictionary of attribute-value pairs
method open
def open(self)

Open (without user credentials) the adapter connection

Opens a connection, without passing in a username and password for authentication purposes. Use this method to open connections to databases which do not require a username and password.

Return Value
True iff adapter was successfully opened
method login
def login(self,username,password)

Open (with user credentials) the adapter connection

Opens a connection to the database using the given username and password strings for authentication.

Parameters
usernamethe user name for login
passwordthe password for login
Return Value
True iff adapter was successfully opened
method getConnection
def getConnection(self)

Get the database connection for this adapter

Returns the connection to the database, as a DBAPI 2.0 connection object. Call this method only after using the open() or login() methods to open the adapter. If open() or login() have failed, or have not been called, this method will return None.

Return Value
DBAPI2 connection object or None if adapter is not open

9.2) Configuring Database Adapters

To use a database adapter with sessions of type <session-type>, ApyaX's configuration file (named in the environment variable $APYAXCFG) must be configured to specify the name of the database adapter class for sessions of that type.

[<session-type>]

dbadapter=<dbadapter-class-name>

db.<option1>=<value1>

db.<option2>=<value2>

As well as specifying the name of the database adapter class using the dbadapter property, other options that configure the database adapter can be configured by assigning values to properties of the name db.<option-name>. The names and meanings of these options are specific to each database adapter class.

In the next sub-section, the database adapter classes provided by VelociKit as standard are introduced, along with the options that can be used to configure them.

9.3) Database Adapters provided with VelociKit

Currently APyaX provides three database adapter classes:

9.3.1) postgres_psycopg2_dbadapter

This class provides an adapter for Postgres databases, and requires that the python DBAPI 2.0 driver for Postgres, psycopg2, is installed into the python system. Postgres is an open source server-based relational database system, and users must authenticate themselves using a username and password, so the login method must be used to open a database connection using this adapter). Postgres and psycopg2 are not bundled with VelociKit, and must be downloaded separately.

This adapter understands the following configuration options:

db.database

Configures the name of the database that the database adapter will manage connections for.

db.host

Configures the name of the host system on which the Postgres server is running.

9.3.2) mysql_mysqldb_dbadapter

This class provides an adapter for MySQL databases, and requires that the python DBAPI 2.0 driver for MySQL, mysqldb, is installed into the python system. MySQL is an open source server-based relational database system, and users must authenticate themselves using a username and password, so the login method must be used to open a database connection using this adapter). MySQL and mysqldb are not bundled with VelociKit, and must be downloaded separately.

This adapter understands the following configuration options:

db.database

Configures the name of the database that the database adapter will manage connections for.

db.host

Configures the name of the host system on which the Postgres server is running.

9.3.3) scridble_dbadapter

This class provides an adapter for scridble, a pure-python, file-based open-source database system. scridble is bundled with VelociKit. Unlike Postgres or MySQL, scridble does not provide authentication facilities, so the open method should be invoked to obtain a database connection.

This adapter understands the following configuration options:

db.database

Configures the path of the directory into which scridble will save database files.

9.4) Using Database Adapters - Example

The following APyaX example shows how the APyaX database adapter can be used to provide python scripts with access to a Postgres database.

Firstly, a session type of PG is set up in the APyaX configuration file:


    [PG]
    dbadapter=velocikit.core.dbadapters.postgres_psycopg2_dbadapter
    db.database=testdb
    db.host=localhost
    

Configuring the Postgres Database Adapter for Session Type PG.


Now, Python scripts invoked by APyaX can open the PG session, obtain its database adapter, and open a connection to the database:




def get_database_options(apyax,dbname):

    if dbname == 'select':
	return ''

    option_html='<form id="sqloptions" name="sqloptions">'

    session = apyax.getSession(dbname)

    option_html+='<table>'
    option_html+='<thead>'
    option_html+='<tr><th colspan="0">Connection Details and Query</th></tr>'
    option_html+='</thead>'
    option_html+='<tbody>'

    if dbname == 'mysql':
	dbname=session.getValue('db.database')
	if dbname == None:
	    dbname = ''
	
	option_html+= '<tr><td>Username</td>'
	option_html+= '<td><input name="username" type="text" maxlength="50" /></td></tr>'
	option_html+= '<tr><td>Password</td>'
	option_html+= '<td><input name="password" type="password" maxlength="50" /></td></tr>'
	option_html+= '<tr><td>Database</td>'
	option_html+= '<td><input name="database" type="text" maxlength="50" value="'+dbname+'"/></td></tr>'
	

    if dbname == 'postgres':

	dbname=session.getValue('db.database')
	if dbname == None:
	    dbname = ''
	hostname=session.getValue('db.host')
	if hostname == None:
	    hostname = ''
	port=session.getValue('db.port')
	if port == None:
	    port = ''

	
	option_html+= '<tr><td>Username</td>'
	option_html+= '<td><input name="username" type="text" maxlength="50" /></td></tr>'
	option_html+= '<tr><td>Password</td>'
	option_html+= '<td><input name="password" type="password" maxlength="50" /></td></tr>'
	option_html+= '<tr><td>Hostname</td>'
	option_html+= '<td><input name="hostname" type="text" maxlength="50" value="'+hostname+'"/></td></tr>'
	option_html+= '<tr><td>Port</td>'
	option_html+= '<td><input name="port" type="text" maxlength="50" value="'+port+'"/></td></tr>'
	option_html+= '<tr><td>Database</td>'
	option_html+= '<td><input name="database" type="text" maxlength="50" value="'+dbname+'"/></td></tr>'
	
    if dbname == 'scridble':
	dbname=session.getValue('db.database')
	if dbname == None:
	    dbname = ''

	option_html+= '<tr><td>Database Directory</td>'
	option_html+= '<td><input name="database" type="text" maxlength="50" value="'+dbname+'"/></td></tr>'
	
	   
    option_html+='<tr><td>SQL</td>'
    option_html+='<td><textarea name="sql" rows="10" cols="50"></textarea></td></tr>'

    option_html+='</tbody>'
    option_html+='</table>'
    option_html+='</form>'
    
    option_html+='<p/>'
    option_html+='<button class="btn" onclick="query_database_js(document.sqlaccess.database.options[document.sqlaccess.database.selectedIndex].value, document.sqloptions.sql.value)">Submit</button>'
    return option_html


def query_database(apyax,options,sessionType,sql):

    session = apyax.getSession(sessionType)

    db_adapter = session.getDbAdapter()

    if sessionType == 'scridble':
	db_adapter.open()
    else:
	db_adapter.login(options['username'],options['password'])

    db_conn = db_adapter.getConnection()
    
    if db_conn == None:
	apyax.raiseError("Cannot open connection")

    return exec_sql(db_conn,sql)


def exec_sql(db_conn,sql):

    cursor = db_conn.cursor()

    try:
	cursor.execute(sql)
    except Exception, e:
	# error
	return "<pre>\n"+str(e)+"\n</pre>\n"
    
    metadata = cursor.description
   
    db_conn.commit()

    if (metadata == None or len(metadata) == 0):
	return str(cursor.rowcount)+" rows affected"	
    else:
	# return query results
	results = cursor.fetchall()

	result_table = '<table class="apyaxdatatable">\n'
	result_table+='<thead>\n'
	result_table+='<tr><th colspan="0">Query Results</th></tr>\n'

	result_table+='<tr>\n'
	for md in metadata:
	    colname = md[0]
	    result_table+= '<th>'+colname+'</th>\n'
	result_table+='</tr>\n'

	result_table+='</thead>'
	rowclass = 'odd'
	for row in results:
	    result_table += '<tr class="'+rowclass+'">\n'
	    for val in row:
		result_table += '<td>'
		result_table += str(val)
		result_table += '</td>\n'
	    result_table+='</tr>\n'
	    if rowclass == 'odd':
		rowclass = 'even'
	    else:
		rowclass = 'odd'
	result_table += "</table>\n"
	return result_table


APyaX script using the Database Adapter