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