def edit_booking(dataDir):
try:
booking_id = dataDir['booking_id']
is_check_in = dataDir['is_check_in']
check_in = dataDir['check_in']
check_out = dataDir['check_out']
room_num = dataDir['room_num']
room_type = dataDir['room_type']
breakfast = dataDir['breakfast']
adult_num = dataDir['adult_num']
child_num = dataDir['child_num']
extra_text = dataDir['extra_text']
first_name = dataDir['first_name']
last_name = dataDir['last_name']
phone = dataDir['phone']
cursor = connection.cursor()
sqlStrs = [
f"update page_booking set is_check_in={is_check_in}, check_in='{check_in}', check_out='{check_out}' where booking_id='{booking_id}'",
f"update page_booking_rooms set room_num={room_num} where booking_id='{booking_id}'",
f"update page_book_request set room_type='{room_type}',breakfast='{breakfast}',adult_num='{adult_num}',child_num='{child_num}',baby_num='0',extra_text='{extra_text}' where booking_id='{booking_id}'",
f"update page_customer_info set first_name='{first_name}',last_name='{last_name}' where booking_id='{booking_id}'",
f"update page_customer_phone set phone='{phone}' where booking_id='{booking_id}'"
]
for sqlStr in sqlStrs:
cursor.execute(sqlStr)
cursor.fetchall()
connection.commit()
connection.close()
return True
except:
connection.rollback()
connection.close()
return False
def delete_booking(dataDir):
try:
cursor = connection.cursor()
booking_id = dataDir["booking_id"]
table = [
"page_booking", "page_booking_rooms", "page_book_request",
"page_customer_info", "page_customer_phone",
"page_member_customer", "page_booking_parking", "page_bill",
"page_invoice"
]
sqlStrs = []
for sql in table:
sqlStrs.append(
f"delete from {sql} where booking_id='{booking_id}'")
car_numbers = []
sqlStr = f"select car_number from page_booking_parking where booking_id='{booking_id}'"
cursor.execute(sqlStr)
result = cursor.fetchall()
for car in result:
sqlStrs.append(
f"delete from page_parking where car_number = '{car[0]}'")
for sqlStr in sqlStrs:
cursor.execute(sqlStr)
cursor.fetchall()
connection.commit()
connection.close()
return True
except:
connection.rollback()
connection.close()
return False
def insert_parking(dataDir):
# car_number room_num spot team_name
try:
car_number = dataDir["car_number"]
room_num = dataDir["room_num"]
spot = dataDir["spot"]
team_name = dataDir["team_name"]
cursor = connection.cursor()
sqlStr = f"select booking_id from page_booking_rooms where room_num = {room_num}"
cursor.execute(sqlStr)
result = cursor.fetchall()
if (len(result) != 1):
raise ValueError
booking_id = str(result[0][0])
sqlStr = f"select section from page_team where team_name='{team_name}'"
cursor.execute(sqlStr)
result = cursor.fetchall()
if (not result):
raise ValueError
sqlStr = f"insert into page_parking(car_number,team_name,spot) values('{car_number}','{team_name}','{spot}')"
cursor.execute(sqlStr)
cursor.fetchall()
sqlStr = f"insert into page_booking_parking(booking_id,car_number) values('{booking_id}','{car_number}')"
cursor.execute(sqlStr)
cursor.fetchall()
return True
except:
connection.rollback()
connection.close()
return False
def complete_purchase(dataDir):
try:
cursor = connection.cursor()
purchase_id = dataDir["purchase_id"]
sqlStr = f"update page_purchase_slip set is_purchase=1 where purchase_id = '{purchase_id}'"
cursor.execute(sqlStr)
cursor.fetchall()
sqlStr = f"select product_id, count from page_purchase_list where purchase_id = '{purchase_id}'"
cursor.execute(sqlStr)
result = cursor.fetchall()
for data in result:
sqlStr = f"select count from page_in_storage where product_id='{data[0]}'"
cursor.execute(sqlStr)
count = cursor.fetchall()[0][0]
sqlStr = f"update page_in_storage set count={count+data[1]} where product_id='{data[0]}'"
cursor.execute(sqlStr)
cursor.fetchall()
connection.commit()
connection.close()
return True
except:
connection.rollback()
connection.close()
return False
def WantToWatchActions(request):
user = request.user.id
# user watched the movie
if 'watched' in request.POST:
user = request.user.id
movie = request.POST.get('watched')
try:
with connection.cursor() as cursor:
cursor.callproc('SetToWatched', [user, movie])
except Exception as E:
print(str(E))
connection.rollback()
return redirect('rate')
# user wants to remove from watchlist
elif 'delete' in request.POST:
user = request.user.id
movie = request.POST.get('delete')
try:
with connection.cursor() as cursor:
cursor.callproc('DeleteFromWatchlist', [user, movie])
except Exception as E:
print(str(E))
connection.rollback()
return redirect('wanttowatch')
watchlist = sql_query('WantToWatch', user)
return render(request, 'wanttowatch.html', {'watchlist': watchlist})
def get_instance_info():
cursor = connection.cursor()
query = "select instance_to_parse.form_id_string,instance_to_parse.form_instance_id,instance_to_parse.form_id_int from instance_to_parse where is_new=TRUE "
#in (select instance_id from approval_instanceapproval where status like 'Approved')
try:
cursor.execute(query)
form_informations = cursor.fetchall()
rowcount = cursor.rowcount
except Exception as e:
print e
connection.rollback()
form_info_json = {}
#print form_informations
for info in form_informations:
data = {}
form_id = int(info[2])
#print form_id
try:
xform = get_object_or_404(XForm, pk=form_id)
user_id = xform.user_id
owner = get_object_or_404(User, pk=user_id)
data['username'] = str(owner.username)
data['xform_id_string'] = str(xform.id_string)
form_info_json[str(info[1])] = data
except Exception as e:
print e
connection.rollback()
#print owner.username
cursor.close()
return json.dumps(form_info_json)
def BookListView(request):
# books = Book.objects.all()
try:
cursor = connection.cursor()
strSql = "SELECT code, name, author FROM bookstore_book"
result = cursor.execute(strSql)
datas = cursor.fetchall()
connection.commit()
connection.close()
books = []
for data in datas:
row = {'code': data[0],
'name': data[1],
'author': data[2]}
books.append(row)
except:
connection.rollback()
print("Failed selecting in BookListView")
return render(request, 'book_list.html', {'books': books})
def delete_staff(dataDir):
try:
staff_id = dataDir["staff_id"]
sqlStr1 = f"delete from page_staff where staff_id='{staff_id}'"
sqlStr2 = f"delete from page_staff_info where staff_id='{staff_id}'"
sqlStr3 = f"delete from page_staff_address where staff_id='{staff_id}'"
sqlStr4 = f"delete from page_staff_working_info where staff_id='{staff_id}'"
sqlStr5 = f"delete from page_staff_day_off_info where staff_id='{staff_id}'"
sqlStr6 = f"delete from page_team_staff where staff_id='{staff_id}'"
cursor1 = connection.cursor()
cursor1.execute(sqlStr1)
cursor1.fetchall()
cursor2 = connection.cursor()
cursor2.execute(sqlStr2)
cursor2.fetchall()
cursor3 = connection.cursor()
cursor3.execute(sqlStr3)
cursor3.fetchall()
cursor4 = connection.cursor()
cursor4.execute(sqlStr4)
cursor4.fetchall()
cursor5 = connection.cursor()
cursor5.execute(sqlStr5)
cursor5.fetchall()
cursor6 = connection.cursor()
cursor6.execute(sqlStr6)
cursor6.fetchall()
connection.commit()
connection.close()
return True
except:
connection.rollback()
connection.close()
return False
return False
def grab_workflows(instance=None, connection_galaxy=None):
# Delete old references to workflows
Workflow.objects.all().delete()
# checks to see if an existing galaxy connection, otherwise create a connection
if (connection_galaxy is None):
print("instance is none")
#get connection
instance = Instance.objects.all()[0]
connection_galaxy = instance.get_galaxy_connection()
#get all your workflows
workflows = connection_galaxy.get_complete_workflows()
#for each workflow, create a core Workflow object and its associated WorkflowDataInput objects
for workflow in workflows:
workflow_dict = {
'name': workflow.name,
'internal_id': workflow.identifier
#'visibility': 2 #give public visibility for now
}
w = Workflow(**workflow_dict)
try:
w.save()
inputs = workflow.inputs
for input in inputs:
input_dict = {
'name': input.name,
'internal_id': input.identifier
}
i = WorkflowDataInput(**input_dict)
i.save()
w.data_inputs.add(i)
except:
connection.rollback()
def get_room(room_type=""):
try:
cursor = connection.cursor()
#sqlStr = "select staff_id, first_name, last_name, rank,depart_id, status,bank,account, phone,wide_area_unit,basic_unit,street,si_gu,eub_myeon, building_number, detail_address, team_name from page_staff natural join (select * from page_team_staff natural join (select page_staff_info.staff_id, first_name, last_name, bank,account,phone,wide_area_unit,basic_unit,street,si_gu,eub_myeon,building_number,detail_address from page_staff_address inner join page_staff_info on page_staff_info.staff_id = page_staff_address.staff_id))"
sqlStr = "select room_type, price, mem_limit, photo_url, bed_type, bed_num from page_room_type natural join page_room_type_bed"
result = cursor.execute(sqlStr)
datas = cursor.fetchall()
output_data = []
for data in datas:
output_data.append({
'room_type': data[0],
'price': data[1],
'mem_limit': data[2],
'photo_url': data[3],
'bed_type': data[4],
'bed_num': data[5]
})
sqlStr = "select count(room_num), room_type from (select room_num, room_type from page_rooms where room_num not in (select room_num from page_booking_rooms)) group by room_type"
cursor.execute(sqlStr)
result = cursor.fetchall()
output2 = []
for data in result:
output2.append({"room_num": data[0], "room_type": data[1]})
return output_data, output2
except:
connection.rollback()
connection.close()
return None
def get(self, request):
results = []
try:
cursor = connection.cursor()
strSql = "SELECT * FROM crawled_data_playerstatistics WHERE name LIKE 'Son Heung-Min%';"
cursor.execute(strSql)
datas = cursor.fetchall()
for data in datas:
item = {
'id': data[0],
'specific_id': data[1],
'League': data[2],
'name': data[3],
'age': data[4],
'position': data[5],
'Games': data[6],
'Goals': data[7],
'Assists': data[8],
'MotM': data[11],
'Rating': data[12],
}
results.append(item)
connection.commit()
connection.close()
except:
connection.rollback()
print("Failed")
return JsonResponse(results, safe=False)
def get(self, request):
results = []
try:
cursor = connection.cursor()
strSql = f"SELECT * FROM crawled_data_clubvalue;"
cursor.execute(strSql)
datas = cursor.fetchall()
for data in datas:
item = {
'id': data[0],
'specific_id': data[1],
'ranking': data[2],
'club_image': data[3],
'name': data[4],
'Competition': data[5],
'value': data[6],
}
results.append(item)
connection.commit()
connection.close()
except:
connection.rollback()
print("Failed")
return JsonResponse(list(results), safe=False)
def get(self, request, name):
results = []
try:
cursor = connection.cursor()
strSql = f"SELECT * FROM crawled_data_playervalue WHERE name LIKE '%{name}%'"
cursor.execute(strSql)
datas = cursor.fetchall()
for data in datas:
item = {
'id': data[0],
'specific_id': data[1],
'_position': data[2],
'ranking': data[3],
'player_image': data[4],
'name': data[5],
'position': data[6],
'age': data[7],
'value': data[8],
}
results.append(item)
connection.commit()
connection.close()
except:
connection.rollback()
print("Failed")
return JsonResponse(list(results), safe=False)
def get(self, request, name):
stats = ClubStatistics.objects.all()
results = []
try:
cursor = connection.cursor()
strSql = f"SELECT * FROM crawled_data_clubstatistics WHERE name LIKE '%{name}%'"
cursor.execute(strSql)
datas = cursor.fetchall()
for data in datas:
item = {
'id': data[0],
'specific_id': data[1],
'League': data[2],
'ranking': data[3],
'state': data[4],
'name': data[5],
'play': data[6],
'win': data[7],
'draw': data[8],
'lose': data[9],
'gf': data[10],
'gd': data[11],
'ga': data[12],
'pts': data[13],
}
results.append(item)
connection.commit()
connection.close()
except:
connection.rollback()
print("Failed")
return JsonResponse(list(results), safe=False)
def exec_query(self, params):
# 返回结果data
data = {}
cursor = connection.cursor()
if params.has_key('sql'):
sql = params['sql']
if sql == '':
data['code'] = 500
data['res'] = 'query is empty..'
else:
try:
# 执行sql语句
cursor.execute(sql)
# 提交到数据库执行
connection.commit()
data['code'] = 200
data['res'] = 'execute done..'
except Exception, e:
# Rollback in case there is any error
connection.rollback()
data['code'] = 500
data['res'] = format(e)
finally:
# 最终关闭数据库连接
connection.close()
def query_one(self, params):
# 返回结果data
data = {}
cursor = connection.cursor()
if params.has_key('sql'):
sql = params['sql']
if sql == '':
data['code'] = 500
data['res'] = 'query is empty..'
else:
try:
# 执行sql语句
cursor.execute(sql)
index = cursor.description
result = []
for res in cursor.fetchone():
row = {}
for i in range(len(index) - 1):
row[index[i][0]] = res[i]
result.append(row)
data['code'] = 200
data['res'] = result
except Exception, e:
# Rollback in case there is any error
connection.rollback()
data['code'] = 500
data['res'] = format(e)
finally:
def query_n(self, params):
# 返回结果data
# limit 必须大于0且为整数
data = {}
cursor = connection.cursor()
if params.has_key('sql') and params.has_key('limit'):
sql = params['sql']
limit = params['limit']
if sql == '':
data['code'] = 500
data['res'] = 'query is empty..'
elif int(limit) > 0 and limit.isdigit():
try:
# 执行sql语句
cursor.execute(sql)
index = cursor.description
result = []
for res in cursor.fetchmany(int(limit)):
row = {}
for i in range(len(index) - 1):
row[index[i][0]] = res[i]
result.append(row)
data['code'] = 200
data['res'] = result
except Exception, e:
# Rollback in case there is any error
connection.rollback()
data['code'] = 500
data['res'] = format(e)
finally:
def add_offer(request):
if request.POST:
offer_id = request.POST['offer_id']
percentage = request.POST['percentage']
min_amt = request.POST['min_amt']
cursor = connection.cursor()
cursor.execute("SELECT * FROM offer WHERE offer_id='" + offer_id + "';")
data = cursor.fetchone()
if data is not None:
messages.error(request, 'The offer ID already exists!')
return HttpResponseRedirect(request.META.get('HTTP_REFERER'))
else:
try:
if offer_id and percentage and min_amt:
q = "INSERT INTO offer VALUES ('" + offer_id + "'," + percentage + "," + min_amt + ");"
cursor.execute(q)
connection.commit()
except Exception as e:
print e
connection.rollback()
connection.close()
return HttpResponseRedirect(reverse('offers'))
def insert_purchase(dataDir):
try:
cursor = connection.cursor()
staff_id = dataDir["staff_id"]
product_id_list = dataDir["product_id"]
count_list = dataDir["count"]
now = datetime.datetime.now()
purchase_id = now.strftime('%Y%m%d%H%M%S')
order_date = now.strftime('%Y-%m-%d')
delivery_date = (now +
datetime.timedelta(days=7)).strftime('%Y-%m-%d')
sqlStr = f"insert into page_purchase_slip(purchase_id,delivery_date,is_purchase,staff_id,order_date) values('{purchase_id}','{delivery_date}',0,'{staff_id}','{order_date}')"
cursor.execute(sqlStr)
cursor.fetchall()
for i in range(len(product_id_list)):
sqlStr = f"insert into page_purchase_list(purchase_id,product_id,count) values('{purchase_id}','{product_id_list[i]}',{count_list[i]})"
cursor.execute(sqlStr)
cursor.fetchall()
connection.commit()
connection.close()
return True
except:
connection.rollback()
connection.close()
return False
def decrement(request):
dish_id = request.POST['dish_id']
order_id = request.POST['order_id']
rate = request.POST['rate']
nunits = request.POST['nunits']
print str(nunits > 1)
if int(nunits) > 1:
try:
cursor = connection.cursor()
cursor.execute("update orders set total=total-" + str(rate) +
" where order_id=" + str(order_id) + ";")
cursor.execute(
"update order_meals set units=units-1 where order_id=" +
str(order_id) + " and dish_id=" + str(dish_id) + ";")
connection.commit()
except Exception as e:
print e
connection.rollback()
else:
try:
cursor = connection.cursor()
cursor.execute("update orders set total=total-" + str(rate) +
" where order_id=" + str(order_id) + ";")
cursor.execute("delete from order_meals where order_id=" +
str(order_id) + " and dish_id=" + str(dish_id) +
";")
connection.commit()
except Exception as e:
print e
connection.rollback()
return HttpResponseRedirect(reverse('checkout'))
def manage_depart(request):
if (request.method == "POST"):
try:
depart_id = request.POST.get("depart_id") + ','
depart_name = request.POST.get("depart_name") + ','
position = request.POST.get("position")
cursor = connection.cursor()
sqlStr = "insert into page_depart values(" + depart_id + depart_name + position + ")"
result = cursor.execute(sqlStr)
cursor.fetchall()
connection.commit()
connection.close()
except:
connection.rollback()
connection.close()
cursor = connection.cursor()
sqlStr = "select * from page_depart"
result = cursor.execute(sqlStr)
dataSet = cursor.fetchall()
connection.close()
datas = []
for data in dataSet:
datas.append(
str(data).replace("'",
'').replace('(',
'').replace(')',
'').replace(',', ' /'))
return render(request, 'develop/manage_depart.html', {'datas': datas})
def archive(self):
# store zipped version of /opt/websafety/etc to the shared folder
zip_dir = os.path.join(Paths.var_dir(), "cluster")
if not os.path.exists(zip_dir):
os.makedirs(zip_dir)
# copy the database
backup_db_file = os.path.join(Paths.etc_dir(), "config.sqlite")
db_file = os.path.join(Paths.var_dir(), "db", "config.sqlite")
try:
c = connection.cursor()
c.execute("BEGIN IMMEDIATE")
shutil.copyfile(db_file, backup_db_file)
finally:
c.close()
connection.rollback()
# remove all old configs, only last 10 will stay
files = [
f for f in os.listdir(zip_dir)
if os.path.isfile(os.path.join(zip_dir, f))
]
for f in files[:-9]:
os.remove(os.path.join(zip_dir, f))
# source and target paths
etc_dir = Paths.etc_dir()
zip_file = os.path.join(
zip_dir, "%s-%s.zip" % (str(self.timestamp), str(self.version)))
# zip it
zipdir(etc_dir, zip_file)
def grab_workflows(instance=None, connection_galaxy=None):
# Delete old references to workflows
Workflow.objects.all().delete()
# checks to see if an existing galaxy connection, otherwise create a connection
if (connection_galaxy is None):
print ("instance is none")
#get connection
instance = Instance.objects.all()[0]
connection_galaxy = instance.get_galaxy_connection()
#get all your workflows
workflows = connection_galaxy.get_complete_workflows()
#for each workflow, create a core Workflow object and its associated WorkflowDataInput objects
for workflow in workflows:
workflow_dict = {
'name': workflow.name,
'internal_id': workflow.identifier
#'visibility': 2 #give public visibility for now
}
w = Workflow(**workflow_dict)
try:
w.save()
inputs = workflow.inputs
for input in inputs:
input_dict = {
'name': input.name,
'internal_id': input.identifier
}
i = WorkflowDataInput(**input_dict)
i.save()
w.data_inputs.add(i)
except:
connection.rollback()
def get_member(member_id=""):
try:
cursor = connection.cursor()
#sqlStr = "select staff_id, first_name, last_name, rank,depart_id, status,bank,account, phone,wide_area_unit,basic_unit,street,si_gu,eub_myeon, building_number, detail_address, team_name from page_staff natural join (select * from page_team_staff natural join (select page_staff_info.staff_id, first_name, last_name, bank,account,phone,wide_area_unit,basic_unit,street,si_gu,eub_myeon,building_number,detail_address from page_staff_address inner join page_staff_info on page_staff_info.staff_id = page_staff_address.staff_id))"
sqlStr = "select member_id, membership, birth, is_sms, password, email, first_name, last_name, phone, point from page_member_info"
result = cursor.execute(sqlStr)
datas = cursor.fetchall()
output_data = []
for data in datas:
output_data.append({
'member_id': data[0],
'membership': data[1],
'birth': data[2],
'is_sms': data[3],
'password': data[4],
'email': data[5],
'first_name': data[6],
'last_name': data[7],
'phone': data[8],
'point': data[9]
})
if (member_id != ""):
for data in output_data:
if (data["member_id"] == member_id):
return data
raise ValueError
return output_data
except:
connection.rollback()
connection.close()
return None
def insert_purchase(dataDir):
try:
booking_id = dataDir["booking_id"]
product_id = dataDir["name"]
order_time = datetime.datetime.now().strftime(
"%Y-%m-%d %H:%M:%S:%f")[:-4]
count = dataDir["count"]
cursor = connection.cursor()
sqlStr = f"select name from page_product where product_id='{product_id}'"
cursor.execute(sqlStr)
result = cursor.fetchall()
if (len(result) == 0):
raise ValueError
if (not count.isdigit()):
raise ValueError
sqlStr = f"insert into page_invoice(booking_id,product_id,count,order_time,offer_time,is_payment) values('{booking_id}','{product_id}',{count},'{order_time}','',0)"
cursor.execute(sqlStr)
cursor.fetchall()
connection.commit()
connection.close()
return True
except:
connection.rollback()
connection.close()
return False
def BookDetailView(request, code):
# book1 = get_object_or_404(Book, code=code)
# book2 = Book.objects.get(code=code)
try:
cursor = connection.cursor()
strSql = "SELECT code, name, author, price, url FROM bookstore_book WHERE code = (%s)"
result = cursor.execute(strSql, (code,))
datas = cursor.fetchall()
connection.commit()
connection.close()
book = {'code': datas[0][0],
'name': datas[0][1],
'author': datas[0][2],
'price': datas[0][3],
'url': datas[0][4]}
except:
connection.rollback()
print("Failed selecting in BookListView")
return render(request, 'book_detail.html', {'book': book})
def get_dict(query_string, show_info=False, conn=None):
connection = get_connection(conn)
cursor = connection.cursor()
try:
cursor.execute(query_string)
except Exception, e:
connection.rollback()
raise e
def get_list_extra(query_string, conn=None):
connection = get_connection(conn)
cursor = connection.cursor()
try:
cursor.execute(query_string)
except Exception, e:
connection.rollback()
raise e
def evSearch(request):
if request.method == 'POST':
user_lat = request.POST['lat']
user_lng = request.POST['lng']
try:
cursor = connection.cursor()
strSql = "select evst.statNm,evst.addr,evst.lat,evst.lng,evst.useTime,evst.busiCall,descInfo,congestion, \
(6371*acos(cos(radians(" + user_lat + "))*cos(radians(evst.lat))*cos(radians(evst.lng)-radians(" + user_lng + "))+sin(radians(" + user_lat + "))*sin(radians(evst.lat))))AS distance \
from ev_station evst \
join ev_real_time evtm on(evst.evsn=evtm.evsn),\
(select c.evSn, group_concat(des SEPARATOR '\n') as descInfo \
from (select a.evSn, a.chgerId, concat('기기 번호 : ', a.chgerId , ' ( 상태 : ' , (select codeName from ev.ev_code_inf where codeId = a.stat) , ', 충전타입 : ' \
,GROUP_CONCAT((select codeName from ev.ev_code_inf where codeId = b.chgerType) SEPARATOR ','),')') as des \
from ev_station_status a,ev_station_chgertype b \
where a.evSn = b.evSn \
group by a.evSn, a.chgerId) c \
group by c.evSn) info \
where evst.evSn = info.evSn \
HAVING distance <= 2 \
ORDER BY distance;"
result = cursor.execute(strSql)
stations = cursor.fetchall()
print('stations - ', stations)
connection.commit()
connection.close()
list = []
# cnt = 0
for station in stations:
conlevel = ""
if station[7] < 0.5:
conlevel = 'green-dot.png'
print(conlevel)
elif station[7] < 0.99:
conlevel = 'yellow-dot.png'
print(conlevel)
else:
conlevel = 'red-dot.png'
print(conlevel)
row = {'statNm': station[0],
'addr': station[1],
'lat': station[2],
'lng': station[3],
'useTime': station[4],
'busiCall': station[5],
'descInfo': station[6],
'congestion': station[7],
'distance': station[8],
'conlevel': conlevel}
list.append(row)
# cnt = cnt + 1
# if cnt == 5 :
# break
for a in list:
print("check - ", a)
except:
connection.rollback()
print('Failed selecting in stations')
return JsonResponse(list, safe=False)
def sql_query(proc, param):
try:
with connection.cursor() as cursor:
cursor.callproc(proc, [param])
results = cursor.fetchall()
except Exception as E:
print(str(E))
connection.rollback()
return results
def checkCourseTable(self):
if self.request.session['is_login'] != True or self.request.session[
'role'] != STUDENT_ROLE:
self._init_response()
return self._get_response(UNAUTHORIZED_AS_STUDENT)
try:
print('user_id' in self.data)
user_id = self.data['user_id']
except Exception as error:
self._init_response()
return self._get_response(POST_ARG_ERROR, -1)
try:
cursor = connection.cursor()
#
check_courses_sql = 'select * from section natural join course natural join teaches natural join takes natural join instructor natural join exam where student_id = %s'
cursor.execute(check_courses_sql, (user_id, ))
raw_courses_taken = sql_util.dictfetchall(cursor)
total_num = len(raw_courses_taken)
sections = []
for row in raw_courses_taken:
tmp = {
'title': row['title'],
'course_id': row['course_id'],
'section_id': row['section_id'],
'dept_name': row['dept_name'],
'instructor_name': row['instructor_name'],
'credits': row['credits'],
'classroom_no': row['classroom_no'],
'day': row['day'],
'start': row['start'],
'end': row['end'],
'exam_classroom_no': row['exam_classroom_no'],
'exam_day': row['exam_day'],
'exam_type': row['type'],
'start_time': row['start_time'],
'end_time': row['end_time'],
'open_note_flag': row['open_note_flag']
}
sections.append(tmp)
res = {
'total_num': total_num,
'sections': sections,
}
self.response.update(res)
self._init_response()
return self._get_response(SHOW_COURSE_TABLE, 1)
except Exception as error:
print(error)
traceback.print_exc()
connection.rollback()
self._init_response()
return self._get_response(SERVER_ERROR, -1)
def update_sql(sql):
try:
cursor = connection.cursor()
cursor.execute(sql)
return EXECUTE_SUCCESSFULLY
except Exception as e:
logger.error(sql)
logger.error(e)
connection.rollback()
return UPDATE_SQL_ERROR
def get_staff(staff_id=""):
try:
cursor = connection.cursor()
#sqlStr = "select staff_id, first_name, last_name, rank,depart_id, status,bank,account, phone,wide_area_unit,basic_unit,street,si_gu,eub_myeon, building_number, detail_address, team_name from page_staff natural join (select * from page_team_staff natural join (select page_staff_info.staff_id, first_name, last_name, bank,account,phone,wide_area_unit,basic_unit,street,si_gu,eub_myeon,building_number,detail_address from page_staff_address inner join page_staff_info on page_staff_info.staff_id = page_staff_address.staff_id))"
sqlStr = "select staff_id, first_name, last_name, rank,depart_id, status,bank,account, phone,wide_area_unit,basic_unit,street,si_gu,eub_myeon, building_number, detail_address, team_name,depart_name,position from page_depart natural join (select staff_id, first_name, last_name, rank,depart_id, status,bank,account, phone,wide_area_unit,basic_unit,street,si_gu,eub_myeon, building_number, detail_address, team_name from page_staff natural join (select * from page_team_staff natural join (select page_staff_info.staff_id, first_name, last_name, bank,account,phone,wide_area_unit,basic_unit,street,si_gu,eub_myeon,building_number,detail_address from page_staff_address inner join page_staff_info on page_staff_info.staff_id = page_staff_address.staff_id)))"
result = cursor.execute(sqlStr)
datas = cursor.fetchall()
output_data = []
for data in datas:
output_data.append({
'staff_id': data[0],
'first_name': data[1],
'last_name': data[2],
'rank': data[3],
'depart_id': data[4],
'status': data[5],
'bank': data[6],
'account': data[7],
'phone': data[8],
'wide_area_unit': data[9],
'basic_unit': data[10],
'street': data[11],
'si_gu': data[12],
'eub_myeon': data[13],
'buildding_number': data[14],
'detail_address': data[15],
'team': data[16],
'depart_name': data[17],
'depart_position': data[18],
'rooms': []
})
sqlStr = f"select room_num from page_rooms where team_name='{data[16]}'"
cursor.execute(sqlStr)
result = cursor.fetchall()
for room_number in result:
output_data[-1]["rooms"].append(room_number[0])
output_data[-1]["rooms"] = str(
output_data[-1]["rooms"]).replace('[',
'').replace(']', '')
connection.close()
if (staff_id != ""):
for data in output_data:
if (data["staff_id"] == staff_id):
return data
raise ValueError
return output_data
except:
connection.rollback()
connection.close()
return None
def test_commit(self):
"""
Users are allowed to commit and rollback connections.
"""
# The starting value is False, not None.
self.assertIs(connection._dirty, False)
list(Mod.objects.all())
self.assertTrue(connection.is_dirty())
connection.commit()
self.assertFalse(connection.is_dirty())
list(Mod.objects.all())
self.assertTrue(connection.is_dirty())
connection.rollback()
self.assertFalse(connection.is_dirty())
def _insert_query(query):
cursor = connection.cursor()
id = None
try:
cursor.execute(query)
id = cursor.lastrowid
cursor.close()
connection.commit()
except Exception as e:
connection.rollback()
connection.close()
return id
def _update_query(query):
cursor = connection.cursor()
try:
cursor.execute(query)
cursor.close()
connection.commit()
result = True
except Exception as e:
connection.rollback()
result = False
connection.close()
return result
def test_save(self):
# First, create a SubMod, then try to save another with conflicting
# cnt field. The problem was that transactions were committed after
# every parent save when not in managed transaction. As the cnt
# conflict is in the second model, we can check if the first save
# was committed or not.
SubMod(fld=1, cnt=1).save()
# We should have committed the transaction for the above - assert this.
connection.rollback()
self.assertEqual(SubMod.objects.count(), 1)
try:
SubMod(fld=2, cnt=1).save()
except IntegrityError:
connection.rollback()
self.assertEqual(SubMod.objects.count(), 1)
self.assertEqual(Mod.objects.count(), 1)
def test_commit_unless_managed_in_managed(self):
cursor = connection.cursor()
connection.enter_transaction_management()
transaction.managed(True)
cursor.execute("INSERT into transactions_regress_mod (fld) values (2)")
connection.commit_unless_managed()
self.assertTrue(connection.is_dirty())
connection.rollback()
self.assertFalse(connection.is_dirty())
self.assertEqual(len(Mod.objects.all()), 0)
connection.commit()
connection.leave_transaction_management()
self.assertFalse(connection.is_dirty())
self.assertEqual(len(Mod.objects.all()), 0)
self.assertTrue(connection.is_dirty())
connection.commit_unless_managed()
self.assertFalse(connection.is_dirty())
self.assertEqual(len(Mod.objects.all()), 0)
def set_search_path(self, cursor):
"""
Actual search_path modification for the cursor. Database will
search schemata from left to right when looking for the object
(table, index, sequence, etc.).
"""
if self.schema_name is None:
raise ImproperlyConfigured("Database schema not set. Did your forget "
"to call set_schema() or set_tenant()?")
_check_identifier(self.schema_name)
connection.enter_transaction_management()
try:
if self.schema_name == 'public':
cursor.execute('SET search_path = public')
else:
cursor.execute('SET search_path = %s', [self.schema_name]) #, public
except utils.DatabaseError, e:
connection.rollback()
raise utils.DatabaseError(e.message)
