開発環境
- OS X Mavericks - Apple、たまにFreeBSD 10(OS)
- Emacs (CUI)、BBEdit - Bare Bones Software, Inc. (GUI) (Text Editor)
- Python 3.4(プログラミング言語)
- SQLite (RDBMS(Relational Database Management System))
Head First SQL ―頭とからだで覚えるSQLの基本 (Lynn Beighley(著)、 佐藤 直生 (監訳)、 松永 多苗子 (翻訳)、オライリージャパン)の6章(高度なSELECT文: 新たな目でデータを見る)、頭の体操(p.271)を解いてみる。
頭の体操(p.271)
コード(BBEdit, Emacs)
sample269.py
#!/usr/bin/env python3 #-*- coding: utf-8 -*- import sqlite3 connection = sqlite3.connect('chapter6.sqlite') cursor = connection.cursor() def p_all(table): cursor.execute("""SELECT * FROM {0}""".format(table)) print(tuple(map(lambda header: header[0], cursor.description))) p(table) def p(msg): print(msg) for row in cursor.fetchall(): print(row) p_all('cookie_sales') cursor.execute(""" SELECT first_name, COUNT(DISTINCT sale_date) FROM cookie_sales GROUP BY first_name ORDER BY COUNT(DISTINCT sale_date) DESC """) p('クッキーを売った日数') connection.close()
入出力結果(Terminal, IPython)
$ ./sample271.py ('ID', 'first_name', 'sales', 'sale_date') cookie_sales (1, 'Lindsey', 32.02, '2007-03-12') (2, 'Nicole', 26.53, '2007-03-12') (3, 'Britney', 11.25, '2007-03-12') (4, 'Ashley', 18.96, '2007-03-12') (5, 'Lindsey', 9.16, '2007-03-11') (6, 'Nicole', 1.52, '2007-03-11') (7, 'Britney', 43.21, '2007-03-11') (8, 'Ashley', 8.05, '2007-03-11') (9, 'Lindsey', 17.62, '2007-03-10') (10, 'Nicole', 24.19, '2007-03-10') (11, 'Britney', 3.4, '2007-03-10') (12, 'Ashley', 15.21, '2007-03-10') (13, 'Lindsey', 0, '2007-03-09') (14, 'Nicole', 31.99, '2007-03-09') (15, 'Britney', 2.58, '2007-03-09') (16, 'Ashley', 0, '2007-03-09') (17, 'Lindsey', 2.34, '2007-03-08') (18, 'Nicole', 13.44, '2007-03-08') (19, 'Britney', 8.78, '2007-03-08') (20, 'Ashley', 26.82, '2007-03-08') (21, 'Lindsey', 3.71, '2007-03-07') (22, 'Nicole', 0.56, '2007-03-07') (23, 'Britney', 34.19, '2007-03-07') (24, 'Ashley', 7.77, '2007-03-07') (25, 'Lindsey', 16.23, '2007-03-06') (26, 'Nicole', 0, '2007-03-06') (27, 'Britney', 4.5, '2007-03-06') (28, 'Ashley', 19.22, '2007-03-06') クッキーを売った日数 ('Ashley', 7) ('Britney', 7) ('Lindsey', 7) ('Nicole', 7) $
0 コメント:
コメントを投稿