SPIRE - Database

Using an external database with Spire

If your lab already has a database of reconstruction projects, Spire can communicate with it. This generally occurs at the beginning and end of a project. At the start of a project Spire can download some project parameters (e.g., electron voltage, pixel resolution, etc) from the database. At the end of a project, final volumes, resolution information, etc. can be uploaded into the database.

Since Spire does not know which database you are using, nor anything about the internal organization of that database, you will have to supply that information. This usually means
- installing an additional Python database library,
- writing some Python code that enables Spire to access your database (code for MySQL is included in Spire package).

Setting up Spire to connect to an external database:

Find, install, and test the Python module for the database you are using.
For MySQL, this means installing MySQLdb, which can be downloaded from sourceforge.net/projects/mysql-python. For other databases, Python modules for connecting to Informix, Ingres, ODBC, Oracle, Postgres, Sybase and others are at www.python.org/topics/database/modules.html. They usually come with some python test programs - use the test programs to make sure Python can connect properly with your database.

Create a module for Spire, called spiderLocalDB.py
For MySQL, such a module is included in the Spire package, called spiderLocalMysql.py. This file should be copied to spiderLocalDB.py.
For other databases, write a similar Python module for your database, using spiderLocalDB.py as a template (see an example). This module must contain a class spiderDatabase, which inherits from spiderClasses.SpiderDatabase. This class object must have the following attributes and methods:

Attributes

databaseName a database name to present to the user, e.g., "the Project Database"

id the project ID number is used to specify a particular project in the database.

projectquery another SQL query to fetch project information

Methods

__init__() sets the required attributes.

isExtDatabaseAlive() returns 1 or 0 depending on whether it can successfully connect.

sendQuery(query) returns results of SQL statement

getProjectInfo() using projectquery, fetches project information, and inserts it into GB.P, the internal class object of the current Spire project.

upload(upload_object) given an uploadable object, establish a connection and insert data into appropriate places in the external database

Copy your database module to spiderLocalDB.py in $PYTHONPATH/site-packages/Spire

Attributes
`databaseName`	a database name to present to the user, e.g., "the Project Database"
`id`	the project ID number is used to specify a particular project in the database.
`projectquery`	another SQL query to fetch project information
Methods
`__init__()`	sets the required attributes.
`isExtDatabaseAlive()`	returns 1 or 0 depending on whether it can successfully connect.
`sendQuery(query)`	returns results of SQL statement
`getProjectInfo()`	using `projectquery`, fetches project information, and inserts it into GB.P, the internal class object of the current Spire project.
`upload(upload_object)`	given an uploadable object, establish a connection and insert data into appropriate places in the external database

Example: setting up Spire to connect to a MySQL database

The Spire distribution contains example code for a tiny project database in MySQL.

You must first:

have MySQL installed,

have the MySQL server started,

have the Python mySQL interface, MySQLdb, installed
(download from sourceforge.net/projects/mysql-python)

create the test database with testmysql.py, which creates a table in the specified database and inserts some test data in it. Usage:

testmysql.py -u user -p password -d database -t table

All arguments are optional. If you type just

testmysql.py

it will use the following default values:


database = "test_spire"

table = "projects"

user = ""   i.e., anonymous login must be enabled for mysql

password = ""

testmysql.py will create the table projects in the database test_spire and put some values in the table.


CHECK THE FIELD NAMES
Data loaded into table 'projects':
+------------+-----------+------+--------------+----------+
| project_id | pixelsize | kev  | project_file | data_ext |
+------------+-----------+------+--------------+----------+
| 149        | 4.78      | 100  | proj149      | xyz      |
+------------+-----------+------+--------------+----------+

"database", "user", and "password" must be set up for your MySQL database.
"newtable" is the name of the new table created to hold project information.

loadtable.py database user password table

puts sample project data into the table.

E.g., for database 'test' and user 'brian', whose password is 'heinz57'

newtable.py test brian heinz57 projects

creates a new table called 'projects' (note this is not a secure way to handle passwords)

loadtable.py test brian heinz57 projects

then loads the test data.

copy this file, spiderLocalMysql.py, to spiderLocalDB.py, and run Spire. Start a new project, enter project number 149. It should fetch data from the MySQL database.