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