Python - SQL - SQLite - Databases: Getting Organized - select, insert, fetchone method
Head First Python: A Learner’s Guide to the Fundamentals of Python Programming, A Brain-Friendly Guide、 Paul Barry(著)、 O’Reilly Mediaの Chapter 12.(Databases: Getting Organized)、EXERCISE(564/682)の解答を求めてみる。
Jupyter(コード、入出力結果)
webapp/PopulateTables.ipynb
with DBcm.UseDatabase(db_details) as db:
for file in files:
name, age, distance, stroke = file.removesuffix('.txt').split('-')
db.execute(sql_get_swimmer, (name, age,))
swimmer_id = db.fetchone()[0]
db.execute(sql_get_event, (distance, stroke, ))
event_id = db.fetchone()[0]
with open(FOLDER + file) as f:
times = f.read().strip().split(',')
for time in times:
db.execute(sql_insert, (swimmer_id, event_id, time),)
with DBcm.UseDatabase(db_details) as db:
db.execute('select * from times')
for row in db.fetchall():
print(row)
(1, 2, '1:21.43', '2025-05-15 04:30:50')
(1, 2, '1:21.40', '2025-05-15 04:30:50')
(1, 2, '1:21.62', '2025-05-15 04:30:50')
(1, 2, '1:25.38', '2025-05-15 04:30:50')
(2, 3, '1:22.57', '2025-05-15 04:30:50')
(2, 3, '1:29.64', '2025-05-15 04:30:50')
(2, 3, '1:20.39', '2025-05-15 04:30:50')
(2, 3, '1:23.83', '2025-05-15 04:30:50')
(3, 2, '1:15.57', '2025-05-15 04:30:50')
(3, 2, '1:14.40', '2025-05-15 04:30:50')
(3, 2, '1:19.82', '2025-05-15 04:30:50')
(3, 2, '1:12.90', '2025-05-15 04:30:50')
(4, 2, '1:02.52', '2025-05-15 04:30:50')
(4, 2, '1:02.56', '2025-05-15 04:30:50')
(4, 2, '1:06.41', '2025-05-15 04:30:50')
(4, 2, '1:07.08', '2025-05-15 04:30:50')
(4, 2, '1:02.56', '2025-05-15 04:30:50')
(4, 2, '1:03.23', '2025-05-15 04:30:50')
(4, 2, '1:04.54', '2025-05-15 04:30:50')
(1, 3, '1:35.75', '2025-05-15 04:30:50')
(1, 3, '1:32.78', '2025-05-15 04:30:50')
(1, 3, '1:34.01', '2025-05-15 04:30:50')
(1, 3, '1:32.57', '2025-05-15 04:30:50')
(4, 3, '1:19.23', '2025-05-15 04:30:50')
(4, 3, '1:13.70', '2025-05-15 04:30:50')
(4, 3, '1:13.96', '2025-05-15 04:30:50')
(4, 3, '1:15.94', '2025-05-15 04:30:50')
(4, 4, '1:14.55', '2025-05-15 04:30:50')
(4, 4, '1:14.15', '2025-05-15 04:30:50')
(4, 4, '1:08.38', '2025-05-15 04:30:50')
(4, 4, '1:08.74', '2025-05-15 04:30:50')
(4, 4, '1:10.89', '2025-05-15 04:30:50')
(4, 4, '1:09.47', '2025-05-15 04:30:50')
(4, 4, '1:09.52', '2025-05-15 04:30:50')
(4, 4, '1:09.86', '2025-05-15 04:30:50')
(4, 4, '1:09.83', '2025-05-15 04:30:50')
(8, 5, '41.50', '2025-05-15 04:30:50')
(8, 5, '43.58', '2025-05-15 04:30:50')
(8, 5, '42.35', '2025-05-15 04:30:50')
(8, 5, '43.35', '2025-05-15 04:30:50')
(8, 5, '39.85', '2025-05-15 04:30:50')
(8, 5, '40.53', '2025-05-15 04:30:50')
(8, 5, '42.14', '2025-05-15 04:30:50')
(8, 5, '39.18', '2025-05-15 04:30:50')
(8, 5, '40.89', '2025-05-15 04:30:50')
(8, 5, '40.89', '2025-05-15 04:30:50')
(9, 6, '2:22.64', '2025-05-15 04:30:50')
(9, 6, '2:28.16', '2025-05-15 04:30:50')
(9, 6, '2:28.38', '2025-05-15 04:30:50')
(9, 6, '2:25.08', '2025-05-15 04:30:50')
(9, 6, '2:25.64', '2025-05-15 04:30:50')
(9, 6, '2:33.58', '2025-05-15 04:30:50')
(10, 3, '1:19.63', '2025-05-15 04:30:50')
(10, 3, '1:16.69', '2025-05-15 04:30:50')
(10, 3, '1:19.68', '2025-05-15 04:30:50')
(10, 3, '1:16.56', '2025-05-15 04:30:50')
(11, 2, '1:19.04', '2025-05-15 04:30:50')
(11, 2, '1:15.88', '2025-05-15 04:30:50')
(11, 2, '1:18.62', '2025-05-15 04:30:50')
(11, 2, '1:21.74', '2025-05-15 04:30:50')
(9, 7, '2:56.68', '2025-05-15 04:30:50')
(9, 7, '2:42.63', '2025-05-15 04:30:50')
(9, 7, '2:40.90', '2025-05-15 04:30:50')
(9, 7, '2:42.88', '2025-05-15 04:30:50')
(9, 7, '2:46.09', '2025-05-15 04:30:50')
(8, 8, '39.07', '2025-05-15 04:30:50')
(8, 8, '37.66', '2025-05-15 04:30:50')
(8, 8, '36.13', '2025-05-15 04:30:50')
(8, 8, '39.42', '2025-05-15 04:30:50')
(14, 8, '45.75', '2025-05-15 04:30:50')
(14, 8, '41.62', '2025-05-15 04:30:50')
(14, 8, '40.49', '2025-05-15 04:30:50')
(14, 8, '41.00', '2025-05-15 04:30:50')
(15, 2, '1:06.01', '2025-05-15 04:30:50')
(15, 2, '1:06.93', '2025-05-15 04:30:50')
(15, 2, '1:05.34', '2025-05-15 04:30:50')
(15, 2, '1:06.10', '2025-05-15 04:30:50')
(10, 2, '1:08.87', '2025-05-15 04:30:50')
(10, 2, '1:07.34', '2025-05-15 04:30:50')
(10, 2, '1:11.00', '2025-05-15 04:30:50')
(10, 2, '1:08.62', '2025-05-15 04:30:50')
(10, 2, '1:10.94', '2025-05-15 04:30:50')
(10, 2, '1:09.52', '2025-05-15 04:30:50')
(10, 2, '1:12.04', '2025-05-15 04:30:50')
(10, 2, '1:10.91', '2025-05-15 04:30:50')
(8, 3, '1:31.59', '2025-05-15 04:30:50')
(8, 3, '1:26.55', '2025-05-15 04:30:50')
(8, 3, '1:28.75', '2025-05-15 04:30:50')
(8, 3, '1:39.79', '2025-05-15 04:30:50')
(8, 3, '1:32.37', '2025-05-15 04:30:50')
(8, 9, '48.08', '2025-05-15 04:30:50')
(8, 9, '53.17', '2025-05-15 04:30:50')
(8, 9, '45.76', '2025-05-15 04:30:50')
(8, 9, '49.28', '2025-05-15 04:30:50')
(8, 9, '52.85', '2025-05-15 04:30:50')
(8, 9, '46.19', '2025-05-15 04:30:50')
(8, 9, '47.76', '2025-05-15 04:30:50')
(8, 9, '48.83', '2025-05-15 04:30:50')
(8, 9, '45.71', '2025-05-15 04:30:50')
(8, 9, '47.76', '2025-05-15 04:30:50')
(4, 10, '2:48.05', '2025-05-15 04:30:50')
(4, 10, '2:44.03', '2025-05-15 04:30:50')
(4, 10, '2:38.93', '2025-05-15 04:30:50')
(4, 10, '2:54.86', '2025-05-15 04:30:50')
(20, 3, '1:36.54', '2025-05-15 04:30:50')
(20, 3, '1:39.10', '2025-05-15 04:30:50')
(20, 3, '1:39.45', '2025-05-15 04:30:50')
(20, 3, '1:42.87', '2025-05-15 04:30:50')
(9, 3, '1:17.16', '2025-05-15 04:30:50')
(9, 3, '1:16.65', '2025-05-15 04:30:50')
(9, 3, '1:23.11', '2025-05-15 04:30:50')
(9, 3, '1:14.37', '2025-05-15 04:30:50')
(9, 3, '1:18.16', '2025-05-15 04:30:50')
(9, 3, '1:18.28', '2025-05-15 04:30:50')
(9, 3, '1:16.87', '2025-05-15 04:30:50')
(9, 3, '1:17.37', '2025-05-15 04:30:50')
(9, 3, '1:20.46', '2025-05-15 04:30:50')
(22, 3, '1:21.76', '2025-05-15 04:30:50')
(22, 3, '1:23.32', '2025-05-15 04:30:50')
(22, 3, '1:24.09', '2025-05-15 04:30:50')
(22, 3, '1:21.51', '2025-05-15 04:30:50')
(20, 2, '1:27.31', '2025-05-15 04:30:50')
(20, 2, '1:27.00', '2025-05-15 04:30:50')
(20, 2, '1:27.46', '2025-05-15 04:30:50')
(20, 2, '1:22.50', '2025-05-15 04:30:50')
(20, 2, '1:20.39', '2025-05-15 04:30:50')
(20, 2, '1:26.04', '2025-05-15 04:30:50')
(20, 2, '1:20.22', '2025-05-15 04:30:50')
(2, 11, '1:30.24', '2025-05-15 04:30:50')
(2, 11, '1:33.18', '2025-05-15 04:30:50')
(2, 11, '1:32.73', '2025-05-15 04:30:50')
(2, 11, '1:33.05', '2025-05-15 04:30:50')
(2, 11, '1:30.81', '2025-05-15 04:30:50')
(2, 11, '1:31.74', '2025-05-15 04:30:50')
(2, 11, '1:31.53', '2025-05-15 04:30:50')
(2, 11, '1:33.05', '2025-05-15 04:30:50')
(9, 2, '1:07.99', '2025-05-15 04:30:50')
(9, 2, '1:11.87', '2025-05-15 04:30:50')
(9, 2, '1:11.08', '2025-05-15 04:30:50')
(9, 2, '1:09.75', '2025-05-15 04:30:50')
(26, 8, '30.05', '2025-05-15 04:30:50')
(26, 8, '30.21', '2025-05-15 04:30:50')
(26, 8, '28.86', '2025-05-15 04:30:50')
(26, 8, '30.65', '2025-05-15 04:30:50')
(26, 8, '30.03', '2025-05-15 04:30:50')
(26, 8, '29.64', '2025-05-15 04:30:50')
(26, 8, '29.82', '2025-05-15 04:30:50')
(27, 11, '1:50.10', '2025-05-15 04:30:50')
(27, 11, '1:47.12', '2025-05-15 04:30:50')
(27, 11, '1:54.65', '2025-05-15 04:30:50')
(27, 11, '1:51.03', '2025-05-15 04:30:50')
(28, 11, '1:34.71', '2025-05-15 04:30:50')
(28, 11, '1:37.87', '2025-05-15 04:30:50')
(28, 11, '1:35.15', '2025-05-15 04:30:50')
(28, 11, '1:37.20', '2025-05-15 04:30:50')
(28, 11, '1:36.29', '2025-05-15 04:30:50')
(28, 11, '1:36.00', '2025-05-15 04:30:50')
(28, 11, '1:35.63', '2025-05-15 04:30:50')
(28, 11, '1:35.42', '2025-05-15 04:30:50')
(28, 11, '1:42.01', '2025-05-15 04:30:50')
(9, 12, '5:11.32', '2025-05-15 04:30:50')
(9, 12, '5:04.12', '2025-05-15 04:30:50')
(9, 12, '5:20.67', '2025-05-15 04:30:50')
(9, 12, '5:08.31', '2025-05-15 04:30:50')
(9, 12, '5:04.32', '2025-05-15 04:30:50')
(9, 12, '5:06.77', '2025-05-15 04:30:50')
(9, 12, '5:13.01', '2025-05-15 04:30:50')
(30, 2, '1:32.21', '2025-05-15 04:30:50')
(30, 2, '1:33.68', '2025-05-15 04:30:50')
(30, 2, '1:34.10', '2025-05-15 04:30:50')
(30, 2, '1:28.57', '2025-05-15 04:30:50')
(31, 13, '39.47', '2025-05-15 04:30:50')
(31, 13, '37.30', '2025-05-15 04:30:50')
(31, 13, '41.90', '2025-05-15 04:30:50')
(31, 13, '42.85', '2025-05-15 04:30:50')
(31, 13, '39.90', '2025-05-15 04:30:50')
(31, 13, '38.66', '2025-05-15 04:30:50')
(31, 13, '38.84', '2025-05-15 04:30:50')
(31, 13, '37.65', '2025-05-15 04:30:50')
(31, 13, '39.68', '2025-05-15 04:30:50')
(31, 13, '37.18', '2025-05-15 04:30:50')
(31, 13, '43.61', '2025-05-15 04:30:50')
(2, 4, '1:27.95', '2025-05-15 04:30:50')
(2, 4, '1:21.07', '2025-05-15 04:30:50')
(2, 4, '1:30.96', '2025-05-15 04:30:50')
(2, 4, '1:23.22', '2025-05-15 04:30:50')
(2, 4, '1:27.95', '2025-05-15 04:30:50')
(2, 4, '1:28.30', '2025-05-15 04:30:50')
(4, 6, '2:19.77', '2025-05-15 04:30:50')
(4, 6, '2:41.57', '2025-05-15 04:30:50')
(4, 6, '2:27.88', '2025-05-15 04:30:50')
(4, 6, '2:37.28', '2025-05-15 04:30:50')
(30, 11, '2:01.15', '2025-05-15 04:30:50')
(30, 11, '1:57.86', '2025-05-15 04:30:50')
(30, 11, '1:59.38', '2025-05-15 04:30:50')
(30, 11, '1:57.94', '2025-05-15 04:30:50')
(30, 11, '1:56.97', '2025-05-15 04:30:50')
(10, 11, '1:20.59', '2025-05-15 04:30:50')
(10, 11, '1:26.48', '2025-05-15 04:30:50')
(10, 11, '1:24.15', '2025-05-15 04:30:50')
(10, 11, '1:21.96', '2025-05-15 04:30:50')
(36, 2, '1:02.13', '2025-05-15 04:30:50')
(36, 2, '1:03.25', '2025-05-15 04:30:50')
(36, 2, '1:04.16', '2025-05-15 04:30:50')
(36, 2, '1:03.17', '2025-05-15 04:30:50')
(36, 2, '1:04.84', '2025-05-15 04:30:50')
(36, 2, '1:03.88', '2025-05-15 04:30:50')
(36, 2, '1:04.87', '2025-05-15 04:30:50')
(8, 11, '1:42.97', '2025-05-15 04:30:50')
(8, 11, '1:43.31', '2025-05-15 04:30:50')
(8, 11, '1:43.50', '2025-05-15 04:30:50')
(8, 11, '1:40.34', '2025-05-15 04:30:50')
(22, 11, '1:28.86', '2025-05-15 04:30:50')
(22, 11, '1:29.90', '2025-05-15 04:30:50')
(22, 11, '1:28.12', '2025-05-15 04:30:50')
(22, 11, '1:28.67', '2025-05-15 04:30:50')
(36, 3, '1:12.71', '2025-05-15 04:30:50')
(36, 3, '1:19.87', '2025-05-15 04:30:50')
(36, 3, '1:14.94', '2025-05-15 04:30:50')
(36, 3, '1:11.53', '2025-05-15 04:30:50')
(36, 3, '1:14.94', '2025-05-15 04:30:50')
(40, 7, '2:29.06', '2025-05-15 04:30:50')
(40, 7, '2:21.49', '2025-05-15 04:30:50')
(40, 7, '2:24.13', '2025-05-15 04:30:50')
(40, 7, '2:29.40', '2025-05-15 04:30:50')
(2, 10, '3:03.84', '2025-05-15 04:30:50')
(2, 10, '3:04.27', '2025-05-15 04:30:50')
(2, 10, '3:01.49', '2025-05-15 04:30:50')
(2, 10, '3:04.27', '2025-05-15 04:30:50')
(2, 10, '2:52.44', '2025-05-15 04:30:50')
(42, 2, '59.21', '2025-05-15 04:30:50')
(42, 2, '59.27', '2025-05-15 04:30:50')
(42, 2, '58.67', '2025-05-15 04:30:50')
(42, 2, '59.72', '2025-05-15 04:30:50')
(42, 2, '59.17', '2025-05-15 04:30:50')
(28, 2, '1:13.86', '2025-05-15 04:30:50')
(28, 2, '1:12.00', '2025-05-15 04:30:50')
(28, 2, '1:14.69', '2025-05-15 04:30:50')
(28, 2, '1:10.01', '2025-05-15 04:30:50')
(28, 2, '1:12.24', '2025-05-15 04:30:50')
(28, 2, '1:11.00', '2025-05-15 04:30:50')
(28, 2, '1:11.20', '2025-05-15 04:30:50')
(28, 2, '1:12.28', '2025-05-15 04:30:50')
(28, 2, '1:15.46', '2025-05-15 04:30:50')
(28, 2, '1:16.03', '2025-05-15 04:30:50')
(44, 2, '1:21.08', '2025-05-15 04:30:50')
(44, 2, '1:11.23', '2025-05-15 04:30:50')
(44, 2, '1:12.46', '2025-05-15 04:30:50')
(44, 2, '1:16.73', '2025-05-15 04:30:50')
(27, 13, '40.11', '2025-05-15 04:30:50')
(27, 13, '45.43', '2025-05-15 04:30:50')
(27, 13, '50.17', '2025-05-15 04:30:50')
(27, 13, '43.58', '2025-05-15 04:30:50')
(27, 13, '52.61', '2025-05-15 04:30:50')
(27, 9, '47.44', '2025-05-15 04:30:50')
(27, 9, '52.32', '2025-05-15 04:30:50')
(27, 9, '57.16', '2025-05-15 04:30:50')
(27, 9, '56.77', '2025-05-15 04:30:50')
(27, 5, '43.25', '2025-05-15 04:30:50')
(27, 5, '47.35', '2025-05-15 04:30:50')
(27, 5, '46.12', '2025-05-15 04:30:50')
(27, 5, '45.69', '2025-05-15 04:30:50')
(44, 3, '1:27.10', '2025-05-15 04:30:50')
(44, 3, '1:26.21', '2025-05-15 04:30:50')
(44, 3, '1:26.42', '2025-05-15 04:30:50')
(44, 3, '1:35.79', '2025-05-15 04:30:50')
(44, 3, '1:30.47', '2025-05-15 04:30:50')
(10, 14, '2:54.14', '2025-05-15 04:30:50')
(10, 14, '3:03.58', '2025-05-15 04:30:50')
(10, 14, '3:02.43', '2025-05-15 04:30:50')
(10, 14, '2:57.99', '2025-05-15 04:30:50')
(27, 8, '38.94', '2025-05-15 04:30:50')
(27, 8, '37.76', '2025-05-15 04:30:50')
(27, 8, '41.83', '2025-05-15 04:30:50')
(27, 8, '42.81', '2025-05-15 04:30:50')
(42, 7, '2:35.46', '2025-05-15 04:30:50')
(42, 7, '2:28.30', '2025-05-15 04:30:50')
(42, 7, '2:35.35', '2025-05-15 04:30:50')
(42, 7, '2:28.84', '2025-05-15 04:30:50')
(11, 14, '3:27.51', '2025-05-15 04:30:50')
(11, 14, '3:34.70', '2025-05-15 04:30:50')
(11, 14, '3:27.15', '2025-05-15 04:30:50')
(11, 14, '3:28.45', '2025-05-15 04:30:50')
(11, 14, '3:34.79', '2025-05-15 04:30:50')
(11, 14, '3:30.21', '2025-05-15 04:30:50')
(11, 14, '3:33.74', '2025-05-15 04:30:50')
(31, 5, '38.79', '2025-05-15 04:30:50')
(31, 5, '39.03', '2025-05-15 04:30:50')
(31, 5, '41.29', '2025-05-15 04:30:50')
(31, 5, '39.66', '2025-05-15 04:30:50')
(31, 5, '41.13', '2025-05-15 04:30:50')
(31, 5, '38.57', '2025-05-15 04:30:50')
(31, 5, '39.40', '2025-05-15 04:30:50')
(31, 5, '41.06', '2025-05-15 04:30:50')
(31, 5, '40.37', '2025-05-15 04:30:50')
(31, 5, '43.05', '2025-05-15 04:30:50')
(31, 5, '37.85', '2025-05-15 04:30:50')
(31, 8, '35.78', '2025-05-15 04:30:50')
(31, 8, '36.19', '2025-05-15 04:30:50')
(31, 8, '36.89', '2025-05-15 04:30:50')
(31, 8, '34.29', '2025-05-15 04:30:50')
(31, 8, '34.18', '2025-05-15 04:30:50')
(31, 8, '35.94', '2025-05-15 04:30:50')
(55, 3, '1:29.78', '2025-05-15 04:30:50')
(55, 3, '1:28.38', '2025-05-15 04:30:50')
(55, 3, '1:25.03', '2025-05-15 04:30:50')
(55, 3, '1:25.66', '2025-05-15 04:30:50')
(40, 3, '1:11.34', '2025-05-15 04:30:50')
(40, 3, '1:05.81', '2025-05-15 04:30:50')
(40, 3, '1:05.75', '2025-05-15 04:30:50')
(40, 3, '1:05.82', '2025-05-15 04:30:50')
(27, 2, '1:20.81', '2025-05-15 04:30:50')
(27, 2, '1:23.61', '2025-05-15 04:30:50')
(27, 2, '1:24.80', '2025-05-15 04:30:50')
(27, 2, '1:22.05', '2025-05-15 04:30:50')
(27, 2, '1:22.58', '2025-05-15 04:30:50')
(27, 2, '1:23.94', '2025-05-15 04:30:50')
(36, 4, '1:11.49', '2025-05-15 04:30:50')
(36, 4, '1:10.16', '2025-05-15 04:30:50')
(36, 4, '1:12.59', '2025-05-15 04:30:50')
(36, 4, '1:11.63', '2025-05-15 04:30:50')
(36, 4, '1:09.43', '2025-05-15 04:30:50')
(11, 11, '1:36.72', '2025-05-15 04:30:50')
(11, 11, '1:34.78', '2025-05-15 04:30:50')
(11, 11, '1:34.05', '2025-05-15 04:30:50')
(11, 11, '1:32.78', '2025-05-15 04:30:50')
(11, 11, '1:33.51', '2025-05-15 04:30:50')
(11, 11, '1:33.29', '2025-05-15 04:30:50')
(11, 11, '1:36.47', '2025-05-15 04:30:50')
(11, 11, '1:31.82', '2025-05-15 04:30:50')
(27, 3, '1:33.36', '2025-05-15 04:30:50')
(27, 3, '1:31.52', '2025-05-15 04:30:50')
(27, 3, '1:28.10', '2025-05-15 04:30:50')
(27, 3, '1:33.37', '2025-05-15 04:30:50')
(27, 3, '1:32.89', '2025-05-15 04:30:50')
(27, 3, '1:33.38', '2025-05-15 04:30:50')
(27, 3, '1:32.03', '2025-05-15 04:30:50')
(27, 3, '1:32.71', '2025-05-15 04:30:50')