Python - Manage Your data: Handling input(sql, database, tables, insert, select)

Head First Python
( O'Reilly Media)
Paul Barry (著)

開発環境

• OS X Mavericks - Apple(OS)
• Emacs (CUI)、BBEdit - Bare Bones Software, Inc. (GUI) (Text Editor)
• Python (プログラミング言語)

Head First Python (Paul Barry(著)、 O'Reilly Media )のChapter 9(Manage Your data: Handling input)、SHARPEN YOUR PENCIL(p.323)を解いてみる。

SHARPEN YOUR PENCIL(p.323)

コード(BBEdit, Emacs)

cgi-gin/initDBathletes.py

#!/usr/bin/env python3
#-*- coding: utf-8 -*-

import sqlite3
import glob
import athletemodel

data_files = glob.glob('../data/*.txt')
athletes = athletemodel.put_to_store(data_files)
connection = sqlite3.connect('coachdata.sqlite')
cursor = connection.cursor()

for each_ath in athletes:
    name = athletes[each_ath].name
    dob = athletes[each_ath].dob

    cursor.execute('INSERT INTO athletes (name, dob) VALUES (?, ?)',
                    (name, dob))
    cursor.execute('SELECT id FROM athletes WHERE name=? AND dob=?',
                   (name, dob))
    the_current_id = cursor.fetchone()[0]
    for t in athletes[each_ath].clean_data:
        cursor.execute(
            'INSERT INTO timing_data (athlete_id, value) VALUES(?, ?)',
            (the_current_id, t))

connection.commit()
connection.close()

入出力結果(Terminal, IPython)

$ ./initDBathletes.py
$ ipython
Python 3.4.1 (default, May 21 2014, 01:39:38) 
Type "copyright", "credits" or "license" for more information.

IPython 2.1.0 -- An enhanced Interactive Python.
?         -> Introduction and overview of IPython's features.
%quickref -> Quick reference.
help      -> Python's own help system.
object?   -> Details about 'object', use 'object??' for extra details.

In [1]: import sqlite3

In [2]: connection = sqlite3.connect('coachdata.sqlite')

In [3]: cursor = connection.cursor()

In [4]: cursor.execute('SELECT * FROM athletes')
Out[4]: <sqlite3.Cursor at 0x104574570>

In [5]: cursor.fetchall()
Out[5]: 
[(1, 'Sarah Sweeney', '2002-6-17'),
 (2, 'Mikey McManus', '2002-2-24'),
 (3, 'Sally Sanchez', '2002-11-24'),
 (4, 'Vera Vi', '2002-12-25'),
 (5, 'Julie Jones', '2002-8-17'),
 (6, 'James Lee', '2002-3-14')]

In [6]: cursor.execute('SELECT * FROM timing_data')
Out[6]: <sqlite3.Cursor at 0x104574570>

In [7]: cursor.fetchall()
Out[7]: 
[(1, '2.18'),
 (1, '2.21'),
 (1, '2.22'),
 (1, '2.25'),
 (1, '2.39'),
 (1, '2.54'),
 (1, '2.55'),
 (1, '2.58'),
 (2, '2.22'),
 (2, '2.31'),
 (2, '2.38'),
 (2, '2.40'),
 (2, '2.49'),
 (2, '3.01'),
 (2, '3.02'),
 (2, '3.22'),
 (3, '2.11'),
 (3, '2.26'),
 (3, '2.31'),
 (3, '2.32'),
 (3, '2.41'),
 (3, '2.44'),
 (3, '2.51'),
 (3, '2.55'),
 (3, '3.00'),
 (3, '3.01'),
 (4, '2.41'),
 (4, '2.49'),
 (4, '3.01'),
 (4, '3.02'),
 (4, '3.11'),
 (4, '3.23'),
 (5, '2.11'),
 (5, '2.23'),
 (5, '2.59'),
 (5, '3.01'),
 (5, '3.02'),
 (5, '3.10'),
 (5, '3.21'),
 (6, '2.01'),
 (6, '2.16'),
 (6, '2.22'),
 (6, '2.34'),
 (6, '2.45'),
 (6, '3.01'),
 (6, '3.10'),
 (6, '3.21')]

In [8]: quit()
$