2018年12月3日月曜日

開発環境

初めてのSQL (Alan Beaulieu (著)、株式会社クイープ (翻訳)、オライリージャパン)の3章(クエリ入門)、3.8(練習問題)3-1.を取り組んでみる。

コード(Emacs)

Python 3

#!/usr/bin/env python3
import psycopg2 as sql

conn = sql.connect(database='gregs_list', user='kamimura')
cursor = conn.cursor()

_sql = '''
create table if not exists employ (
emp_id serial,
fname varchar(20),
lname varchar(20),
start_date date,
end_date date,
sperior_emp_id smallint,
dept_id smallint,
title varchar(20),
assigned_branch_id smallint
)
'''

cursor.execute(_sql)

employees = [
    ('Susan', 'Barker', '2002-09-12', '2003-09-12'),
    ('Susan', 'Hawthorne', '2002-04-24', '2004-05-25'),
    ('John', 'Gooding', '2003-11-14', '2005-12-24'),
    ('Helen', 'Fleming', '2004-03-17', '2004-04-17'),
    ('Chris', 'Tucker', '2000-05-11', '2001-05-12')
]

_sql = '''insert into employ
(fname, lname, start_date, end_date)
values
(%s, %s, %s, %s)'''

for employee in employees:
    cursor.execute(_sql, employee)

conn.commit()

_sql = 'select * from employ'
cursor.execute(_sql)
print(', '.join([column[0] for column in cursor.description]))
for row in cursor.fetchall():
    print(row)

_sql = '''select emp_id, lname, fname from employ'''
cursor.execute(_sql)

print(', '.join([column[0] for column in cursor.description]))
for row in cursor.fetchall():
    print(row)

cursor.close()
conn.close()

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

$ ./sample1.py
emp_id, fname, lname, start_date, end_date, sperior_emp_id, dept_id, title, assigned_branch_id
(1, 'Susan', 'Barker', datetime.date(2002, 9, 12), datetime.date(2003, 9, 12), None, None, None, None)
(2, 'Susan', 'Hawthorne', datetime.date(2002, 4, 24), datetime.date(2004, 5, 25), None, None, None, None)
(3, 'John', 'Gooding', datetime.date(2003, 11, 14), datetime.date(2005, 12, 24), None, None, None, None)
(4, 'Helen', 'Fleming', datetime.date(2004, 3, 17), datetime.date(2004, 4, 17), None, None, None, None)
(5, 'Chris', 'Tucker', datetime.date(2000, 5, 11), datetime.date(2001, 5, 12), None, None, None, None)
emp_id, lname, fname
(1, 'Barker', 'Susan')
(2, 'Hawthorne', 'Susan')
(3, 'Gooding', 'John')
(4, 'Fleming', 'Helen')
(5, 'Tucker', 'Chris')
$

0 コメント:

コメントを投稿