Python Programming for Biology: Bioinformatics and Beyond

Querying the database

Instead  of  loading  records  from  the  database  into  a  Structure  object,  and  manipulating

those,  we  can  just  manipulate  the  records  directory.  For  example,  suppose  we  want  to

count all the chains in the database of a specified molType. Then we can do as follows:

def countChainMolType(connection, molType, placeHolder='%s'):

cursor = connection.cursor()

try:

# get matching chain records from database

stmt = "select * from chain where molType=%s"

stmt = formatStatement(stmt, placeHolder)

cursor.execute(stmt, (molType,))

result = cursor.fetchall()

count = len(result)

finally:

cursor.close()

return count

Alternatively, we could use the SQL statement

"select count(*) from chain where molType=%s"

which counts the records and returns that result.

The test code, for sqlite3, is:

import sys

if __name__ == '__main__':

import sys

if len(sys.argv) != 3:

print('need to specify database, molType')

sys.exit(1)

database = sys.argv[1]

molType = sys.argv[2]

import sqlite3

connection = sqlite3.connect(database)

placeHolder = '?'

---

try:

count = countChainMolType(connection, molType,

placeHolder)

print 'Found %d chain(s)' % count

finally:

connection.close()

Note that we do not even have to deal with the Structure table in order to answer this

particular query, but just the Chain table.

The  Structure  object  is  useful  to  have  if  there  is  a  general  application  dealing  with

structures and where it is useful to have the structures all in memory at the same time, and

where  the  database  just  happens  to  be  the  way  that  the  data  is  stored.  But  in  some

applications, e.g. using a browser to display information about structures that are stored in

a  remote  database,  it  is  quite  possible  that  just  querying  the  database  and  using  the  data

directly without creating a Structure is the best way to proceed.