計算機科学のブログ

ほしい物リスト

Python - SQLite - Databases: Getting Organized - select, insert

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(コード、入出力結果)

webapp/CreateDatabaseTables.ipynb

import os
import DBcm

db_details = 'CoachDB.sqlite'

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,))
        results = db.fetchall()
        if not results:
            print(f'Inserting: {name}, {age}')
            db.execute(SQL_INSERT, (name, age,))

Inserting: Hannah, 13
Inserting: Darius, 13
Inserting: Owen, 15
Inserting: Mike, 15
Inserting: Abi, 10
Inserting: Ruth, 13
Inserting: Tasmin, 15
Inserting: Erika, 15
Inserting: Maria, 9
Inserting: Elba, 14
Inserting: Ali, 12
Inserting: Chris, 17
Inserting: Aurora, 13
Inserting: Katie, 9
Inserting: Alison, 14
Inserting: Emma, 13
Inserting: Calvin, 9
Inserting: Blake, 15
Inserting: Bill, 18
Inserting: Dave, 17
Inserting: Lizzie, 14
Inserting: Carl, 15

with DBcm.UseDatabase(db_details) as db:
    for filename in files:
        name, age, *_ = filename.removesuffix('.txt').split('-')
        db.execute(SQL_SELECT, (name, age,))
        results = db.fetchall()
        if not results:
            print(f'Inserting: {name}, {age}')
            db.execute(SQL_INSERT, (name, age,))