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.
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.
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|