SQLite demo notebook¶

MCS 275 Spring 2021 - Emily Dumas¶

We'll work with the SQLite star database available from:

hyg_data.zip (original source: https://github.com/astronexus/HYG-Database )

import os
# Set this to directory containing the database file.
# In this case, I unzipped it into the notebook dir 
# so I can just use "." (current dir)
DATADIR = "."
DBFILE = os.path.join(DATADIR,"hyg_data.sqlite")
DBFILE

'./hyg_data.sqlite'

os.path.exists(DBFILE)

True

import sqlite3
con = sqlite3.connect(DBFILE)

c = con.execute("SELECT proper,ra,dec,mag FROM stars LIMIT 10")
c.fetchone()

('Sol', 0.0, 0.0, -26.7)

c = con.execute("SELECT proper,ra,dec,mag FROM stars LIMIT 10")
c.fetchall()

[('Sol', 0.0, 0.0, -26.7),
 (None, 6e-05, 1.089009, 9.1),
 (None, 0.000283, -19.49884, 9.27),
 (None, 0.000335, 38.859279, 6.61),
 (None, 0.000569, -51.893546, 8.06),
 (None, 0.000665, -40.591202, 8.55),
 (None, 0.001246, 3.946458, 12.31),
 (None, 0.00147, 20.036114, 9.64),
 (None, 0.001823, 25.886461, 9.05),
 (None, 0.002355, 36.585958, 8.59)]

Getting rows as dictionary-like objects¶

import sqlite3
con = sqlite3.connect(DBFILE)
con.row_factory = sqlite3.Row
# Roughly equivalent to using csv.DictReader instead of csv.reader

c = con.execute("SELECT proper,ra,dec,mag FROM stars LIMIT 10")
r = c.fetchone()

r["proper"]  # sqlite3.Row[columnname] gives you the value in a column

'Sol'

r[0]  # column 0 in the order of the query

'Sol'

r.keys()

['proper', 'ra', 'dec', 'mag']

print(r)

<sqlite3.Row object at 0x7f02a9782f10>

Brightest stars¶

c = con.execute("SELECT proper,ra,dec,mag FROM stars ORDER BY mag LIMIT 10")
for r in c:
    print(tuple(r))

('Sol', 0.0, 0.0, -26.7)
('Sirius', 6.752481, -16.716116, -1.44)
('Canopus', 6.399195, -52.69566, -0.62)
('Arcturus', 14.26103, 19.18241, -0.05)
('Rigil Kentaurus', 14.660765, -60.833976, -0.01)
('Vega', 18.61564, 38.783692, 0.03)
('Capella', 5.27815, 45.997991, 0.08)
('Rigel', 5.242298, -8.20164, 0.18)
('Procyon', 7.655033, 5.224993, 0.4)
('Achernar', 1.628556, -57.236757, 0.45)

All named stars¶

c = con.execute("SELECT proper,ra,dec,mag FROM stars WHERE proper IS NOT NULL;")
data = c.fetchall()
print([ r["proper"] for r in data ])

['Sol', 'Alpheratz', 'Caph', 'Algenib', 'Ankaa', 'Shedir', 'Diphda', '96 G. Psc', "Van Maanen's Star", 'Cih', 'Mirach', 'Ruchbah', 'Achernar', 'Sheratan', 'Almaak', 'Hamal', 'Mira', 'Polaris', '268 G. Cet', 'Acamar', 'Menkar', 'Algol', '82 G. Eri', 'Mirphak', 'Alcyone', 'Zaurak', 'Aldebaran', 'Hassaleh', 'Cursa', "Kapteyn's Star", 'Rigel', 'Capella', 'Bellatrix', 'Alnath', 'Nihal', 'Mintaka', 'Arneb', 'Hatsya', 'Alnilam', 'Phakt', 'Alnitak', 'Saiph', 'Betelgeuse', 'Menkalinan', 'Red Rectangle', 'Mirzam', 'Canopus', 'Alhena', 'Sirius', 'Adhara', 'Wezen', 'Aludra', 'Gomeisa', "Luyten's Star", 'Castor', 'Procyon', 'Pollux', 'Naos', 'Avior', 'Miaplacidus', 'Tureis', 'Alphard', 'Ras Elased Australis', 'Regulus', 'Algieba', 'Merak', 'Lalande 21185', 'Dubhe', 'Zosma', 'Denebola', 'Groombridge 1830', 'Phad', 'Megrez', 'Gienah Ghurab', 'Acrux', '3C 273', 'Algorab', 'Gacrux', 'Kraz', 'Porrima', 'Becrux', 'Alioth', 'Cor Caroli', 'Vindemiatrix', 'Mizar', 'Spica', 'Alcor', 'Alkaid', 'Mufrid', 'Hadar', 'Thuban', 'Menkent', 'Arcturus', 'Proxima Centauri', 'Rigil Kentaurus', 'Izar', 'Kochab', 'Zubenelgenubi', 'Zubeneschemali', 'Alphekka', 'Unukalhai', 'Dschubba', 'Graffias', 'Antares', 'Kornephoros', 'Atria', 'Rasalgethi', 'Rastaban', 'Shaula', 'Rasalhague', 'Sargas', 'Cebalrai', 'Etamin', "Barnard's Star", 'Nash', 'Kaus Meridionalis', 'Kaus Australis', 'Kaus Borealis', 'Vega', 'Sheliak', 'Nunki', 'Albaldah', 'Albireo', "Campbell's Hydrogen Star", 'Tarazed', 'Altair', 'Alshain', 'Cygnus X-1', 'Sadr', 'Peacock', 'Deneb', 'Gienah', 'Lacaille 8760', 'Alderamin', 'Sadalsuud', 'Enif', 'Sadalmelik', 'Alnair', 'Kruger 60', 'Matar', "Babcock's star", 'Fomalhaut', 'Scheat', 'Markab', 'Lacaille 9352', 'p Eridani']

All named stars that are also bright¶

c = con.execute("""
SELECT proper,ra,dec,mag FROM stars
WHERE proper IS NOT NULL
AND mag < 1;""")
data = c.fetchall()
print([ r["proper"] for r in data ])

['Sol', 'Achernar', 'Aldebaran', 'Rigel', 'Capella', 'Betelgeuse', 'Canopus', 'Sirius', 'Procyon', 'Acrux', 'Spica', 'Hadar', 'Arcturus', 'Rigil Kentaurus', 'Vega', 'Altair']

Adding a table¶

import sqlite3
con = sqlite3.connect("stars2.sqlite")
con.row_factory = sqlite3.Row

con.execute("""CREATE TABLE named_stars_simple (
  proper TEXT,
  ra REAL,
  dec REAL,
  mag REAL,
  ci REAL
);""")

<sqlite3.Cursor at 0x7f02a91476c0>

con.execute("""CREATE TABLE IF NOT EXISTS named_stars_simple (
  proper TEXT,
  ra REAL,
  dec REAL,
  mag REAL,
  ci REAL
);""")

<sqlite3.Cursor at 0x7f02a9147b90>

c = con.execute("SELECT * FROM named_stars_simple;")
for r in c:
    print(tuple(r))

c = con.execute("""
INSERT INTO named_stars_simple (proper,ci)
VALUES ("Sun",0.6);
""")

c = con.execute("SELECT * FROM named_stars_simple;")
for r in c:
    print(tuple(r))

('Sun', None, None, None, 0.6)

c = con.execute("""
UPDATE named_stars_simple SET mag=-26 WHERE proper="Sun";
""")

c = con.execute("SELECT * FROM named_stars_simple;")
for r in c:
    print(tuple(r))

('Sun', None, None, -26.0, 0.6)

c = con.execute("""DELETE FROM named_stars_simple WHERE proper="Sun";""")

c = con.execute("SELECT * FROM named_stars_simple;")
for r in c:
    print(tuple(r))

c = con.execute("""
INSERT INTO named_stars_simple
SELECT proper,ra,dec,mag,ci FROM stars WHERE proper IS NOT NULL;
""")

c = con.execute("SELECT * FROM named_stars_simple;")
for r in c:
    print(tuple(r))

('Sol', 0.0, 0.0, -26.7, 0.656)
('Alpheratz', 0.139791, 29.090432, 2.07, -0.038)
('Caph', 0.152887, 59.14978, 2.28, 0.38)
('Algenib', 0.220598, 15.183596, 2.83, -0.19)
('Ankaa', 0.438056, -42.305981, 2.4, 1.083)
('Shedir', 0.675116, 56.537331, 2.24, 1.17)
('Diphda', 0.72649, -17.986605, 2.04, 1.019)
('96 G. Psc', 0.806382, 5.280615, 5.74, 0.89)
("Van Maanen's Star", 0.819416, 5.38861, 12.37, 0.554)
('Cih', 0.945143, 60.71674, 2.15, -0.046)
('Mirach', 1.162194, 35.620558, 2.07, 1.576)
('Ruchbah', 1.430216, 60.235283, 2.66, 0.16)
('Achernar', 1.628556, -57.236757, 0.45, -0.158)
('Sheratan', 1.910668, 20.808035, 2.64, 0.165)
('Almaak', 2.064984, 42.329725, 2.1, 1.37)
('Hamal', 2.119555, 23.462423, 2.01, 1.151)
('Mira', 2.322442, -2.977643, 6.47, 0.966)
('Polaris', 2.52975, 89.264109, 1.97, 0.636)
('268 G. Cet', 2.601357, 6.88687, 5.79, 0.918)
('Acamar', 2.971023, -40.304672, 2.88, 0.128)
('Menkar', 3.037992, 4.089734, 2.54, 1.63)
('Algol', 3.136148, 40.955648, 2.09, -0.003)
('82 G. Eri', 3.331944, -43.069784, 4.26, 0.711)
('Mirphak', 3.405378, 49.86118, 1.79, 0.481)
('Alcyone', 3.79141, 24.105137, 2.85, -0.086)
('Zaurak', 3.967157, -13.508515, 2.97, 1.588)
('Aldebaran', 4.598677, 16.509301, 0.87, 1.538)
('Hassaleh', 4.949894, 33.16609, 2.69, 1.49)
('Cursa', 5.130829, -5.086446, 2.78, 0.161)
("Kapteyn's Star", 5.194169, -45.018417, 8.86, 1.543)
('Rigel', 5.242298, -8.20164, 0.18, -0.03)
('Capella', 5.27815, 45.997991, 0.08, 0.795)
('Bellatrix', 5.418851, 6.349702, 1.64, -0.224)
('Alnath', 5.438198, 28.60745, 1.65, -0.13)
('Nihal', 5.470756, -20.759441, 2.81, 0.807)
('Mintaka', 5.533445, -0.299092, 2.25, -0.175)
('Arneb', 5.545504, -17.822289, 2.58, 0.211)
('Hatsya', 5.590551, -5.909901, 2.75, -0.21)
('Alnilam', 5.603559, -1.20192, 1.69, -0.184)
('Phakt', 5.660817, -34.074108, 2.65, -0.12)
('Alnitak', 5.679313, -1.942572, 1.74, -0.199)
('Saiph', 5.795941, -9.669605, 2.07, -0.168)
('Betelgeuse', 5.919529, 7.407063, 0.45, 1.5)
('Menkalinan', 5.992149, 44.947433, 1.9, 0.077)
('Red Rectangle', 6.332838, -10.637414, 8.85, 0.344)
('Mirzam', 6.378329, -17.955918, 1.98, -0.24)
('Canopus', 6.399195, -52.69566, -0.62, 0.164)
('Alhena', 6.628528, 16.399252, 1.93, 0.001)
('Sirius', 6.752481, -16.716116, -1.44, 0.009)
('Adhara', 6.977097, -28.972084, 1.5, -0.211)
('Wezen', 7.139857, -26.3932, 1.83, 0.671)
('Aludra', 7.401584, -29.303104, 2.45, -0.083)
('Gomeisa', 7.452512, 8.289315, 2.89, -0.097)
("Luyten's Star", 7.456805, 5.225785, 9.84, 1.573)
('Castor', 7.576634, 31.888276, 1.58, 0.034)
('Procyon', 7.655033, 5.224993, 0.4, 0.432)
('Pollux', 7.755277, 28.026199, 1.16, 0.991)
('Naos', 8.059737, -40.003148, 2.21, -0.269)
('Avior', 8.375236, -59.509483, 1.86, 1.196)
('Miaplacidus', 9.220041, -69.717208, 1.67, 0.07)
('Tureis', 9.284838, -59.275229, 2.21, 0.189)
('Alphard', 9.45979, -8.658603, 1.99, 1.44)
('Ras Elased Australis', 9.764188, 23.774255, 2.97, 0.808)
('Regulus', 10.139532, 11.967207, 1.36, -0.087)
('Algieba', 10.332873, 19.841489, 2.01, 1.128)
('Merak', 11.030677, 56.382427, 2.34, 0.033)
('Lalande 21185', 11.055632, 35.969877, 7.49, 1.502)
('Dubhe', 11.062155, 61.751033, 1.81, 1.061)
('Zosma', 11.235138, 20.523717, 2.56, 0.128)
('Denebola', 11.817663, 14.57206, 2.14, 0.09)
('Groombridge 1830', 11.88282, 37.718679, 6.42, 0.754)
('Phad', 11.897168, 53.69476, 2.41, 0.044)
('Megrez', 12.257086, 57.032617, 3.32, 0.077)
('Gienah Ghurab', 12.263437, -17.541929, 2.58, -0.107)
('Acrux', 12.443311, -63.099092, 0.77, -0.243)
('3C 273', 12.485193, 2.052398, 12.88, 0.12)
('Algorab', 12.497739, -16.515432, 2.94, -0.012)
('Gacrux', 12.519429, -57.113212, 1.59, 1.6)
('Kraz', 12.573121, -23.396759, 2.65, 0.893)
('Porrima', 12.694345, -1.449375, 2.74, 0.368)
('Becrux', 12.795359, -59.688764, 1.25, -0.238)
('Alioth', 12.900472, 55.959821, 1.76, -0.022)
('Cor Caroli', 12.933807, 38.31838, 2.89, -0.115)
('Vindemiatrix', 13.036278, 10.95915, 2.85, 0.934)
('Mizar', 13.398747, 54.925362, 2.23, 0.057)
('Spica', 13.419883, -11.161322, 0.98, -0.235)
('Alcor', 13.420413, 54.987958, 3.99, 0.169)
('Alkaid', 13.792354, 49.313265, 1.85, -0.099)
('Mufrid', 13.911411, 18.397717, 2.68, 0.58)
('Hadar', 14.063729, -60.373039, 0.61, -0.231)
('Thuban', 14.073165, 64.37585, 3.67, -0.049)
('Menkent', 14.111395, -36.369954, 2.06, 1.011)
('Arcturus', 14.26103, 19.18241, -0.05, 1.239)
('Proxima Centauri', 14.495985, -62.679485, 11.01, 1.807)
('Rigil Kentaurus', 14.660765, -60.833976, -0.01, 0.71)
('Izar', 14.749784, 27.074222, 2.35, 0.966)
('Kochab', 14.845105, 74.155505, 2.07, 1.465)
('Zubenelgenubi', 14.847977, -16.041778, 2.75, 0.147)
('Zubeneschemali', 15.283449, -9.382917, 2.61, -0.071)
('Alphekka', 15.578128, 26.714693, 2.22, 0.032)
('Unukalhai', 15.737798, 6.425627, 2.63, 1.167)
('Dschubba', 16.005557, -22.62171, 2.29, -0.117)
('Graffias', 16.09062, -19.805453, 2.56, -0.065)
('Antares', 16.490128, -26.432002, 1.06, 1.865)
('Kornephoros', 16.503668, 21.489613, 2.78, 0.947)
('Atria', 16.811077, -69.027715, 1.91, 1.447)
('Rasalgethi', 17.244127, 14.390333, 2.78, 1.164)
('Rastaban', 17.507213, 52.301387, 2.79, 0.954)
('Shaula', 17.560145, -37.103821, 1.62, -0.231)
('Rasalhague', 17.582241, 12.560035, 2.08, 0.155)
('Sargas', 17.62198, -42.997824, 1.86, 0.406)
('Cebalrai', 17.724543, 4.567303, 2.76, 1.168)
('Etamin', 17.943437, 51.488895, 2.24, 1.521)
("Barnard's Star", 17.963472, 4.693388, 9.54, 1.57)
('Nash', 18.096803, -30.424091, 2.98, 0.981)
('Kaus Meridionalis', 18.3499, -29.828103, 2.72, 1.38)
('Kaus Australis', 18.402868, -34.384616, 1.79, -0.031)
('Kaus Borealis', 18.466179, -25.4217, 2.82, 1.025)
('Vega', 18.61564, 38.783692, 0.03, -0.001)
('Sheliak', 18.834665, 33.362667, 3.52, 0.003)
('Nunki', 18.92109, -26.296722, 2.05, -0.134)
('Albaldah', 19.162731, -21.023615, 2.88, 0.377)
('Albireo', 19.512023, 27.959681, 3.05, 1.088)
("Campbell's Hydrogen Star", 19.579231, 30.516371, 10.0, -0.013)
('Tarazed', 19.770994, 10.613261, 2.72, 1.507)
('Altair', 19.846388, 8.868322, 0.76, 0.221)
('Alshain', 19.921887, 6.406763, 3.71, 0.855)
('Cygnus X-1', 19.972688, 35.201604, 8.84, 0.73)
('Sadr', 20.370473, 40.256679, 2.23, 0.673)
('Peacock', 20.427459, -56.73509, 1.94, -0.118)
('Deneb', 20.690532, 45.280338, 1.25, 0.092)
('Gienah', 20.770178, 33.970256, 2.48, 1.021)
('Lacaille 8760', 21.287725, -38.867362, 6.69, 1.397)
('Alderamin', 21.30963, 62.585573, 2.45, 0.257)
('Sadalsuud', 21.525982, -5.571172, 2.9, 0.828)
('Enif', 21.736433, 9.875011, 2.38, 1.52)
('Sadalmelik', 22.096399, -0.319851, 2.95, 0.969)
('Alnair', 22.137209, -46.960975, 1.73, -0.07)
('Kruger 60', 22.466642, 57.695875, 9.59, 1.613)
('Matar', 22.716704, 30.221245, 2.93, 0.852)
("Babcock's star", 22.735418, 55.589226, 8.83, 0.031)
('Fomalhaut', 22.960838, -29.622236, 1.17, 0.145)
('Scheat', 23.062901, 28.082789, 2.44, 1.655)
('Markab', 23.079348, 15.205264, 2.49, -0.002)
('Lacaille 9352', 23.097531, -35.853073, 7.35, 1.483)
('p Eridani', 1.663003, -56.19462, 5.8, 0.86)

The right way to do this would be to make a VIEW, a virtual table that always shows the results of another query (without storing anything).