計算機科学のブログ

ほしい物リスト

Python - SQLite - Databases: Getting Organized - select, insert, fetchall 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(557/682)の解答を求めてみる。

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

PopulateTables.ipynb

import os
import DBcm

db_details = 'CoachDB.sqlite3'

FOLDER = 'swimdata'
files = os.listdir(FOLDER)
files.remove('.DS_Store')

SQL_SELECT = '''
select * from swimmers
where name = ? and age = ?
'''
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_SELECT, (name, age,))
        if not db.fetchall():
            db.execute(SQL_INSERT, (name, age,))