This example shows how to create a blank ODM2 database and load the controlled vocabulary terms from http://vocabulary.odm2.org. This example uses SQLite for the database because it doesn't require a server. However, the process for creating databases using other relational database management systems is very similar. Data Definition Language scripts for creating blank instances of ODM2 are available for:
For details of the blank schema scripts, see: https://github.com/ODM2/ODM2/tree/master/src/blank_schema_scripts
First import the necessary libraries
import os
import sqlite3
from IPython.core.display import display, HTML
Create a new, blank ODM2 SQLite database into which data can be loaded. The DDL SQL script for creating the blank ODM2 database has been uploaded to the "utilities" folder on this Jupyter server for your convenience, but it is available in GitHub at: https://github.com/ODM2/ODM2/tree/master/src/blank_schema_scripts/sqlite
NOTE: The output SQLite database is written to the "Data" directory on this server. Click the "Control Panel" button at the top of this window then click the "My Server" link. Navigate to the Notebooks --> Data folder.
# First check to see if the ODM2 SQLite file already exists from previous runs of this example. If so, delete it.
if os.path.isfile('data/ODM2_Example1.sqlite'):
os.remove('data/ODM2_Example1.sqlite')
# Create a new SQLite database and get a cursor
conn = sqlite3.connect('data/ODM2_Example1.sqlite')
c = conn.cursor()
# Open the DDL SQL file for ODM2
sqlString = open('code/ODM2_for_SQLite.sql', 'r').read()
# Execute the DDL SQL script on the blank SQLite database
c.executescript(sqlString)
# Close the connection to the database
conn.close()
print "Done creating ODM2 database!"
Load the controlled vocabularies into the blank database. The cvload.py script has been uploaded to the "utilities" folder on this Jupyter server for your convenience, but it is available in GitHub at: https://github.com/ODM2/ODM2/tree/master/src/load_cvs.
The cvload.py script retrieves all of the CV terms from the online CV management system at http://vocabulary.odm2.org and loads them into the corresponding tables in the ODM2 database.
# Run the CV Loader script
%run code/cvload.py sqlite:///data/ODM2_Example1.sqlite
print "Done loading controlled vocabularies!"
Check to make sure the CVs got loaded correctly. Do a quick query on one of the CV tables to make sure. Print one term from the SiteType CV table. Other CVs you may be interested include: CV_ActionType, CV_MethodType, CV_Medium, etc. To explore other CVs, just change the name of the CV and re-run the code block below.
conn = sqlite3.connect('data/ODM2_Example1.sqlite')
c = conn.cursor()
cvName = 'CV_SiteType'
sqlString = 'SELECT Name, Definition FROM ' + cvName
c.execute(sqlString)
rows = c.fetchall()
print rows[0]
conn.close()
At this point, I now have a blank ODM2 database in a SQLite file with all of the controlled vocabulary terms from http://vocabulary.odm2.org that I could load data into. Execute the following code to get a link to download the SQLite file that you just created. SQLite files can be viewed and queried using a number of different tools, including the Firefox browser SQLite Manager Program available at: https://addons.mozilla.org/En-us/firefox/addon/sqlite-manager/
print("\nYou can download the blank ODM2 SQLite database with populated CVs using the following link:\n")
dbname = 'ODM2_Example1.sqlite'
display(HTML('<a href=%s target="_blank">%s<a>' % ('data/%s' % dbname, dbname)))