def _db_save_update(self):
with DBConnector(dbName='db_%s' %
project.name()) as con, con.cursor() as cursor:
# データの保存(UPDATE)
cursor.execute(
"""
UPDATE table_member
SET name = %s,
birthday = %s,
ym = %s,
gender = %s,
position = %s,
detail = %s,
money = %s,
last_updated = %s
WHERE member_id = %s; """, (
self.attr["name"],
self.attr["birthday"],
self.attr["ym"],
self.attr["gender"],
self.attr["position"],
self.attr["detail"],
self.attr["money"],
'{0:%Y-%m-%d %H:%M:%S}'.format(self.attr["last_updated"]),
self.attr["member_id"],
))
con.commit()
return self.attr["member_id"]
def find(id):
with DBConnector(dbName='db_%s' % project.name()) as con, con.cursor(MySQLdb.cursors.DictCursor) as cursor:
cursor.execute("""
SELECT *
FROM healthcan
WHERE id = %s;
""", (id,))
results = cursor.fetchall()
if (len(results) == 0):
return None
data = results[0]
hc = healthcan()
hc.attr["id"] = data["id"]
hc.attr["user_id"] = data["user_id"]
hc.attr["name"] = data["name"]
hc.attr["date"] = data["date"]
hc.attr["time"] = data["time"]
hc.attr["height"] = data["height"]
hc.attr["weight"] = data["weight"]
hc.attr["bmi"] = data["bmi"]
hc.attr["pro_weight"] = data["pro_weight"]
hc.attr["diff_weight"] = data["diff_weight"]
return hc
def select_by_user_id(user_id):
with DBConnector(dbName='db_%s' % project.name()) as con, \
con.cursor(MySQLdb.cursors.DictCursor) as cursor:
cursor.execute(
"""
SELECT *
FROM table_cashbook
WHERE user_id = %s;
""", (user_id, ))
results = cursor.fetchall()
records = []
for data in results:
cb = cashbook()
cb.attr["id"] = data["id"]
cb.attr["user_id"] = data["user_id"]
cb.attr["ym"] = data["ym"]
cb.attr["date"] = data["date"]
cb.attr["summary"] = data["summary"]
cb.attr["detail"] = data["detail"]
cb.attr["income"] = data["income"]
cb.attr["expenses"] = data["expenses"]
cb.attr["amount"] = data["amount"]
cb.attr["last_updated"] = data["last_updated"]
records.append(cb)
return records
def migrate():
# データベースへの接続とカーソルの生成
with DBConnector(dbName=None) as con, con.cursor() as cursor:
# データベース生成
cursor.execute('CREATE DATABASE IF NOT EXISTS db_%s;' %
project.name())
# 生成したデータベースに移動
cursor.execute('USE db_%s;' % project.name())
# テーブル初期化(DROP)
cursor.execute('DROP TABLE IF EXISTS table_cashbook;')
# テーブル初期化(CREATE)
cursor.execute("""
CREATE TABLE `table_cashbook` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) unsigned NOT NULL,
`ym` int(11) NOT NULL,
`date` date NOT NULL,
`summary` varchar(255) DEFAULT NULL,
`detail` text,
`income` decimal(12,0) NOT NULL DEFAULT '0',
`expenses` decimal(12,0) NOT NULL DEFAULT '0',
`amount` decimal(12,0) NOT NULL DEFAULT '0',
`last_updated` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `summary` (`summary`)
)""")
con.commit()
def _db_save_update(self):
with DBConnector(dbName='db_%s' %
project.name()) as con, con.cursor() as cursor:
# データの保存(UPDATE)
cursor.execute(
"""
UPDATE table_cashbook
SET user_id = %s,
ym = %s,
date = %s,
summary = %s,
detail = %s,
income = %s,
expenses = %s,
amount = %s,
last_updatedemail = %s
WHERE id = %s; """,
(self.attr["user_id"], self.attr["ym"], self.attr["date"],
self.attr["summary"], self.attr["detail"],
self.attr["income"], self.attr["expenses"],
self.attr["amount"], '{0:%Y-%m-%d %H:%M:%S}'.format(
self.attr["last_updated"]), self.attr["id"]))
con.commit()
return self.attr["id"]
def migrate():
# データベースへの接続とカーソルの生成
with DBConnector(dbName=None) as con, con.cursor() as cursor:
# データベース生成
cursor.execute('CREATE DATABASE IF NOT EXISTS db_%s;' %
project.name())
# 生成したデータベースに移動
cursor.execute('USE db_%s;' % project.name())
# テーブル初期化(DROP)
cursor.execute('DROP TABLE IF EXISTS table_ingredients;')
# テーブル初期化(CREATE)
cursor.execute("""
CREATE TABLE `table_ingredients` (
`ingredient_id` int(100) unsigned NOT NULL AUTO_INCREMENT,
`ingredient_name` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`ingredient_id`),
UNIQUE KEY (`ingredient_name`)
); """)
# データの追加
cursor.execute("""
INSERT INTO table_ingredients (ingredient_name)
VALUES ('たまご'),('きのこ'),('ベーコン'),('パスタ麺'),('ほうれん草')
,('牛肉'),('じゃがいも'),('人参'),('玉ねぎ'),('白滝')
,('水'),('醤油'),('砂糖'),('みりん'),('ほんだし')
,('鶏肉'),('にんにく'),('塩こしょう'),('オリーブオイル'),('キャベツ')
,('トマト'),('薄力粉'),('ホットケーキミックス'),('ピーマン'),('ハム')
,('ケチャップ'),('スライスチーズ'),('ひき肉'),('ご飯'),('生姜')
,('オイスターソース'),('ソース'),('チリパウダー'),('カレールー'),('ウスターソース')
,('ツナ'),('豆腐'),('豆板醤'),('料理酒'),('水溶き片栗粉')
,('ごま油'),('ねぎ'),('ウインナー'),('サラダ油'),('マヨネーズ')
,('バター'),('塩'),('しめじ')
""")
con.commit()
def _db_save_insert(self):
with DBConnector(dbName='db_%s' % project.name()) as con, con.cursor() as cursor:
# データの保存(INSERT)
cursor.execute("""
INSERT INTO healthcan
(user_id, name, date, time, height, weight, bmi, pro_weight, diff_weight)
VALUES
(%s, %s, %s, %s, %s, %s, %s, %s, %s); """,
(self.attr["user_id"],
self.attr["name"],
self.attr["date"],
self.attr["time"],
self.attr["height"],
self.attr["weight"],
self.attr["bmi"],
self.attr["pro_weight"],
self.attr["diff_weight"]
))
# INSERTされたAUTO INCREMENT値を取得
cursor.execute("SELECT last_insert_id();")
results = cursor.fetchone()
self.attr["id"] = results[0]
con.commit()
return self.attr["id"]
def find(id):
with DBConnector(dbName='db_%s' % project.name()) as con, \
con.cursor(MySQLdb.cursors.DictCursor) as cursor:
cursor.execute(
"""
SELECT *
FROM table_cashbook
WHERE id = %s;
""", (id, ))
results = cursor.fetchall()
if (len(results) == 0):
return None
data = results[0]
cb = cashbook()
cb.attr["id"] = data["id"]
cb.attr["user_id"] = data["user_id"]
cb.attr["ym"] = data["ym"]
cb.attr["date"] = data["date"]
cb.attr["summary"] = data["summary"]
cb.attr["detail"] = data["detail"]
cb.attr["income"] = data["income"]
cb.attr["expenses"] = data["expenses"]
cb.attr["amount"] = data["amount"]
cb.attr["last_updated"] = data["last_updated"]
return cb
def move(id):
with DBConnector(dbName='db_%s' % project.name()) as con, \
con.cursor(MySQLdb.cursors.DictCursor) as cursor:
cursor.execute("""
SELECT *
FROM table_wishList
WHERE id = %s;
""", (id,))
results = cursor.fetchall()
if (len(results) == 0):
return None
data = results[0]
w = wishList()
w.attr["id"] = data["id"]
w.attr["user_id"] = data["user_id"]
w.attr["name"] = data["name"]
w.attr["quantity"] = data["quantity"]
w.attr["kind"] = data["kind"]
w.attr["last_updated"] = data["last_updated"]
# データの削除(DELETE)
cursor.execute("""
DELETE
FROM table_wishList
WHERE id = %s; """,
(id,)
)
con.commit()
return w
def order_by_member_id(member_id):
with DBConnector(dbName='db_%s' % project.name()) as con, \
con.cursor(MySQLdb.cursors.DictCursor) as cursor:
cursor.execute("""
SELECT *
FROM table_member
ORDER BY member_id ASC;
""")
results = cursor.fetchall()
records = []
for data in results:
lm = member()
lm.attr["member_id"] = data["member_id"]
lm.attr["name"] = data["name"]
lm.attr["birthday"] = data["birthday"]
lm.attr["ym"] = data["ym"]
lm.attr["gender"] = data["gender"]
lm.attr["position"] = data["position"]
lm.attr["detail"] = data["detail"]
lm.attr["money"] = data["money"]
lm.attr["last_updated"] = data["last_updated"]
records.append(lm)
return records
def createProcedure():
json = request.json
db = DBConnector()
results = []
for procedure in json:
name = abort(400) if not procedure.get('name') else procedure.get(
'name')
note = abort(400) if not procedure.get('note') else procedure.get(
'note')
price = abort(400) if not procedure.get('price') else procedure.get(
'price')
date = abort(400) if not procedure.get('date') else procedure.get(
'date')
storageID = abort(400) if not procedure.get(
'storageID') else procedure.get('storageID')
patientID = abort(400) if not procedure.get(
'patientID') else procedure.get('patientID')
procedure = {
'name': name,
'note': note,
'price': price,
'date': date,
'storageID': storageID,
'patientID': patientID
}
result = db.createProcedure(procedure)
results.append(result)
db.close()
return jsonify({'procedures': results}), 201
def getProcedureType(typeID):
db = DBConnector()
result = db.getProcedureType(typeID)
if len(result) == 0:
abort(404)
db.close()
return jsonify({'type': result})
def createPatient():
'''
Create a new patient through a POST method. Takes a JSON object
Returns:
The newly created patient.
'''
json = request.json
if not json or not 'firstName' in json:
abort(400)
lastName = '' if not json.get('lastName') else json.get('lastName')
mobile = '' if not json.get('mobile') else json.get('mobile')
gender = '' if not json.get('gender') else json.get('gender')
email = '' if not json.get('email') else json.get('email')
note = '' if not json.get('note') else json.get('note')
storageID = '' if not json.get('storageID') else json.get('storageID')
date = '' if not json.get('date') else json.get('date')
age = 0 if not json.get('age') else json.get('age')
patient = {
'firstName': request.json['firstName'],
'lastName': lastName,
'mobile': mobile,
'gender': gender,
'email': email,
'note': note,
'storageID': storageID,
'date': date,
'age': age
}
db = DBConnector()
result = db.createPatient(patient)
db.close()
return jsonify({'patient': result}), 201
def find(member_id):
with DBConnector(dbName='db_%s' % project.name()) as con, \
con.cursor(MySQLdb.cursors.DictCursor) as cursor:
cursor.execute(
"""
SELECT *
FROM table_member
WHERE member_id = %s;
""", (member_id, ))
results = cursor.fetchall()
if (len(results) == 0):
return None
data = results[0]
lm = member()
lm.attr["member_id"] = data["member_id"]
lm.attr["name"] = data["name"]
lm.attr["birthday"] = data["birthday"]
lm.attr["ym"] = data["ym"]
lm.attr["gender"] = data["gender"]
lm.attr["position"] = data["position"]
lm.attr["detail"] = data["detail"]
lm.attr["money"] = data["money"]
lm.attr["last_updated"] = data["last_updated"]
return lm
def getProcedureByPatient(patientID):
db = DBConnector()
result = db.getProcedureByPatient(patientID)
if len(result) == 0:
abort(404)
db.close()
return jsonify({'procedures': result})
def select_by_user_id(user_id):
with DBConnector(dbName='db_%s' % project.name()) as con, con.cursor(MySQLdb.cursors.DictCursor) as cursor:
cursor.execute("""
SELECT *
FROM healthcan
WHERE user_id = %s;
""", (user_id,))
results = cursor.fetchall()
records = []
for data in results:
hc = healthcan()
hc.attr["id"] = data["id"]
hc.attr["user_id"] = data["user_id"]
hc.attr["name"] = data["name"]
hc.attr["date"] = data["date"]
hc.attr["time"] = data["time"]
hc.attr["height"] = data["height"]
hc.attr["weight"] = data["weight"]
hc.attr["bmi"] = data["bmi"]
hc.attr["pro_weight"] = data["pro_weight"]
hc.attr["diff_weight"] = data["diff_weight"]
records.append(hc)
return records
def migrate():
# データベースへの接続とカーソルの生成
with DBConnector(dbName=None) as con, con.cursor() as cursor:
# データベース生成
cursor.execute('CREATE DATABASE IF NOT EXISTS db_%s;' %
project.name())
# 生成したデータベースに移動
cursor.execute('USE db_%s;' % project.name())
# テーブル初期化(DROP)
cursor.execute('DROP TABLE IF EXISTS table_member;')
# テーブル初期化(CREATE)
cursor.execute("""
CREATE TABLE `table_member` (
`member_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`birthday` date NOT NULL,
`ym` int(11) NOT NULL,
`gender` varchar(255) DEFAULT NULL,
`position` varchar(255) DEFAULT NULL,
`detail` text,
`money` decimal(12,0) NOT NULL DEFAULT '0',
`last_updated` datetime NOT NULL,
PRIMARY KEY (`member_id`),
KEY `position` (`position`)
)""")
con.commit()
def _db_save_update(self):
with DBConnector(dbName='db_%s' % project.name()) as con, con.cursor() as cursor:
# データの保存(UPDATE)
cursor.execute("""
UPDATE
SET user_id = %s,
name = %s,
date = %s,
time = %s,
height = %s,
weight = %s,
bmi = %s,
pro_weight = %s,
diff_weight = %s
WHERE id = %s; """,
(self.attr["user_id"],
self.attr["name"],
self.attr["date"],
self.attr["time"],
self.attr["height"],
self.attr["weight"],
self.attr["bmi"],
self.attr["pro_weight"],
self.attr["diff_weight"]),
self.attr["id"])
con.commit()
return self.attr["id"]
def migrate():
# データベースへの接続とカーソルの生成
with DBConnector(dbName=None) as con, con.cursor() as cursor:
# データベース生成
cursor.execute('CREATE DATABASE IF NOT EXISTS db_%s;' %
project.name())
# 生成したデータベースに移動
cursor.execute('USE db_%s;' % project.name())
# テーブル初期化(DROP)
cursor.execute('DROP TABLE IF EXISTS table_cookings;')
# テーブル初期化(CREATE)
cursor.execute("""
CREATE TABLE `table_cookings` (
`cooking_id` int(100) unsigned NOT NULL AUTO_INCREMENT,
`cooking_name` varchar(255) NOT NULL DEFAULT '',
`cooking_img_url` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`cooking_id`),
UNIQUE KEY (`cooking_name`)
); """)
# データの追加
cursor.execute("""
INSERT INTO table_cookings (cooking_name, cooking_img_url)
VALUES ('肉じゃが','../static/img/nikujyaga.jpg'),('チキンステーキ','../static/img/chickensteak.jpg')
,('ピザ','../static/img/pizza.jpg'),('タコライス','../static/img/takoraisu.jpg')
,('カレー','../static/img/curry.jpg'),('麻婆豆腐','../static/img/mabodofu.jpg')
,('オムライス', '../static/img/omuraisu.jpg')
""")
con.commit()
def mode_identification(self):
with DBConnector(dbName='db_%s' %
project.name()) as con, con.cursor() as cursor:
if self.attr["artist_name"] == None:
return all([
self.attr["id"] is None or type(self.attr["id"]) is int,
self.attr["email"] is not None
and type(self.attr["email"]) is str,
self.attr["favorite"] is None
or type(self.attr["favorite"]) is int,
self.attr["password"] is not None
and type(self.attr["password"]) is str,
])
return all([
self.attr["id"] is None or type(self.attr["id"]) is int,
self.attr["email"] is not None
and type(self.attr["email"]) is str,
self.attr["genre"] is None or type(self.attr["genre"]) is str,
self.attr["sex"] is None or type(self.attr["sex"]) is str,
self.attr["fan_class"] is None
or type(self.attr["fan_class"]) is str,
self.attr["artist_name"] is None
or type(self.attr["artist_name"]) is str,
self.attr["password"] is not None
and type(self.attr["password"]) is str,
])
def migrate():
# データベースへの接続とカーソルの生成
with DBConnector(dbName=None) as con, con.cursor() as cursor:
# データベース生成
cursor.execute('CREATE DATABASE IF NOT EXISTS db_%s;' %
project.name())
# 生成したデータベースに移動
cursor.execute('USE db_%s;' % project.name())
# テーブル初期化(DROP)
cursor.execute('DROP TABLE IF EXISTS table_amounts;')
# テーブル初期化(CREATE)
cursor.execute("""
CREATE TABLE `table_amounts` (
`amount_id` int(100) unsigned NOT NULL AUTO_INCREMENT,
`ingredient_id` int(100) NOT NULL ,
`cooking_id` int(100) NOT NULL ,
`ingredient_number` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`amount_id`),
UNIQUE KEY (`ingredient_id`,`cooking_id`)
); """)
# データの追加
cursor.execute("""
INSERT INTO table_amounts (ingredient_id, cooking_id, ingredient_number)
VALUES ('6','1','100g'),('7','1','1個'),('8','1','1/4本'),('9','1','1/4個'),('10','1','1/4袋'),('11','1','100cc'),('12','1','大1'),('13','1','大1'),('14','1','大1'),('15','1','大1/4')
,('16','2','250g'),('17','2','1/2かけら'),('18','2','適量'),('19','2','小さじ1'),('20','2','適量'),('21','2','適量')
,('5','3','一枚'),('9','3','少々'),('11','3','70cc'),('22','3','60g'),('23','3','大さじ2'),('24','3','半分'),('25','3','一枚'),('26','3','適量'),('27','3','一枚'),('47','3','ひとつまみ'),('48','3','一握り')
,('9','4','1/4個'),('17','4','5mm'),('18','4','少々'),('21','4','1/2個'),('26','4','小さじ1'),('28','4','80g'),('29','4','一杯分'),('30','4','5mm'),('31','4','小さじ1'),('32','4','小さじ1'),('33','4','小さじ1/4')
,('9','5','1個'),('11','5','150cc'),('26','5','大さじ1'),('34','5','1かけ'),('35','5','大さじ1'),('36','5','大さじ1'),('48','5','ひとつかみ')
,('11','6','120cc'),('12','6','小さじ2'),('13','6','小さじ1'),('17','6','一片'),('28','6','50g'),('29','6','150g~200g'),('30','6','少々'),('37','6','1/2丁'),('38','6','小さじ1'),('39','6','大さじ1'),('40','6','少々'),('41','6','小さじ1'),('42','6','適量')
,('1','7','M2個'),('9','7','1/2個'),('13','7','小さじ1/4'),('14','7','小さじ1'),('18','7','各少々'),('24','7','1個'),('26','7','大さじ5'),('29','7','300g'),('43','7','3本'),('44','7','適量'),('45','7','小さじ1/2'),('46','7','5g')
""")
con.commit()
def delete(self):
if self.attr["id"] == None: return None
with DBConnector(dbName='db_%s' % project.name()) as con, con.cursor() as cursor:
# データの削除(DELETE)
cursor.execute("""
DELETE FROM table_wishList
WHERE id = %s; """,
(self.attr["id"],))
con.commit()
return self.attr["id"]
def _db_save_delete(self):
with DBConnector(dbName='db_%s' %
project.name()) as con, con.cursor() as cursor:
# データの削除(DELETE)
cursor.execute(
"""
DELETE FROM table_users
WHERE user_id = %s; """, (self.attr["user_id"], ))
con.commit()
return True
def artist_all():
with DBConnector(dbName='db_%s' %
project.name()) as con, con.cursor() as cursor:
cursor.execute("""
SELECT * FROM table_user
WHERE artist_name != ''
""")
con.commit()
recodes = cursor.fetchall()
u_list = [user.find(recode[0]) for recode in recodes]
return u_list
def find_ingredient_idtocooking_id(i_id):
with DBConnector(dbName='db_%s' %
project.name()) as con, con.cursor() as cursor:
cursor.execute(
"""
SELECT cooking_id
FROM table_amounts
WHERE ingredient_id = %s; """, (i_id, ))
con.commit()
recodes = cursor.fetchall()
return recodes
def bmi(user_id):
with DBConnector(dbName='db_%s' % project.name()) as con, con.cursor(MySQLdb.cursors.DictCursor) as cursor:
cursor.execute("""
SELECT bmi FROM healthcan
WHERE user_id = %s
ORDER BY `id` ASC; """,
(user_id,))
con.commit()
recodes = cursor.fetchall()
return recodes
def kind(user_id, kind):
with DBConnector(dbName='db_%s' % project.name()) as con, con.cursor() as cursor:
cursor.execute("""
SELECT id FROM table_wishList
WHERE user_id = %s and kind = %s
ORDER BY `quantity` ASC; """,
(user_id,kind,))
con.commit()
recodes = cursor.fetchall()
w_list = [wishList.find(recode[0]) for recode in recodes]
return w_list
def getPatients():
'''
Get all the patients
Returns:
a list of patient objects
'''
db = DBConnector()
result = db.getPatients()
db.close()
return jsonify(
{'patients': [make_public_patient(patient) for patient in result]})
def serect_by_gender(gender):
with DBConnector(dbName='db_%s' %
project.name()) as con, con.cursor() as cursor:
cursor.execute(
"""
SELECT member_id FROM table_member
WHERE `gender` = %s; """, (gender, ))
con.commit()
recodes = cursor.fetchall()
lm = [member.find(recode[0]) for recode in recodes]
return lm
def find_idtoname(cid):
with DBConnector(dbName='db_%s' % project.name()) as con, con.cursor(
MySQLdb.cursors.DictCursor) as cursor:
# 対応するidをリストで返す
cursor.execute(
"""
SELECT cooking_name FROM table_cookings
WHERE cooking_id = %s ;""", (cid, ))
con.commit()
recode = cursor.fetchall()
cooking_name = recode[0]
return cooking_name
