Python - データの行き先(RDB(リレーショナルデータベース)、SQL、sqlte3モジュール、connectメソッド、cursorメソッド、executeメソッド、プレースホルダー、commitメソッド)

入門 Python 3
原著

開発環境

• macOS High Sierra - Apple
• Emacs (Text Editor)
• Python 3.6 (プログラミング言語)

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

コード(Emacs)

Python 3

#!/usr/bin/env python3

print('8-5')
filename = 'books.csv'
text = '''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(filename, 'w') as f:
    print(text, file=f, end='')

with open(filename) as f:
    for line in f:
        print(line, end='')
print()

print('8-6')
import sqlite3

conn = sqlite3.connect('books.db')
curs = conn.cursor()
curs.execute('''CREATE TABLE book
(title VARCHAR(20), author VARCHAR(20), year INT)''')
conn.commit()

print('8-7')
import csv
ins = 'INSERT INTO book VALUES(?, ?, ?)'
with open(filename) as f:
    reader = csv.DictReader(f)
    for row in reader:
        curs.execute(ins, (row['title'], row['author'], row['year']))
conn.commit()

print('8-8')
curs.execute('SELECT title FROM book ORDER BY title')

for row in curs.fetchall():
    print(row[0])

print('8-9')
curs.execute('SELECT title, author, year from book ORDER BY year')
for row in curs.fetchall():
    print(row)

curs.close()
conn.close()

入出力結果(Terminal, Jupyter(IPython))

$ ./sample3.py
8-5
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
8-6
8-7
8-8
Perdido Street Station
Small Gods
The Spellman Files
The Weirdstone of Brisingamen
Thud!
8-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)
$