クラスの実装
データベースの管理 - base
今回はpythonanywhereでMysqlを使います。
db.py
import MySQLdb
class Db:
def __init__(self):
self.connect = None
self.cursor = None
def openDb(self,**dns):
self.connect = MySQLdb.connect(
user=dns['user'], #'eternalkagosima',
passwd=dns['passwd'], #'xxxxxxxxx',
host=dns['host'], #'eternalkagosima.mysql.pythonanywhere-services.com',
db=dns['db'], #'eternalkagosima$mydb',
charset=dns['charset'], #"utf8"
connect_timeout=600 #10 minute
)
def getConnect(self):
return self.connect
def getCursor(self):
self.cursor = self.connect.cursor()
return self.cursor
def getDictCursor(self):
self.cursor = self.connect.cursor(MySQLdb.cursors.DictCursor)
return self.cursor
def execute(self,sqlstr):
return self.cursor.execute(sqlstr)
def lastID(self):
sql = 'select last_insert_id();'
self.cursor.execute(sql)
results = self.cursor.fetchall()
return results
def commit(self):
self.connect.commit()
def close(self):
self.cursor.close()
self.connect.close()
で、このdbをimportしてこのプログラム用のラッパークラスを作ります。
PADb.py
from db import Db
class PADb(Db):
def __init__(self):
super().__init__()
super().openDb(user='eternalkagosima',passwd='xxxxxxxx',host='eternalkagosima.mysql.pythonanywhere-services.com',
db='eternalkagosima$mydb',charset='utf8')
def createUser(self):
self.execute('create table if not exists users (ukey int not null auto_increment primary key,' \
'uid varchar(30) not null,' \
'pass varchar(255) not null)'
)
def createEvent(self):
self.execute('create table if not exists events (ekey int not null auto_increment primary key,'\
'comment varchar(50),'\
'email varchar(255),'\
'eventname varchar(50),'\
'firstDay date,'\
'ownername varchar(30),'\
'limitdate date,'\
'paticip varchar(255))'
)
def createPhotos(self):
self.execute('create table if not exists photos (pkey int not null auto_increment primary key,'\
'comment varchar(50),'\
'pdate datetime,'\
'eventID int,'\
'filename varchar(80),'\
'fromId varchar(30)'
)
def createPaticip(self):
self.execute('create table if not exists paticip (ckey int not null auto_increment primary key,'\
'eventID int,'\
'eventname varchar(50),'\
'userID varchar(30),'\
'rmemo varchar(100))'
)
新規ユーザーの追加と、ユーザーデータの読み込みを作りました。データベースにusersというテーブルを作り、idとpasswordを保存します。passwordは暗号化して渡します
イベント管理用のクラス - event
event.py
from photoBase import PhotoBase
import datetime
import re
class Event:
def __init__(self,connect,cursor):
self.connect = connect
self.cursor = cursor
def newEvent(self, userid, eventname, firstDay, limitdate, comment, email, paticip):
t = datetime.date.today()
edata = self.dirCurrentEvents(t.strftime("%Y-%m-%d"))
if len(edata) > 10:
return False
else:
sql = 'insert into events (ownername,eventname,firstDay,limitdate,comment,email,paticip) values ("{0}","{1}","{2}","{3}","{4}","{5}","{6}");'.format(
userid,eventname,firstDay,limitdate,comment,email,userid)
self.cursor.execute(sql)
self.connect.commit()
return True
def getEvent(self, searchKey:int):
"""
イベントIDを指定して、イベント情報を辞書で返却する
Parameters:
searchKey: イベントID
Returns:
イベントレコードの内容、ただしpaticipは a,b,cの文字列を['a','b','c']のリストにする
"""
result = []
sql = 'select * from events where ekey = {0}'.format(searchKey)
self.cursor.execute(sql)
rows = self.cursor.fetchall()
for row in rows:
row['paticip'] = self.str2list(row['paticip'])
result.append(row)
return result
def updateEvent(self, ekey, userid, eventname, limitdate, comment, email, paticip:list):
strpat = self.list2str(paticip)
sql = 'update events set ownername="{0}",eventname="{1}",limitdate="{2}",comment="{3}",email="{4}",paticip="{5}" where ekey={6}'.format(
userid,eventname,limitdate,comment,email,strpat,ekey)
self.cursor.execute(sql)
self.connect.commit()
def dirAllEvents(self):
result = []
sql = 'select * from events'
self.cursor.execute(sql)
rows = self.cursor.fetchall()
for row in rows:
row['paticip'] = self.str2list(row['paticip'])
result.append(row)
return result
def deleteEvent(self, ekey):
photos = PhotoBase(self.connect,self.cursor)
edata = self.getEvent(ekey)
for e in edata:
pdata = self.dirEventPhotos(e["ekey"])
if pdata != None:
for p in pdata:
photos.deletePhoto(p["pkey"])
sql = 'delete from events where ekey={0}'.format(ekey)
self.cursor.execute(sql)
self.connect.commit()
def fmtDate(self,day:str,sepChar:str):
l = re.findall(r"\d+", day)
l = [int(s) for s in l]
dt = datetime.datetime(*l)
fmt = "%Y-%m-%d"
fmt.replace("-",sepChar)
strDay = dt.strftime(fmt)
return strDay
def dirCurrentEvents(self,thisDay:str):
strDay = self.fmtDate(thisDay,"-")
result = []
sql = 'select * from events where limitdate>="{0}"'.format(strDay)
self.cursor.execute(sql)
rows = self.cursor.fetchall()
for row in rows:
row['paticip'] = self.str2list(row['paticip'])
result.append(row)
return result
def dirLostEvents(self,thisDay:str):
strDay = self.fmtDate(thisDay,"-")
result = []
sql = 'select * from events where limitdate<="{0}"'.format(strDay)
self.cursor.execute(sql)
rows = self.cursor.fetchall()
for row in rows:
row['paticip'] = self.str2list(row['paticip'])
result.append(row)
return result
def dirEventPhotos(self,ekey):
'''make relation ship to event photos and photo image'''
photos = PhotoBase(self.connect,self.cursor)
results = photos.dirPhoto(ekey)
if results:
returnList = []
for result in results:
returnList.append(result)
return returnList
else:
return None
def str2list(self,s):
l = s.split(',')
return l
def list2str(self,l):
s = ','.join(l)
return s
eventsというデータベーステーブルを作り、新規イベントの登録、イベント情報の取得、イベントの修正、イベントの一覧、イベントの削除、現在稼働中のイベント一覧、期限切れのイベント一覧、イベント中のファイルの一覧、を作りました
イベント参加依頼のクラス - paticip
paticip.py
from event import Event
class Paticip:
def __init__(self,connect,cursor):
self.connect = connect
self.cursor = cursor
def newRequest(self, ekey, eventname, uid, rmemo):
sql = 'insert into paticip (eventID,eventname,userid,rmemo) values ({0},"{1}","{2}","{3}")'.format(
ekey,eventname,uid,rmemo)
self.cursor.execute(sql)
self.connect.commit()
return True
def havePatic(self, fromid, ekey):
"""
該当イベントのpaticipの中に申請者の名前があるか
Parameters:
fromid: 申請者の名前
ekey: イベントの番号
Returns:
True: 申請済み
False: 未申請
"""
sql = 'select * from events where ekey = {0}'.format(ekey)
self.cursor.execute(sql)
rows = self.cursor.fetchall()
events = Event(self.connect,self.cursor)
paticip = events.str2list(rows[0]['paticip'])
return fromid in paticip
def getRequest(self, myElist:list):
"""
paticptテーブルにある自分宛てのリクエストを探す
Parameters:
myElist: 自分が作ったイベントの辞書の[{'eventid': 2}]のリスト
Returns:
自分宛てのpaticipレコードのリスト
"""
result = []
for my in myElist:
sql = 'select * from paticip where eventID = {0}'.format(my['eventid'])
self.cursor.execute(sql)
rows = self.cursor.fetchall()
for row in rows:
result.append(row)
return result
def sendRequest(self, ckey):
"""
paticptテーブルにある指定された接続要求のデータを返す
Parameters:
ckey: paticipのckey指定
Returns:
paticipレコードのリスト
"""
sql = 'select * from paticip where ckey = {0}'.format(ckey)
self.cursor.execute(sql)
rows = self.cursor.fetchall()
return rows[0]
def deleteRequest(self,ckey):
"""
指定されたキーのpaticipレコードを削除する
Parameters:
ckey: paticipレコードキー
Returns:
True:
"""
sql = 'delete from paticip where ckey={0}'.format(ckey)
self.cursor.execute(sql)
self.connect.commit()
return True
paticipというデータベーステーブルを作り、依頼の発行、依頼の読み込み、依頼の削除を行います
画像ファイルを管理するデータベースのクラス - photoBase
photobase.py
import os
class PhotoBase:
def __init__(self,connect,cursor):
self.connect = connect
self.cursor = cursor
self.script_directory = os.path.dirname(os.path.abspath(__file__))
def getPhotoDetail(self,pKey):
return self.photoBase.fetch(query=dict)
def newPhoto(self, fromID, eventID, fileName, datetime, comment):
sql = 'insert into photos (comment,pdate,eventID,filename,fromid) values ("{0}","{1}",{2},"{3}","{4}")'.format(
comment,datetime,eventID,fileName,fromID)
self.cursor.execute(sql)
self.connect.commit()
return True
def updatePhoto(self, pkey, fromID, eventID, fileName, deleted, datetime, accesscount, comment):
self.photoBase.update({"fromid": fromID, "eventID": eventID,
"filename": fileName,"deleted":deleted,"datetime":datetime,
"accesscount":accesscount,"comments":comment},pkey)
def dirPhoto(self,eKey):
"""
イベントに含まれる写真辞書データの一覧を取得する
Parameters:
ekey: イベント番号
Returns:
ファイル名一覧のリスト
"""
result = []
sql = 'select * from photos where eventId="{0}"'.format(eKey)
self.cursor.execute(sql)
rows = self.cursor.fetchall()
for row in rows:
result.append(row)
return result
def dirPhotoNames(self):
result = self.photoBase.list(limit=1000)
all_files = result.get("names")
paging = result.get("paging")
last = paging.get("last") if paging else None
while (last):
result = self.photoBase.list(limit=1000,last=last)
all_files += result.get("names")
paging = result.get("paging")
last = paging.get("last") if paging else None
return all_files
def deletePhoto(self,pkey):
sql = 'select * from photos where pkey={0}'.format(pkey)
self.cursor.execute(sql)
rows = self.cursor.fetchall()
if rows:
filename = rows[0]['filename']
os.remove(self.script_directory + '/static/images/' + filename)
sql = 'delete from photos where pkey={0}'.format(pkey)
self.cursor.execute(sql)
self.connect.commit()
return True
else:
return False
photosというデータベーステーブルを作成し、画像ファイルの詳細取得、写真登録、写真情報修正、ファイル名一覧、画像ファイル削除を作成しました
全体のコントール - app
app.py
import os
class PhotoBase:
def __init__(self,connect,cursor):
self.connect = connect
self.cursor = cursor
self.script_directory = os.path.dirname(os.path.abspath(__file__))
def getPhotoDetail(self,pKey):
return self.photoBase.fetch(query=dict)
def newPhoto(self, fromID, eventID, fileName, datetime, comment):
sql = 'insert into photos (comment,pdate,eventID,filename,fromid) values ("{0}","{1}",{2},"{3}","{4}")'.format(
comment,datetime,eventID,fileName,fromID)
self.cursor.execute(sql)
self.connect.commit()
return True
def updatePhoto(self, pkey, fromID, eventID, fileName, deleted, datetime, accesscount, comment):
self.photoBase.update({"fromid": fromID, "eventID": eventID,
"filename": fileName,"deleted":deleted,"datetime":datetime,
"accesscount":accesscount,"comments":comment},pkey)
def dirPhoto(self,eKey):
"""
イベントに含まれる写真辞書データの一覧を取得する
Parameters:
ekey: イベント番号
Returns:
ファイル名一覧のリスト
"""
result = []
sql = 'select * from photos where eventId="{0}"'.format(eKey)
self.cursor.execute(sql)
rows = self.cursor.fetchall()
for row in rows:
result.append(row)
return result
def dirPhotoNames(self):
result = self.photoBase.list(limit=1000)
all_files = result.get("names")
paging = result.get("paging")
last = paging.get("last") if paging else None
while (last):
result = self.photoBase.list(limit=1000,last=last)
all_files += result.get("names")
paging = result.get("paging")
last = paging.get("last") if paging else None
return all_files
def deletePhoto(self,pkey):
sql = 'select * from photos where pkey={0}'.format(pkey)
self.cursor.execute(sql)
rows = self.cursor.fetchall()
if rows:
filename = rows[0]['filename']
os.remove(self.script_directory + '/static/images/' + filename)
sql = 'delete from photos where pkey={0}'.format(pkey)
self.cursor.execute(sql)
self.connect.commit()
return True
else:
return False