PostgreSQL - Python - クエリ入門(select文、列の選択)

初めてのSQL
原著
楽天ブックス(Kobo) Yahoo!

開発環境

• macOS Mojave - Apple (OS)
• Emacs (Text Editor)
• Windows 10 Pro (OS)
• Visual Studio Code (Text Editor)
• PostgreSQL (ORDBMS(object-relational database management system))
• Python 3.7 (プログラミング言語)
• psycopg2 (パッケージ)

初めての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')
$