Python - データの行き先(SQLiteデータベース、sqlite3モジュール(標準ライブラリ)、Prepared statement、csvファイルの読み書き、csvモジュール(標準ライブラリ)、DictReader)

入門 Python 3
原著
楽天ブックス(Kobo) Yahoo!

開発環境

• macOS Mojave - Apple (OS)
• Emacs (Text Editor)
• Windows 10 Pro (OS)
• Visual Studio Code (Text Editor)
• Python 3.7 (プログラミング言語)

入門 Python 3 (Bill Lubanovic (著)、斎藤 康毅 (監修)、長尾 高弘 (翻訳)、オライリージャパン)の8章(データの行き先)、8.7(復習問題)8-5、6、7、8、9.を取り組んでみる。

コード(Emacs)

Python 3

#!/usr/bin/env python3
import csv
import sqlite3 as sql
print('5.')

lines = '''title,author,year
The Weirdstone of Brisingamen,Alan Garner,1960
Perdido Street Station,China Miéville,2000
Thud!,Terry Pratchett,2005
The Spellman Files,Lisa Lutz,2007
Small Gods,Terry Pratchett,1992'''

with open('books.csv', 'w') as f:
    print(lines, end='', file=f)

print('6.')
conn = sql.connect('books.db')
cursor = conn.cursor()
cursor.execute('CREATE TABLE book (title TEXT, author TEXT, year INTEGER)')
conn.commit()

print('7.')

_sql = '''INSERT INTO book VALUES(?, ?, ?)'''

with open('books.csv') as f:
    reader = csv.DictReader(f)
    for book in reader:
        cursor.execute(_sql, (book['title'], book['author'], book['year']))
conn.commit()

print('8.')
cursor.execute('SELECT * FROM book ORDER BY title')
for book in cursor.fetchall():
    print(book)

print('9.')
cursor.execute('SELECT * FROM book ORDER BY year')
for book in cursor.fetchall():
    print(book)

cursor.close()
conn.close()

入出力結果(Terminal, cmd(コマンドプロンプト), Jupyter(IPython))

$ ./sample5.py
5.
6.
7.
8.
('Perdido Street Station', 'China Miéville', 2000)
('Small Gods', 'Terry Pratchett', 1992)
('The Spellman Files', 'Lisa Lutz', 2007)
('The Weirdstone of Brisingamen', 'Alan Garner', 1960)
('Thud!', 'Terry Pratchett', 2005)
9.
('The Weirdstone of Brisingamen', 'Alan Garner', 1960)
('Small Gods', 'Terry Pratchett', 1992)
('Perdido Street Station', 'China Miéville', 2000)
('Thud!', 'Terry Pratchett', 2005)
('The Spellman Files', 'Lisa Lutz', 2007)
$