Using an external database with Spire
Spire can communicate with an external database. 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.
Setting up Spire to connect to an external database: the general case
-
Find, install, and test the Python module for the database you are using.
Modules for connecting to Informix, Ingres, MySQL, 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
Using spiderLocalDB.py as a template, write a similar Python module for your database, say MySQLDB.py. 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 Archive" |
id | the project ID number is used to specify a particular project in the database. |
testquery | simple SQL query to test the connection. |
projectquery | another SQL query to fetch project information |
| Methods | |
|---|
isExtDatabaseAlive() | returns 1 or 0 depending on whether it can successfully connect. |
sendQuery(query) | returns results of SQL statement |
getProjectInfo() | using the 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
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 tiny test database with newtable.py, and loadtable.py.
newtable.py creates a table; loadtable.py inserts some test data in it. :
newtable.py database user password newtable
"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.
