Python - SQLite - Databases: Getting Organized - 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(554/682)の解答を求めてみる。
Jupyter(コード、入出力結果)
PopulateTables.ipynb
import os
import DBcm
db_details = 'CoachDB.sqlite3'
FOLDER = 'swimdata'
files = os.listdir(FOLDER)
files.remove('.DS_Store')
SQL_INSERT = '''
insert into swimmers
(name, age)
values
(?, ?)
'''
files[0]
'Hannah-13-100m-Free.txt'
with DBcm.UseDatabase(db_details) as db:
for filename in files:
name, age, *_ = filename.removesuffix('.txt').split('-')
db.execute(SQL_INSERT, (name, age))
with DBcm.UseDatabase(db_details) as db:
db.execute('''select * from swimmers''')
results = db.fetchall()
results
[(1, 'Hannah', 13),
(2, 'Darius', 13),
(3, 'Owen', 15),
(4, 'Mike', 15),
(5, 'Hannah', 13),
(6, 'Mike', 15),
(7, 'Mike', 15),
(8, 'Abi', 10),
(9, 'Ruth', 13),
(10, 'Tasmin', 15),
(11, 'Erika', 15),
(12, 'Ruth', 13),
(13, 'Abi', 10),
(14, 'Maria', 9),
(15, 'Elba', 14),
(16, 'Tasmin', 15),
(17, 'Abi', 10),
(18, 'Abi', 10),
(19, 'Mike', 15),
(20, 'Ali', 12),
(21, 'Ruth', 13),
(22, 'Chris', 17),
(23, 'Ali', 12),
(24, 'Darius', 13),
(25, 'Ruth', 13),
(26, 'Aurora', 13),
(27, 'Katie', 9),
(28, 'Alison', 14),
(29, 'Ruth', 13),
(30, 'Emma', 13),
(31, 'Calvin', 9),
(32, 'Darius', 13),
(33, 'Mike', 15),
(34, 'Emma', 13),
(35, 'Tasmin', 15),
(36, 'Blake', 15),
(37, 'Abi', 10),
(38, 'Chris', 17),
(39, 'Blake', 15),
(40, 'Bill', 18),
(41, 'Darius', 13),
(42, 'Dave', 17),
(43, 'Alison', 14),
(44, 'Lizzie', 14),
(45, 'Katie', 9),
(46, 'Katie', 9),
(47, 'Katie', 9),
(48, 'Lizzie', 14),
(49, 'Tasmin', 15),
(50, 'Katie', 9),
(51, 'Dave', 17),
(52, 'Erika', 15),
(53, 'Calvin', 9),
(54, 'Calvin', 9),
(55, 'Carl', 15),
(56, 'Bill', 18),
(57, 'Katie', 9),
(58, 'Blake', 15),
(59, 'Erika', 15),
(60, 'Katie', 9)]