A document from MCS 275 Spring 2021, instructor David Dumas. You can also get the notebook file.

SQLite demo notebook

MCS 275 Spring 2021 - David Dumas

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

In [7]:
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
Out[7]:
'./hyg_data.sqlite'
In [8]:
os.path.exists(DBFILE)
Out[8]:
True
In [10]:
import sqlite3
con = sqlite3.connect(DBFILE)
In [19]:
c = con.execute("SELECT proper,ra,dec,mag FROM stars LIMIT 10")
c.fetchone()
Out[19]:
('Sol', 0.0, 0.0, -26.7)
In [20]:
c = con.execute("SELECT proper,ra,dec,mag FROM stars LIMIT 10")
c.fetchall()
Out[20]:
[('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

In [24]:
import sqlite3
con = sqlite3.connect(DBFILE)
con.row_factory = sqlite3.Row
# Roughly equivalent to using csv.DictReader instead of csv.reader
In [28]:
c = con.execute("SELECT proper,ra,dec,mag FROM stars LIMIT 10")
r = c.fetchone()
In [33]:
r["proper"]  # sqlite3.Row[columnname] gives you the value in a column
Out[33]:
'Sol'
In [35]:
r[0]  # column 0 in the order of the query
Out[35]:
'Sol'
In [31]:
r.keys()
Out[31]:
['proper', 'ra', 'dec', 'mag']
In [32]:
print(r)

<sqlite3.Row object at 0x7f02a9782f10>

Brightest stars

In [36]:
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

In [39]:
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

In [41]:
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

In [44]:
import sqlite3
con = sqlite3.connect("stars2.sqlite")
con.row_factory = sqlite3.Row
In [45]:
con.execute("""CREATE TABLE named_stars_simple (
  proper TEXT,
  ra REAL,
  dec REAL,
  mag REAL,
  ci REAL
);""")
Out[45]:
<sqlite3.Cursor at 0x7f02a91476c0>
In [47]:
con.execute("""CREATE TABLE IF NOT EXISTS named_stars_simple (
  proper TEXT,
  ra REAL,
  dec REAL,
  mag REAL,
  ci REAL
);""")
Out[47]:
<sqlite3.Cursor at 0x7f02a9147b90>
In [49]:
c = con.execute("SELECT * FROM named_stars_simple;")
for r in c:
    print(tuple(r))
In [50]:
c = con.execute("""
INSERT INTO named_stars_simple (proper,ci)
VALUES ("Sun",0.6);
""")
In [51]:
c = con.execute("SELECT * FROM named_stars_simple;")
for r in c:
    print(tuple(r))

('Sun', None, None, None, 0.6)
In [52]:
c = con.execute("""
UPDATE named_stars_simple SET mag=-26 WHERE proper="Sun";
""")
In [53]:
c = con.execute("SELECT * FROM named_stars_simple;")
for r in c:
    print(tuple(r))

('Sun', None, None, -26.0, 0.6)
In [54]:
c = con.execute("""DELETE FROM named_stars_simple WHERE proper="Sun";""")
In [55]:
c = con.execute("SELECT * FROM named_stars_simple;")
for r in c:
    print(tuple(r))
In [58]:
c = con.execute("""
INSERT INTO named_stars_simple
SELECT proper,ra,dec,mag,ci FROM stars WHERE proper IS NOT NULL;
""")
In [59]:
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).

In [ ]: