SQL for c2d

SQL

Structured Query Language (SQL) is a powerful database language, but often considered too complicated for end-users (scientists?) to use directly. A simple web-form interface to the database often suffices (which we maintain on the YStars Data Retrieval & Analysis Tools webpage). For more complex operations which the web page builder had not considered one may have to go back to the roots of this evil, assuming SQL can efficiently handle the query (which is not always the case).

There are many different database engines that speak SQL, both commercial and opensource. For the c2d project we use MySQL. It comes with most linux distributions. A good programmable method to create forms and return feedback via a webpage is php, which is normally part of the Apache web server.

An alternative approach to interact with the database is using python, for which the MySQL-python (MySQLdb module) is the choice.

Also worth considering are products like MyClient, which provide an (open source) web client interface to MySQL.

MySQL

Using MySQL directly can be done in two ways. Either from within the MySQL itself, or by constructing the query on the commandline and returning the data on stdout for further processing (awk, sm, wip, etc.)

% mysql -pc2d2 -uroot c2d

mysql> SELECT c2d_ID, RA2000, DEC2000 FROM Stars WHERE  Stars.id < 10;
+-------------------------+------------+------------+
| c2d_ID                  | RA2000     | DEC2000    |
+-------------------------+------------+------------+
| SSTc2d J042924.6+263449 |  67.352496 |  26.580416 |
| SSTc2d J042924.7+263546 |  67.352857 |  26.596136 |
| SSTc2d J042924.8+263418 |   67.35335 |  26.571535 |
| SSTc2d J042924.8+263434 |  67.353406 |  26.576101 |
| SSTc2d J042925.7+263435 |  67.356962 |   26.57629 |
| SSTc2d J042927.4+263349 |   67.36397 |  26.563688 |
| SSTc2d J042927.4+263201 |  67.363997 |   26.53363 |
| SSTc2d J042928.7+263401 |  67.369589 |  26.567039 |
| SSTc2d J042929.5+263153 | 67.3729037 | 26.5313133 |
+-------------------------+------------+------------+
9 rows in set (0.12 sec)

mysql> quit


% mysql -pc2d2 -uroot c2d -e 'SELECT c2d_ID, RA2000, DEC2000 FROM Stars WHERE  Stars.id < 10'

+-------------------------+------------+------------+
| c2d_ID                  | RA2000     | DEC2000    |
+-------------------------+------------+------------+
| SSTc2d J042924.6+263449 |  67.352496 |  26.580416 |
| SSTc2d J042924.7+263546 |  67.352857 |  26.596136 |
| SSTc2d J042924.8+263418 |   67.35335 |  26.571535 |
| SSTc2d J042924.8+263434 |  67.353406 |  26.576101 |
| SSTc2d J042925.7+263435 |  67.356962 |   26.57629 |
| SSTc2d J042927.4+263349 |   67.36397 |  26.563688 |
| SSTc2d J042927.4+263201 |  67.363997 |   26.53363 |
| SSTc2d J042928.7+263401 |  67.369589 |  26.567039 |
| SSTc2d J042929.5+263153 | 67.3729037 | 26.5313133 |
+-------------------------+------------+------------+

%

If you are now wondering how the heck one would remember all these table and column names? Indeed, this is one reason why a web interface is nice. However, it can then also become a burden. A little study of the MySQL manual might also be in place, but although there is always the help command, here are two useful commands to learn about these tables and their columns (note in MySQL commands needs to be terminated with a semi-colon!):

mysql> show tables;
+---------------+
| Tables_in_c2d |
+---------------+
| Cha           |
| Cha_matches   |
| Cha_ophot     |
| Cores         |
| Cores_matches |
| Cores_ophot   |
| Lup           |
| Lup_matches   |
| Lup_ophot     |
| Oph           |
| Oph_matches   |
| Oph_ophot     |
| Per           |
| Per_matches   |
| Per_ophot     |
| Ser           |
| Ser_matches   |
| Ser_ophot     |
| Stars         |
| Stars_matches |
| Stars_ophot   |
| Swire         |
| Swire_matches |
| Swire_ophot   |
+---------------+

mysql> describe Stars_ophot;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) |      | PRI | NULL    | auto_increment |
| J_flux_c  | float   | YES  |     | NULL    |                |
| J_dflux_c | float   | YES  |     | NULL    |                |
| H_flux_c  | float   | YES  |     | NULL    |                |
| H_dflux_c | float   | YES  |     | NULL    |                |
| K_flux_c  | float   | YES  |     | NULL    |                |
| K_dflux_c | float   | YES  |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
 

Python

An obvious big advantage of using python is the large amount of software that is available to analyse the data as you have extracted it out of the database

% python
>>> import MySQLdb
>>> db=MySQLdb.connect('localhost','root','c2d2','c2d')
>>> c=db.cursor()

>>> n=c.execute("""SELECT c2d_ID, RA2000, DEC2000 FROM Stars WHERE  Stars.id < 10;""")
>>> a=c.fetchall()
>>> a
[('SSTc2d J042924.6+263449', 67.352496000000002, 26.580416),
 ('SSTc2d J042924.7+263546', 67.352857, 26.596136000000001),
 ('SSTc2d J042924.8+263418', 67.353350000000006, 26.571535000000001),
 ('SSTc2d J042924.8+263434', 67.353406000000007, 26.576101000000001),
 ('SSTc2d J042925.7+263435', 67.356961999999996, 26.57629),
 ('SSTc2d J042927.4+263349', 67.363969999999995, 26.563687999999999),
 ('SSTc2d J042927.4+263201', 67.363996999999998, 26.533629999999999),
 ('SSTc2d J042928.7+263401', 67.369589000000005, 26.567039000000001),
 ('SSTc2d J042929.5+263153', 67.372903699999995, 26.531313300000001)]

>>> ra=float(a[3][1])
>>> dec=float(a[3][2])

>>> a=c.fetchmany(3)