Searching the stars

Today I pulled the star data I described in the post into an sqlite3 database so I could explore the data with SQL queries.

The data is handily already in CSV format. I could have used sqlite3 directly to read the CSV into a virtual table and migrate it from there, but I preferred to filter the data in python instead.

This was pretty straight forward, but things I'd not actually done before:

  • Used csv.DictReader (I should have been using this years ago, or maybe I've forgotten I have).
  • Used sqlite3 from python to do a bulkish insert. The python library sqlite3 provides an executemany.

csv.DictReader:

(https://docs.python.org/3.6/library/csv.html)

This class adds features to the base CSV reader I've used many times, it allows a user to definte columns names, and if one is not provided it will read the csv header line to determine them. A DictReader returns rows as an OrderedDict, allowing access via name like row['id'] and creates a more useful relationship between data and code.

sqlite3

I have used sqlite3 as a part of other applications, but this was the first time I used it for my own fun, I usually just stick with postgres these days. As I mentioned before, there are many ways to insert data from a CSV into a sqlite3 table, I chose to write a python script to pick the fields I wanted.

Picking data from the the CSV reader I generated a single list of rows, each row in the order of the SQL table I'd created. Using the Cursor.executemany() method was fast enough for this small dataset.

cursor.executemany('INSERT INTO stars VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', rows)

was all it took.

Now I can do this!

sqlite> select bayer_flamsteed_designation, distance, magnitude, proper_name  from stars where `constellation` == "Ori" and `bayer_flamsteed_designation` != "" and magnitude < 5;  
1Pi 3Ori|8.0684|3.19|  
2Pi 2Ori|68.8231|4.35|  
3Pi 4Ori|322.5806|3.68|  
4Omi1Ori|199.6008|4.71|  
8Pi 5Ori|411.5226|3.71|  
7Pi 1Ori|35.6633|4.64|  
9Omi2Ori|57.0125|4.06|  
10Pi 6Ori|289.8551|4.47|  
11    Ori|111.9821|4.65|  
15    Ori|165.0165|4.81|  
17Rho Ori|107.2961|4.46|  
19Bet Ori|264.5503|0.18|Rigel  
20Tau Ori|151.5152|3.59|  
22    Ori|284.9003|4.72|  
23    Ori|476.1905|4.99|  
29    Ori|48.2393|4.13|  
28Eta Ori|299.4012|3.35|  
25Psi1Ori|318.4713|4.89|  
24Gam Ori|77.3994|1.64|Bellatrix  
30Psi2Ori|348.4321|4.59|  
31    Ori|155.7632|4.71|  
32    Ori|92.8505|4.2|  
36Ups Ori|877.193|4.62|  
34Del Ori|212.3142|2.25|Mintaka  
37Phi1Ori|333.3333|4.39|  
39Lam Ori|336.7003|3.39|  
41The1Ori|100000.0|4.98|  
43The2Ori|473.9336|4.98|  
42    Ori|271.0027|4.58|  
44Iot Ori|714.2857|2.75|Hatsya  
46Eps Ori|606.0606|1.69|Alnilam  
40Phi2Ori|36.0231|4.09|  
48Sig Ori|328.9474|3.77|  
49    Ori|44.603|4.77|  
47Ome Ori|423.7288|4.5|  
50Zet Ori|225.7336|1.74|Alnitak  
51    Ori|90.009|4.9|  
53Kap Ori|198.4127|2.07|Saiph  
56    Ori|386.1004|4.76|  
54Chi1Ori|8.6633|4.39|  
58Alp Ori|152.6718|0.45|Betelgeuse  
61Mu  Ori|47.5059|4.12|  
62Chi2Ori|552.4862|4.64|  
67Nu  Ori|158.2278|4.42|  
70Xi  Ori|186.2197|4.45|  
69    Ori|162.0746|4.95|