[PREVIOUS] [HOME] [NEXT]

Methods for querying the data

Database-query operations are performed by invoking statements based on the SQLite syntax. There are several for options for retrieving data utilizing conditional arguments in this way.

Using the terminal command line

The sqlite3 engine provides a terminal-based front-end to the SQLite library able to evaluate queries interactively and display the corresponding results. In the following example, we query the atlas_baghdad_py3.db database via the sqlite "interpreter" to retrieve symbol, sample_composition, mass, and exposure_time for all entries in the sample table having an atomic number within the range 30≤Z≤40:

	amhurst@amhurst-office:sql_codes$ sqlite3 atlas_baghdad_py3.db
	SQLite version 3.13.0 2016-05-18 10:57:30
	Enter ".help" for usage hints.
	sqlite> .header on
	sqlite> .mode column
	sqlite> SELECT symbol, sample_composition, mass, exposure_time
	...> FROM sample
	...> WHERE Z >= 30 AND Z <=40;
	symbol      sample_composition  mass        exposure_time
	----------  ------------------  ----------  -------------
	Zn          Zn                  31.5        6.0          
	Ga          Ga                  15.5        23.0         
	Ge          Ge                  4.7         44.0         
	As          As                  22.1        21.0         
	Se          Se                  24.0        12.9         
	Br          BrInGlass           32.0        12.0         
	Rb          Rb2CO3              15.5        23.0         
	Sr          SrCO3               12.1        9.0          
	Y           Y_NO3_3_6H2O        28.9        22.0         
	Zr          ZrO2                42.0        16.1         
	sqlite>
	sqlite>.exit
	amhurst@amhurst-office:sql_codes$

Note that a semicolon ";" is required to terminate a SQL statement. Alternatively, we can read a SQL script from a file. All SQL scripts are located inside the directory ~/BaghdadAtlas/sql_codes. To run a script from this directory, e.g., getNG_data.sql:

	amhurst@amhurst-office:sql_codes$ sqlite3 -header -column atlas_baghdad_py3.db
	SQLite version 3.13.0 2016-05-18 10:57:30
	Enter ".help" for usage hints.
	sqlite> .read getNG_data.sql
	target      compound    sample       E [keV]     dE [keV]    BR          dBR        cross section [mb]  error cs [mb]  
	----------  ----------  ----------  ----------  ----------  ----------  ----------  ------------------  ---------------
	Tb          NG          N           339.2       0.4         4.6         0.6         16.36128            3.3508277090892
	Tb          NG          N           451.3       1.0         1.5         0.6         5.3352              2.2943267436004
	Tb          NG          N           596.4       0.6         4.2         0.8         14.93856            3.6959557399947
	Ho          NG          N           136.0       0.3         11.0        3.0         141.57              44.763924776989
	Ho          NG          N           181.3       0.2         2.9         0.3         37.323              7.1111379555173
	Ho          NG          N           238.4       0.2         4.5         0.5         57.915              11.281639684017
	Ho          NG          N           289.0       0.0         3.9         0.7         50.193              12.068848933282
	Ho          NG          N           416.8       0.4         2.4         0.3         30.888              6.2714811429518
	Ho          NG          N           426.4       0.0         3.5         0.5         45.045              9.6619333903727
	Ho          NG          N           454.0       0.0         1.5         0.3         19.305              4.9444925361456
	Ho          NG          N           543.6       0.2         6.2         0.4         79.794              13.765871362234
	Tm          NG          N           236.4       0.3         30.0        4.0         25.272              5.3939918576134
	Tm          NG          N           551.6       0.4         13.0        2.0         10.9512             2.4839295642187
	Tm          NG          N           562.3       0.4         82.0        10.0        69.0768             14.265634925933
	Tm          NG          N           570.3       0.4         64.0        8.0         53.9136             11.232         
	Tm          NG          N           662.1       0.0         19.0        3.0         16.0056             3.6746196537873
	Tm          NG          N           679.8       0.0         18.0        2.0         15.1632             3.0373163944508
	Tm          NG          N           716.6       0.0         19.0        3.0         16.0056             3.6746196537873
	Re          NG          N           149.5       0.6         8.0         3.0         7.488               3.0243085556867
	Re          NG          N           209.4       0.8         16.0        5.0         14.976              5.1912149791739
	Re          NG          N           214.5       0.3         24.0        4.0         22.464              5.0370368432244
	Re          NG          N           252.0       0.3         20.0        3.0         18.72               3.9711116831436
	Re          NG          N           274.7       0.8         11.0        3.0         10.296              3.2046895887121
	Re          NG          N           290.3       0.3         18.0        2.0         16.848              3.1450157137922
	Re          NG          N           316.3       0.4         10.0        2.0         9.36                2.34           
	Re          NG          N           390.1       0.4         7.2         1.0         6.7392              1.3776699076339
	Ir          NG          N           294.6       0.0         32.0        4.0         12.28032            2.1445637229049
	Ir          NG          N           351.4       0.08        87.0        7.0         33.38712            4.8779341633933
	Ir          NG          N           418.2       0.2         21.0        5.0         8.05896             2.1558500133358
	Ir          NG          N           449.4       0.2         24.0        4.0         9.21024             1.9020846567910
	No. (n,g) lines:
	----------------
	30              
	sqlite>
      

The above example uses information from both relational tables to calculate partial γ-ray cross sections for all (n,γ) lines in the database and then counts the number of (n,γ) lines. The sqlite3 engine can also be invoked in batch mode. Thus, we can also run the getNG_data.sql script directly from the command line:

	sqlite3 -header -column atlas_baghdad_py3.db < getNG_data.sql

Shell scripts also provide batch-processing features. A bash script run_sql.sh is provided inside the ~/BaghdadAtlas/sql_codes folder that may be invoked to run the other SQL scripts in the same location. This bash script determines the Python version running locally and the corresponding database to use. The only variable the user needs to adjust is SQL_SCRIPT according to the desired SQL script. By default, this variable is set to:

	SQL_SCRIPT=getCountingStats.sql

Using the Jupyter (IPython) Notebook

The Jupyter (IPython) Notebook provides a nice API for the SQLite libraries. To use the notebook provided in this package sql_queries_atlas.ipynb and take advantage of the visualization methods for displaying the data a few additional Python dependencies are required: numpy; sqlite3; matplotlib; seaborn. The notebook is located inside the ~/BaghdadAtlas/notebook_analysis folder and may be launched from this location at the command line using:

	jupyter notebook sql_queries_atlas.ipynb

The notebook will then open inside a web browser and provides a Python-based interface to the SQLite libraries. To execute a cell in a Jupyter Notebook press <SHIFT> + <ENTER> or <CTRL> + <ENTER>. A few examples highlighting methods for interacting with and manipulating the data are given below; the syntax is a combination of Python and SQL.

Example 1: Condition on atomic number

Jupyter Z
Query: Display all information from sample relational table for all entries with atomic number Z < 15.

Example 2: Populate list with conditional data

Jupyter list
Query: Fill list with names of enriched isotopes and count number of enriched isotopes.

Example 3: Write query output to file

Jupyter file
Query: Print γ-ray energy and intensity column data to file for nickel (Z = 28): data_Nickel.dat.

Example 4: Plotting from a database

Jupyter plot method
Visualization of cross section data for natural rhenium using matplotlib, seaborn, and numpy methods. If the cell magic %matplotlib inline is invoked, the resulting plot will also be output to the notebook, as well as generating pdf and png files according to the above method.
Re plot

Absolute partial γ-ray cross sections for natural rhenium (and other elements/isotopes) are determined relative to the 2+1 → 0+gs transition in 56Fe which has a cross section σγ = 468 mb. See here for more details. Note that a γ-ray energy cut of 1000 keV has been imposed on the above plot.

The notebook also provides examples of writing γ-ray energy and cross-section data to file and generating plots with error bars, such as the one shown below for Re, for a user-defined nucleus (A, Z, Chem_symb). This provides a convenient means for automating the data of interest and generating presentation-ready plots.

Re plot with errors

Using a GUI

For those who would prefer to interact with the data through a GUI, there are several options. For example, the DB Browser for SQLite and SQLiteStudio are both open source have cross-platform distributions. Another option is the Firefox Add-On, SQLite Manager. A screen shot illustrating the SQLite Manager Add-On connected to the "Baghdad Atlas" database atlas_baghdad_py3.db is shown below.

SQLiteManager GUI
Visualization of the Baghdad Atlas SQL database using SQLite Manager. The first few rows of the sample relational table are shown.
NSSC