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