-
テーブルのクラスを作成する
Visual Studio Code(VSC)からサブフォルダ「sqlite」「lib」を作成します。
次に行1-17をコピペしたらサブフォルダ「sqlite/lib」に「employee.py」の名前で保存します。
class Employee:
def __init__(self, id, first, last, pay):
self.id = id
self.first = first
self.last = last
self.pay = pay
@property
def email(self):
return '{}.{}@email.com'.format(self.first, self.last)
@property
def fullname(self):
return '{} {}'.format(self.first, self.last)
def __repr__(self):
return "Employee({}, '{}', '{}', {})".format(self.id, self.first, self.last, self.pay)
図6
図6はVSCの画面です。
サブフォルダ「sqlite/lib」にテーブルのクラス「employee.py」が保存されています。
-
レコードを追加、更新、削除、選択する関数を定義する
ここではSQLite3のテーブル「employee」のクラス「Employee」を使用して、
レコードを追加、更新、削除、選択(抽出)する関数を定義します。
行2ではSQLite3のライブラリを取り込んでいます。
行3ではテーブル「employee」のクラス「Employee」を取り込んでいます。
行5-15はSQLite3のデータベースをメモリ上に作成してテスト用のレコードを作成する関数です。
行17-24はテーブルから全レコードを抽出する関数です。
以降の関数については説明を省略します。
# Import the libraries
import sqlite3
from sqlite.lib.employee import Employee
def load_data():
con = sqlite3.connect(':memory:')
c = con.cursor()
c.execute('CREATE TABLE employee (id INTEGER, first TEXT, last TEXT, pay INTEGER)')
c.execute("INSERT INTO employee VALUES (1, 'Taro', 'Kasai', 1000)")
c.execute("INSERT INTO employee VALUES (2, 'Hanako', 'Kasai', 900)")
c.execute("INSERT INTO employee VALUES (3, 'Taro', 'Yamada', 2000)")
c.execute("INSERT INTO employee VALUES (4, 'Hanako', 'Yamada', 1900)")
con.commit()
#conn.close()
return con
def get_all_emps():
row_count = c.execute('SELECT * FROM employee').rowcount
emps = []
for row in c.fetchall():
id, first, last, pay = row
emp = Employee(id, first, last, pay)
emps.append(emp)
return emps
def get_emp_by_id(id):
row_count = c.execute('SELECT * FROM employee WHERE id=:id', {'id': id}).rowcount
id, first, last, pay = c.fetchone()
emp = Employee(id, first, last, pay)
return emp
def get_count_by_id(id):
row_count = c.execute('SELECT Count(*) AS rec_count FROM employee WHERE id=:id', {'id': id}).rowcount
rec_count = c.fetchone() # tuple (1,)
#print('rec_count=', rec_count)
return rec_count[0]
def get_emps_by_lastname(lastname):
row_count = c.execute('SELECT * FROM employee WHERE last=:last', {'last': lastname}) .rowcount
emps = []
for row in c.fetchall():
id, first, last, pay = row
emp = Employee(id, first, last, pay)
emps.append(emp)
return emps
def get_emps_by_fullname(firstname, lastname):
row_count = c.execute('SELECT * FROM employee WHERE first=:first AND last=:last',
{'first': firstname, 'last': lastname}) .rowcount
emps = []
for row in c.fetchall():
id, first, last, pay = row
emp = Employee(id, first, last, pay)
emps.append(emp)
return emps
def insert_emp(emp):
with con:
affected_rows = c.execute('INSERT INTO employee VALUES (:id, :first, :last, :pay)',
{'id': emp.id,'first': emp.first, 'last': emp.last, 'pay': emp.pay}).rowcount
return affected_rows
def update_pay_by_id(id, pay):
with con:
affected_row = c.execute('UPDATE employee SET pay=:pay WHERE id=:id',
{'pay': pay, 'id': id}).rowcount
return affected_row
def remove_emp_by_id(id):
with con:
affected_row = c.execute('DELETE FROM employee WHERE id = :id', {'id': id}).rowcount
return affected_row
# %%
図7
図7はVSCの画面です。
-
テーブルからレコードを抽出する
### Select a row or rows
con = load_data()
c = con.cursor()
# Select all the rows
print('id\tfirst\tlast\tpay')
print(('-'*30))
for emp in get_all_emps():
print(f'{emp.id}\t{emp.first}\t{emp.last}\t{emp.pay:,}')
print(('-'*30))
print()
# Filter the row by id
print('get_emp_by_id(1)▶', get_emp_by_id(1))
# Filter the row by lastname
print("get_emps_by_lastname('Kasai')▶", get_emps_by_lastname('Kasai'))
# Filter the row by fullname
print("get_emps_by_fullname('Taro', 'Kasai')▶", get_emps_by_fullname('Taro', 'Kasai'))
con.close()
図8
図8は実行画面です。
-
テーブルにレコードを追加する
### Insert the row
con = load_data()
c = con.cursor()
emp = Employee(9, 'Taro', 'Tanaka', 9999)
print('insert_emp(emp)▶', insert_emp(emp))
print('get_emp_by_id(9)▶', get_emp_by_id(9))
con.commit()
con.close()
図9
図9は実行画面です。
-
テーブルのレコードを更新する
### Update the row
con = load_data()
c = con.cursor()
print('Before Update:', get_emp_by_id(1))
print('update_pay_by_id(1, 9999)▶', update_pay_by_id(1, 9999))
print('After Update:', get_emp_by_id(1))
con.commit()
con.close()
図10
図10は実行画面です。
-
テーブルからレコードを削除する
### Delete the row
con = load_data()
c = con.cursor()
print('Before Remove▶', get_count_by_id(1))
print('remove_emp_by_id(1)▶', remove_emp_by_id(1))
print('After Remove▶', get_count_by_id(1))
con.commit()
con.close()
図11
図11は実行画面です。
-
ここで解説したコードをまとめて掲載
最後にここで解説したすべてのコードをまとめて掲載しましたので参考にしてください。
# Import the libraries
import sqlite3
from sqlite.lib.employee import Employee
def load_data():
con = sqlite3.connect(':memory:')
c = con.cursor()
c.execute('CREATE TABLE employee (id INTEGER, first TEXT, last TEXT, pay INTEGER)')
c.execute("INSERT INTO employee VALUES (1, 'Taro', 'Kasai', 1000)")
c.execute("INSERT INTO employee VALUES (2, 'Hanako', 'Kasai', 900)")
c.execute("INSERT INTO employee VALUES (3, 'Taro', 'Yamada', 2000)")
c.execute("INSERT INTO employee VALUES (4, 'Hanako', 'Yamada', 1900)")
con.commit()
#conn.close()
return con
def get_all_emps():
row_count = c.execute('SELECT * FROM employee').rowcount
emps = []
for row in c.fetchall():
id, first, last, pay = row
emp = Employee(id, first, last, pay)
emps.append(emp)
return emps
def get_emp_by_id(id):
row_count = c.execute('SELECT * FROM employee WHERE id=:id', {'id': id}).rowcount
id, first, last, pay = c.fetchone()
emp = Employee(id, first, last, pay)
return emp
def get_count_by_id(id):
row_count = c.execute('SELECT Count(*) AS rec_count FROM employee WHERE id=:id', {'id': id}).rowcount
rec_count = c.fetchone() # tuple (1,)
#print('rec_count=', rec_count)
return rec_count[0]
def get_emps_by_lastname(lastname):
row_count = c.execute('SELECT * FROM employee WHERE last=:last', {'last': lastname}) .rowcount
emps = []
for row in c.fetchall():
id, first, last, pay = row
emp = Employee(id, first, last, pay)
emps.append(emp)
return emps
def get_emps_by_fullname(firstname, lastname):
row_count = c.execute('SELECT * FROM employee WHERE first=:first AND last=:last',
{'first': firstname, 'last': lastname}) .rowcount
emps = []
for row in c.fetchall():
id, first, last, pay = row
emp = Employee(id, first, last, pay)
emps.append(emp)
return emps
def insert_emp(emp):
with con:
affected_rows = c.execute('INSERT INTO employee VALUES (:id, :first, :last, :pay)',
{'id': emp.id,'first': emp.first, 'last': emp.last, 'pay': emp.pay}).rowcount
return affected_rows
def update_pay_by_id(id, pay):
with con:
affected_row = c.execute('UPDATE employee SET pay=:pay WHERE id=:id',
{'pay': pay, 'id': id}).rowcount
return affected_row
def remove_emp_by_id(id):
with con:
affected_row = c.execute('DELETE FROM employee WHERE id = :id', {'id': id}).rowcount
return affected_row
# %%
### Select a row or rows
con = load_data()
c = con.cursor()
# Select all the rows
print('id\tfirst\tlast\tpay')
print(('-'*30))
for emp in get_all_emps():
print(f'{emp.id}\t{emp.first}\t{emp.last}\t{emp.pay:,}')
print(('-'*30))
print()
# Filter the row by id
print('get_emp_by_id(1)▶', get_emp_by_id(1))
# Filter the row by lastname
print("get_emps_by_lastname('Kasai')▶", get_emps_by_lastname('Kasai'))
# Filter the row by fullname
print("get_emps_by_fullname('Taro', 'Kasai')▶", get_emps_by_fullname('Taro', 'Kasai'))
con.close()
# %%
### Insert the row
con = load_data()
c = con.cursor()
emp = Employee(9, 'Taro', 'Tanaka', 9999)
print('insert_emp(emp)▶', insert_emp(emp))
print('get_emp_by_id(9)▶', get_emp_by_id(9))
# print('get_count_by_id(9)▶', get_count_by_id(9))
# print('id\tfirst\tlast\tpay')
# print(('-'*30))
# for emp in get_all_emps():
# print(f'{emp.id}\t{emp.first}\t{emp.last}\t{emp.pay:,}')
con.commit()
con.close()
# %%
### Update the row
con = load_data()
c = con.cursor()
print('Before Update:', get_emp_by_id(1))
print('update_pay_by_id(1, 9999)▶', update_pay_by_id(1, 9999))
print('After Update:', get_emp_by_id(1))
con.commit()
con.close()
# %%
### Delete the row
con = load_data()
c = con.cursor()
print('Before Remove▶', get_count_by_id(1))
print('remove_emp_by_id(1)▶', remove_emp_by_id(1))
print('After Remove▶', get_count_by_id(1))
con.commit()
con.close()
# %%