計算機科学のブログ

ほしい物リスト

Python - SQL - SQLite - Databases: Getting Organized - events table

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)、SHARPEN YOUR PENCIL(560/682)の解答を求めてみる。

Jupyter(コード、入出力結果)

webapp/PopulateTables.ipynb

import os
import DBcm

FOLDER = 'swimdata/'
files = os.listdir(FOLDER)

files.remove('.DS_Store')

db_details = 'CoachDB.sqlit3'

SQL_SELECT = '''
select * from events
where distance = ? and stroke = ?
'''

SQL_INSERT = '''
insert into events (distance, stroke)
values (?, ?)
'''

with DBcm.UseDatabase(db_details) as db:
    for file in files:
        _, _, distance, stroke = file.removesuffix('.txt').split('-')
        db.execute(SQL_SELECT, (distance, stroke,))
        if db.fetchall():
            continue
        db.execute(SQL_INSERT, (distance, stroke,))

SQL = '''
select * from events
'''
with DBcm.UseDatabase(db_details) as db:
    db.execute(SQL)
    results = db.fetchall()
results

[(2, '100m', 'Free'),
 (3, '100m', 'Back'),
 (4, '100m', 'Fly'),
 (5, '50m', 'Back'),
 (6, '200m', 'Free'),
 (7, '200m', 'Back'),
 (8, '50m', 'Free'),
 (9, '50m', 'Breast'),
 (10, '200m', 'IM'),
 (11, '100m', 'Breast'),
 (12, '400m', 'Free'),
 (13, '50m', 'Fly'),
 (14, '200m', 'Breast')]