[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:

	user@machine:sql_codes$ sqlite3 atlas_baghdad_py3.db
	SQLite version 3.29.0 2019-07-10 17:32:03
	Enter ".help" for usage hints.
	sqlite> .header on
	sqlite> .mode column
	sqlite> SELECT symbol AS "chemical symbol", Z AS "atomic number",
	...> sample_composition AS "sample composition", mass AS "mass [g]", 
	...> exposure_time AS "exposure time [h]" 
	...> FROM sample
	...> WHERE Z >= 30 AND Z <=40;
	chemical symbol  atomic number  sample composition  mass [g]    exposure time [h]
	---------------  -------------  ------------------  ----------  -----------------
	Zn               30             Zn                  31.5        6.0              
	Ga               31             Ga                  15.5        23.0             
	Ge               32             Ge                  4.7         44.0             
	As               33             As                  22.1        21.0             
	Se               34             Se                  24.0        12.9             
	Br               35             BrInGlass           32.0        12.0             
	Rb               37             Rb2CO3              15.5        23.0             
	Sr               38             SrCO3               12.1        9.0              
	Y                39             Y_NO3_3_6H2O        28.9        22.0             
	Zr               40             ZrO2                42.0        16.1             
	sqlite>
	sqlite>.exit
	user@machine:sql_codes$

Note that a semicolon

 user@machine:sql_codes$ SQLite version 3.29.0 Enter ".help" for usage hints. sqlite> .read getNG_dataVarsErr.sql load_extension('../UDF/sq -------------------------

target      residual    sample ----------  ---------- Tb          NG          N Tb          NG          N Tb          NG          N Ho          NG          N Ho          NG          N Ho          NG          N Ho          NG          N Ho          NG          N Ho          NG          N Ho          NG          N Ho          NG          N Tm          NG          N Tm          NG          N Tm          NG          N Tm          NG          N Tm          NG          N Tm          NG          N Tm          NG          N Re          NG          N Re          NG          N Re          NG          N Re          NG          N Re          NG          N Re          NG          N Re          NG          N Re          NG          N Ir          NG          N Ir          NG          N Ir          NG          N Ir          NG          N No. (n,g) lines: ---------------- 30 sqlite>

";" 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: sqlite3 -header -column atlas_baghdad_py3.db 2019-07-10 17:32:03 lite-amalgamation/libsqlitefunctions.so') ----------------------------------------- E [keV] dE [keV] BR dBR cross section [mb] error cs [mb] ---------- ---------- ---------- ---------- ---------- ------------------ --------------- 339.2 0.4 4.6 0.6 11.5179216 2.6995742943076 451.3 1.0 1.5 0.6 3.755844 1.6709112478099 596.4 0.6 4.2 0.8 10.5163632 2.8646636456178 136.0 0.3 11.0 3.0 99.66165 33.497335448877 181.3 0.2 2.9 0.3 26.274435 5.8333805440887 238.4 0.2 4.5 0.5 40.770675 9.2015212426340 289.0 0.0 3.9 0.7 35.334585 9.4023071572226 416.8 0.4 2.4 0.3 21.74436 5.0629891983245 426.4 0.0 3.5 0.5 31.710525 7.7023502986190 454.0 0.0 1.5 0.3 13.590225 3.8098750529416 543.6 0.2 6.2 0.4 56.17293 11.614694422055 236.4 0.3 30.0 4.0 17.79084 4.3047111227286 551.6 0.4 13.0 2.0 7.709364 1.9569727630378 562.3 0.4 82.0 10.0 48.628296 11.470510816151 570.3 0.4 64.0 8.0 37.953792 9.0129618405494 662.1 0.0 19.0 3.0 11.267532 2.8880661767418 679.8 0.0 18.0 2.0 10.674504 2.4600879940750 716.6 0.0 19.0 3.0 11.267532 2.8880661767418 149.5 0.6 8.0 3.0 5.27136 2.2121829836647 209.4 0.8 16.0 5.0 10.54272 3.8469576268090 214.5 0.3 24.0 4.0 15.81408 3.9777343001945 252.0 0.3 20.0 3.0 13.1784 3.1735097597455 274.7 0.8 11.0 3.0 7.24812 2.4025120784553 290.3 0.3 18.0 2.0 11.86056 2.5940711988409 316.3 0.4 10.0 2.0 6.5892 1.8104138449536 390.1 0.4 7.2 1.0 4.744224 1.1103945539532 294.6 0.0 32.0 4.0 8.6450304 1.8027999863316 351.4 0.08 87.0 7.0 23.5036764 4.3552277090380 418.2 0.2 21.0 5.0 5.6733012 1.6496688046614 449.4 0.2 24.0 4.0 6.4837728 1.5294014421535

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

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

Example 2: Populate list with conditional data

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

Example 3: Write query output to file

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

Example 4: Plotting from a database

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.

Absolute partial γ-ray cross sections for natural rhenium (and other elements/isotopes) are determined relative to the 2⁺₁ → 0⁺_gs transition in ⁵⁶Fe where an assumed cross section, σ_γ = 329.46 mb, is used for normalization. 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 natural 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.

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.

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