Programming with SQL Relay using the Python DB API

Establishing a Session

To use SQL Relay, you have to identify the connection that you intend to use.

from SQLRelay import PySQLRDB

con=PySQLRDB.connect('host',9000,'','user','password',0,1)
cur=con.cursor()

... execute some queries ...

After calling the connect(), a session is established when the first execute() is run.

For the duration of the session, the client stays connected to a database connection daemon. While one client is connected, no other client can connect. Care should be taken to minimize the length of a session.

If you're using a transactional database, ending a session has a catch. Database connection daemons can be configured to send either a commit or rollback at the end of a session if DML queries were executed during the session with no commit or rollback. Program accordingly.

Executing Queries

Call execute() to run a query.

from SQLRelay import PySQLRDB

con=PySQLRDB.connect('host',9000,'','user','password',0,1)
cur=con.cursor()

cur.execute('select * from my_table')

... process the result set ...
Commits and Rollbacks

If you need to execute a commit or rollback, you should use the commit() and rollback() methods rather than sending a "commit" or "rollback" query. There are two reasons for this. First, it's much more efficient to call the methods. Second, if you're writing code that can run on transactional or non-transactional databases, some non-transactional databases will throw errors if they receive a "commit" or "rollback" query, but by calling the commit() and rollback() methods you instruct the database connection daemon to call the commit and rollback API methods for that database rather than issuing them as queries. If the API's have no commit or rollback methods, the calls do nothing and the database throws no error. This is especially important when using SQL Relay with ODBC.

Catching Errors

If your call to execute() raises an exception, the query failed. You can find out why by catching the exception.

from SQLRelay import PySQLRDB

con=PySQLRDB.connect('host',9000,'','user','password',0,1)
cur=con.cursor()

try:
	cur.execute('select * from my_nonexistant_table')
except PySQLRDB.DatabaseError, e:
	print e
Bind Variables

Programs rarely execute fixed queries. More often than not, some part of the query is dynamically generated. The Python DB API provides a means for using bind variables in those queries.

For a detailed discussion of binds, see this document.

from SQLRelay import PySQLRDB

con=PySQLRDB.connect('host',9000,'','user','password',0,1)
cur=con.cursor()

cur.execute('select * from my_table where column1>:val1 and column2=:val2 and column3<:val3',{'val1':1,'val2':'hello','val3':50.546})

... process the result set ...
Re-Binding and Re-Execution

A feature of the prepare/bind/execute paradigm is the ability to prepare, bind and execute a query once, then re-bind and re-execute the query over and over without re-preparing it. If your backend database natively supports this paradigm, you can reap a substantial performance improvement.

The Python DB API supports this paradigm via the executemany method. If you pass in a list of parameter dictionaries, the query will be re-executed for each dictionary of bind variable/values.

from SQLRelay import PySQLRDB

con=PySQLRDB.connect('host',9000,'','user','password',0,1)
cur=con.cursor()

cur.executemany('insert into my_table values (:val1,:val2,:val3)',
		[{'val1':1,'val2':'hello','val3':1.11},
		{'val1':2,'val2':'hi','val3':2.22},
		{'val1':3,'val2':'bye','val3':3,33}])
Accessing Fields in the Result Set

The fetchone(), fetchmany() and fetchall() methods are useful for processing result sets. fetchone() returns a list of values. fetchmany() and fetchall() each return a list of rows where each row is a list of values.

The rowcount member variable gives the number of rows in the result set of a select query or the number of rows affected by an insert/update/delete query. 

from SQLRelay import PySQLRDB

con=PySQLRDB.connect('host',9000,'','user','password',0,1)
cur=con.cursor()

cur.execute('select * from my_table')

print 'rowcount:', cur.rowcount

print 'the first row:'
print cur.fetchone()
print

print 'the next three rows:'
print cur.fetchmany(3)
print

print 'the rest of the rows:'
print cur.fetchall()
print
Cursors

Cursors make it possible to execute queries while processing the result set of another query. You can select rows from a table in one query, then iterate through it's result set, inserting rows into another table, using only 1 database connection for both operations.

For example:

from SQLRelay import PySQLRDB

con=PySQLRDB.sqlrconnection('host',9000,'','user','password',0,1)
cursor1=con.cursor()
cursor2=con.cursor()

cursor1.execute('select * from my_huge_table')

for a in cursor1.fetchall():
        cursor2.execute('insert into my_other_table values (:1,:2,:3)',{':1',a[0],':2',a[1],':3',a[2]})
Getting Column Information

After executing a query, column information is stored in the desc variable. desc is a list of tuples. Each tuple corresponds to a column, containing it's name, type and length. 

from SQLRelay import PySQLRDB

con=PySQLRDB.connect('host',9000,'','user','password',0,1)
cur=con.cursor()

cur.execute('select * from my_table')

for name,type,length in cur.desc:
        print 'Name:          ', name
        print 'Type:          ', type
        print 'Length:        ', length