def save_to_db(filename,id):
connection =sqlite3.connect('/home/pgautam/positServer/database/db',isolation_level=None)
execstring = "insert into posit_images (filename, recordid) values (%s,%d)"%filename%id
print execstring
connection.execute(execstring)
connection.commit()
connection.close()
def add_assesment(request_dict):
cur = connection.cursor()
event_base_id = 1
cur.execute("SELECT event_id FROM events_table WHERE title=" + "'" + request_dict['event_name'] + "'")
for i in cur:
event_base_id = int(i[0])
cur.execute("SELECT (event_id, vk_id) FROM ratings_table WHERE\
event_id=" + "'" + str(event_base_id) + "'AND vk_id='" + request_dict['id'] + "'")
check_array = []
for i in cur:
if (len(i) != 0):
check_array.append(i[0])
assesment_new = float(request_dict['asses'])
if (len(check_array) != 0):
cur.execute(
"""UPDATE ratings_table SET assessment=""" + str(assesment_new) +
""" WHERE event_id=""" + str(event_base_id) + """ AND """ +
"""vk_id=""" + str(request_dict['id']) + """;""")
if (len(check_array) == 0):
cur.execute(
"""INSERT INTO ratings_table (event_id, vk_id, assessment, system_asses)
VALUES (%(event_id)s, %(vk_id)s, %(assessment)s, %(system_asses)s);""",
{'event_id': event_base_id, 'vk_id': int(request_dict['id']), \
'assessment': assesment_new, 'system_asses': False})
connection.commit()
def _execute_postgis(cursor):
"""
Run PostGIS commands on the database for cleaning up geometry objects and calculating relationships
between different locations.
"""
try:
with open(_IMPORT_DIR + 'impdjango.sql') as f:
query = comment = ''
for line in f:
if line.startswith("--"):
comment = line.replace("--", "").strip()
if 'Vacuum Analyse' in comment:
_vacuum_analyse(cursor)
continue
query += line
if ";" in line:
print(comment)
with Timer():
cursor.execute(query)
connection.commit()
query = ''
except IOError:
sys.stderr.write('Could not open impdjango.sql. Please make sure it can be found in ' + _IMPORT_DIR + '\n')
sys.exit(1)
def unsubscribeThread(users_email, id):
cursor = connection.cursor()
try:
cursor.execute(
"INSERT INTO subscribers (user_id, thread_id, subscribed) "
"VALUES ((SELECT id FROM users WHERE email = %s), %s, FALSE) "
"ON DUPLICATE KEY UPDATE subscribed=FALSE ",
(users_email, int(id)),
)
connection.commit()
cursor.execute(
"SELECT "
"threads.id AS thread, "
"users.email AS user "
"FROM threads JOIN subscribers ON threads.id = subscribers.thread_id "
"JOIN users ON subscribers.user_id = users.id "
"WHERE users.email = %s AND threads.id = %s AND subscribers.subscribed = FALSE ",
(users_email, id),
)
thread = dictfetchone(cursor)
cursor.close()
except Exception:
raise
else:
return thread
finally:
cursor.close()
def thread_restore(request):
#logger.error("THREAD:")
main_response = {}
json_response = {}
if request.method == 'POST':
input_params = json.loads(request.body)
thread_id = input_params['thread']
thread = Thread.objects.get(id = thread_id)
thread.isDeleted = False
thread.save(update_fields=['isDeleted'])
cursor = connection.cursor()
cursor.execute("UPDATE subdapp_post SET isDeleted=0 WHERE thread_id=%s",[thread.id])
connection.commit()
main_response = {'code':0}
json_response['thread'] = thread_id
main_response['response'] = json_response;
response = JsonResponse(main_response)
return response
def result(request, query_index):
query_index = int(query_index)
if query_index >= len(required_queries):
raise Http404("Question does not exist")
current = required_queries[query_index]
# opening connection to the database
cur = connection.cursor()
# do the selection
cur.execute(current[1])
connection.commit()
# fetch the result
result_array = cur.fetchall()
# we close everything db related
cur.close()
connection.close()
if len(result_array) == 0:
raise Http404("Empty result..")
# [["Bidon", "Bbb"],["Citron", "ccc"]]
context = {'queries': required_queries, 'query_name': current[0],
'query_result': result_array,
'col_title': current[2]}
return render(request, 'application/result.html', context)
def createSolution(solution, created_user, issue_id):
cursor = connection.cursor()
date = datetime.datetime.now()
cursor.execute(
" insert into solution (solution, created_by, created_date, issue_id) values(%s,%s,%s,%s)",
[solution, created_user, date, issue_id])
connection.commit()
def fix_all_users(self, realm: Realm) -> None:
user_profiles = list(UserProfile.objects.filter(
realm=realm,
is_bot=False,
))
for user_profile in user_profiles:
fix(user_profile)
connection.commit()
def delete_upload_table(pk):
"""Delete the table used to merge data into the workflow with the given
PK. Due to the dual use of the database, the command has to be executed
directly on the DB.
"""
cursor = connection.cursor()
cursor.execute('DROP TABLE "{0}"'.format(create_upload_table_name(pk)))
connection.commit()
def deleteData(request):
c_id = request.POST.getlist('contact_id')[0]
with connection.cursor() as cursor:
cursor.execute('DELETE FROM contact WHERE contact_id = ' + str(c_id) +
';')
connection.commit()
html = render_to_string('deletedData.html')
return HttpResponse(html)
def import_file(filename, Model, encoding='utf-8', batch_size=5000):
reader = csv.DictReader(get_fobj(filename, encoding))
counter = 0
for batch in ipartition(reader, batch_size):
Model.objects.bulk_create([Model(**data) for data in batch])
counter += len(batch)
print(counter)
connection.commit()
def add_user(vk_id):
cur = connection.cursor()
try:
vk_inform = vk_api.user_information(vk_id)
insert_to_table(cur, vk_inform, vk_id)
except:
None
connection.commit()
def fix_all_users(self, realm: Realm) -> None:
user_profiles = list(UserProfile.objects.filter(
realm=realm,
is_bot=False
))
for user_profile in user_profiles:
fix(user_profile)
connection.commit()
def signal_probe_change():
try:
cur = connection.cursor()
cur.execute('NOTIFY {}'.format(postgresql_channel))
connection.commit()
except Exception as db_err:
logger.error("Could not signal probe change: %s", db_err)
connection.close_if_unusable_or_obsolete()
def sqltaskdelete(self, nTaskid):
nsql = "delete a.*,b.* from sql_update_task_info a join sql_check_task_info b on a.task_id=b.task_id where a.task_id=%s"
print nsql
cursor = connection.cursor()
cursor.execute(nsql, [nTaskid])
cursor.close()
connection.commit()
connection.close()
def getListTicketsAvalibles(event):
cursor = connection.cursor()
instruction = "SELECT count(*),location_location.name,location_location.cost, location_location.id FROM tickets_ticket,location_location WHERE location_location.id=tickets_ticket.location_id AND state='Disponible' AND tickets_ticket.event_id="+str(event.id)+" GROUP BY location_location.name,location_location.cost, location_location.id;"
cursor.execute(instruction)
rows = cursor.fetchall()
connection.commit()
connection.close()
return rows
def signup(response):
nombre = ''
apellido = ''
gametag = ''
#email=''
#password=''
#creadoEn= timezone.now()
birth = ''
#id=2
values_to_insert = []
if response.method == "POST":
form = RegisterForm(response.POST)
if form.is_valid():
nombre = form.cleaned_data.get("nombre")
apellido = form.cleaned_data.get("apellido")
gametag = form.cleaned_data.get("username")
birth = form.cleaned_data.get("nacimiento")
form.save()
values_to_insert = [nombre, apellido, gametag, birth]
print(values_to_insert)
#id=+1
else:
form = RegisterForm()
try:
connection = psycopg2.connect(user="******",
password="******",
host="localhost",
port="5432",
database="puzzlevid")
#Create a cursor connection object to a PostgreSQL instance and print the connection properties.
cursor = connection.cursor()
#Display the PostgreSQL version installed
cursor.execute(
"""
INSERT INTO puzzlevid_usuario (nombre,apellido,"gameTag",birth)
VALUES (%s, %s, %s, %s)""", values_to_insert)
#Handle the error throws by the command that is useful when using python while working with PostgreSQL
except (Exception, psycopg2.Error) as error:
print("Error connecting to PostgreSQL database", error)
connection = None
#Close the database connection
finally:
if (connection != None):
connection.commit()
cursor.close()
connection.close()
print("PostgreSQL connection is now closed")
return redirect("/juega")
return render(response, 'register/signup.html', {'form': form})
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 deleteUser(request, username, delete_username, role):
deleteQuery = "DELETE from common_users where username = '******' and role = '%s';" % (
delete_username, role)
deleteLoginQuery = "DELETE from login where username = '******';" % (username)
cursor.execute(deleteQuery)
cursor.execute(deleteLoginQuery)
connection.commit()
successMessage = "User has been successfully deleted!"
return HttpResponseRedirect(reverse('admins:homepage', args=(username, )))
def get(self, request):
course_level = request.query_params.get('course_level')
course_level_letter = '"' + request.query_params.get(
'course_level_letter') + '"'
sql_query = f'\
SELECT dayname(timetable.date_discipline) AS weekday, \
TIME_FORMAT(TIME(timetable.date_discipline), "%H:%i") AS time, \
discipline.name \
FROM discipline \
JOIN education_plan ON discipline.id = education_plan.discipline_id \
JOIN course ON course.id = education_plan.course_id \
AND course.level = {course_level} \
AND course.level_letter = {course_level_letter} \
JOIN timetable ON timetable.education_plan_id = education_plan.id \
AND DATE(timetable.date_discipline) >= FIRST_DAY_OF_WEEK(NOW()) \
AND DATE(timetable.date_discipline) <= LAST_DAY_OF_WEEK(NOW()) \
ORDER BY timetable.date_discipline'
response_json = []
with connection.cursor() as cursor:
cursor.execute("SET lc_time_names = 'ru_RU'")
cursor.execute(sql_query)
connection.commit()
all_row = cursor.fetchall()
prev_day = "NoneDay"
day_object = dict()
for row in all_row:
row_list = list(row)
weekday = row_list[0]
time = row_list[1]
discipline = row_list[2]
day_now = weekday
if prev_day != day_now:
if day_object:
response_json.append(day_object)
day_object = dict()
prev_day = day_now
day_object["weekday"] = prev_day
day_object["disciplines"] = list()
day_object["disciplines"].append({
"time": time,
"discipline": discipline
})
elif prev_day == day_now:
day_object["disciplines"].append({
"time": time,
"discipline": discipline
})
response_json.append(day_object)
return Response(response_json)
def getListTicket(event):
cursor = connection.cursor()
instruction = "SELECT count(*),location_location.name,location_location.cost from tickets_ticket,location_location WHERE location_location.id=tickets_ticket.location_id AND tickets_ticket.event_id=" + str(
event) + " GROUP BY location_location.cost,location_location.name;"
cursor.execute(instruction)
rows = cursor.fetchall()
connection.commit()
connection.close()
return rows
def rightupdate(self, nRightname, nAction, nStatus, nMenuid, nRightid):
nsql = "update menu_rights set rightsname=%s,action=%s,status=%s,menuid=%s,parentid=if((select parentid from menu_info where menuid=%s) is null,'',(select parentid from menu_info where menuid=%s)) where rightsid=%s "
cursor = connection.cursor()
cursor.execute(nsql, [
nRightname, nAction, nStatus, nMenuid, nMenuid, nMenuid, nRightid
])
cursor.close()
connection.commit()
connection.close()
def insert_or_update_cluster_details(params):
"""
insert or update the cluster details in the database
:param params:
:return:
"""
cursor = None
error = False
response = None
try:
cursor = connection.cursor()
user_id = int(params.get('user_id'))
provider_id = int(params.get('provider_id'))
cluster_id = str(params.get('cluster_id'))
a = params.get('cluster_details')
print(a)
# cluster_details = str(base64.b64encode(params.get('cluster_details')))
cluster_details = params.get('cluster_details')
print(cluster_details)
status = params.get('status')
operation = params.get('operation')
if params.get('is_insert'):
created_at = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
cmd = "INSERT INTO public._cb_cp_cluster_details(user_id, provider_id, cluster_id, cluster_details, " \
"status, created_at, operation) VALUES ({user_id},{provider_id},'{cluster_id}','{cluster_details}'" \
",'{status}','{created_at}','{operation}')".format(
user_id=int(user_id), provider_id=int(provider_id),
cluster_id=cluster_id,
cluster_details=cluster_details,
status=status, created_at=created_at,
operation=operation)
cursor.execute(cmd)
connection.commit()
response = 'Success'
else:
# update operation
cmd = "UPDATE public._cb_cp_cluster_details SET status = '{status}', cluster_details = " \
"'{cluster_details}', operation = '{operation}' " \
"where user_id = {user_id} and provider_id = {provider_id} and cluster_id = '{cluster_id}' ".format(
status=status,
cluster_details=cluster_details,
operation=operation,
user_id=user_id,
provider_id=provider_id,
cluster_id=cluster_id
)
cursor.execute(cmd)
connection.commit()
response = 'Success'
except Exception as e:
error = True
response = 'Cannot connect to Database server'
print(e.message)
finally:
if cursor is not None:
cursor.close()
return error, response
def load_result_psql(model_arg):
from django.db import connection
import psycopg2
## vars, somewhat redundant with vars declared in save_results_csv() function
psql_connection = os.environ["PSQL_CONNECTION"]
file_path = os.environ["SAVER_PATH"]
csv_file = file_path + "/tmp/results.csv"
def model_count(model_arg):
if model_arg == "resultstage":
count = str(ResultStage.objects.all().count())
elif model_arg == "resultcheck":
count = str(ResultCheck.objects.all().count())
return count
## truncate table
if model_arg == "resultstage":
truncate_sql = "\"TRUNCATE TABLE results_resultstage CASCADE;\""
elif model_arg == "resultcheck":
truncate_sql = "\"TRUNCATE TABLE results_resultcheck CASCADE;\""
else:
message = "No model name match found"
slackbot(message)
truncate_command = psql_connection + " " + truncate_sql
## ?????????? combine TRUNCATE and COPY commands into one statement? ??????????
message = "About to remove %s from %s table..." % (model_count(model_arg),
model_arg)
slackbot(message)
## pass this to subprocess
call(truncate_command, shell=True)
## should we use the mgmt cmd instead? is one faster? if so, will need to be refactored to account for ResultCheck
# from django.core.management import call_command
# call_command('delete_result_psql', model_arg)
## load data via psql copy method
cursor = connection.cursor()
## should it be the way below or this way from a stackoverflow answer?
# COPY results_resultstage FROM stdin WITH CSV HEADER DELIMITER as ',';
message = "\nAbout to load..."
slackbot(message)
if model_arg == "resultstage":
copy_sql = '''
COPY results_resultstage FROM stdin DELIMITER ',' CSV HEADER;
'''
elif model_arg == "resultcheck":
copy_sql = '''
COPY results_resultcheck FROM stdin DELIMITER ',' CSV HEADER;
'''
with open(csv_file, 'r') as f:
cursor.copy_expert(sql=copy_sql, file=f)
connection.commit()
cursor.close()
message = "%s loaded:\t%s" % (model_arg, model_count(model_arg))
slackbot(message)
def Refresh(cls):
SecurityPriceDetail.Refresh()
cursor = connection.cursor()
try:
cursor.execute(
"REFRESH MATERIALIZED VIEW financeview_holdingdetail;")
connection.commit()
finally:
cursor.close()
def servergetinfo(self, nServerid):
"""
获取服务器信息，代码程序部署在不同的服务器上获取服务器信息的方式是不一样的
"""
nsql="select user_postion from product_stat_postion"
cursor = connection.cursor()
cursor.execute(nsql)
rows = cursor.fetchone()
nUserPostion=rows[0]
nServerSourceId = Server.serversourceid(nServerid)
nSqlinfo = "select server_id, outer_net, inner_net,use_outer_inner ,server_login_user, server_login_pwd, ssh_port from server_info where server_id=%s"
cursorinfo = connection.cursor()
cursorinfo.execute(nSqlinfo, nServerid)
inforows = cursorinfo.fetchone()
nServeridone = inforows[0]
nOuternet = inforows[1]
nInnernet = inforows[2]
nUseouterinner = inforows[3]
nServerloginuser = inforows[4]
nServerloginpwd = inforows[5]
nSshport = inforows[6]
if nServerSourceId == nUserPostion:
"""
获取Linux的基础数据，
"""
nSqldata = "select service_id, service_target_name, service_target_value, service_shell from server_service_prototype where service_type=5"
cursordata = connection.cursor()
cursordata.execute(nSqldata)
datarows = cursordata.fetchall()
print datarows
for row in datarows:
nServiceid = row[0]
nShelldata = row[3]
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(nInnernet, nSshport, nServerloginuser, nServerloginpwd)
stdin, stdout, stderr = ssh.exec_command(nShelldata)
outmsg, outerr = stdout.read(), stderr.read()
nServiceNum = outmsg
print nServiceNum
ssh.close()
#print serviceNum
nSqlupdate = "replace into server_info_ext (`server_id`,`service_id`,`service_value`) values (%s,%s,%s)"
cursordata = connection.cursor()
cursordata.execute(nSqlupdate, [nServerid, nServiceid, nServiceNum])
cursor.close()
connection.commit()
connection.close()
else:
print 111111
def create_user(user_id, username, address, phone):
with connection.cursor() as cursor:
cursor.execute(
"INSERT INTO CLIENT VALUES(client_seq.NEXTVAL, '{0}', '{1}', '{2}', {3});"
.format(username, address, phone, user_id))
cursor.execute(
"INSERT INTO CART (cart_id, client_id) VALUES(cart_seq.NEXTVAL, (SELECT client_id FROM CLIENT WHERE username = '******'));"
.format(username))
connection.commit()
def post_new(request, userid):
if request.method == "POST":
form = PostForm(request.POST)
if form.is_valid():
print 'form is valid'
post = form.save(commit=False)
post.student_loginid = userid
post.save()
print post.student
cursor = connection.cursor()
tp='NA'
ep='NA'
mp='NA'
s='0'
skill=[1,2,3,4,5,6]
for i in skill:
print i
cursor.execute("INSERT INTO polls_progress (tutorial_progress, exercise_progress, mastery_progress, student_id, skill, t_status, e_status, m_status) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",(tp, ep, mp, int(post.student), i, s, s, s))
connection.commit()
cursor2 = connection.cursor()
cursor2.execute ('''SELECT polls_progress.[progress]
FROM polls_progress
WHERE polls_progress.[student_id]= %s'''%(post.student))
progressid = cursor2.fetchall()
print "Progress id", progressid
t = '1'
a = '1'
listProgressId = [x[0] for x in progressid]
cursor3 = connection.cursor()
for i in listProgressId:
cursor3.execute("INSERT INTO polls_t_progress_details (progress_id, tutorial, animation_progress, student) VALUES (?, ?, ?, ?)",(i, t, a, int(post.student)))
connection.commit()
cursor4 = connection.cursor()
for i in listProgressId:
cursor4.execute("INSERT INTO polls_exe_progress_details (exercise, exeQueNumStatus, progress_id, student) VALUES (?, ?, ?, ?)",(t, a, i, int(post.student)))
connection.commit()
cursor5 = connection.cursor()
for i in listProgressId:
cursor5.execute("INSERT INTO polls_m_progress_details (mastery, question_progress, mastery_score, progress_id, student, mas_adv_progress, mas_easy_progress, mas_mod_progress) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",(t, a, s, i, int(post.student), s,s,s))
connection.commit()
cursor6 = connection.cursor()
cursor6.execute("""UPDATE polls_progress
SET t_status = %s
WHERE polls_progress.[skill] = %s AND polls_progress.[student_id] = %s""", (t, t, int(post.student)))
connection.commit()
return redirect('login')
else:
form = PostForm()
return render(request, 'info_form.html', {'form': form})
def addmysqlsqltask(self, nDbid, nSqlcontent, nUserid):
nSql = "select if(b.use_outer_inner=2,b.outer_net,b.inner_net) as netip,db_port,db_user,db_passport,default_dbname from db_server_info a join server_info b on a.server_id=b.server_id where a.db_id = %s"
cursor = connection.cursor()
cursor.execute(nSql, [nDbid])
rows = cursor.fetchone()
cursor.close()
nNet = rows[0]
nDbport = int(rows[1])
nDBuser = rows[2]
nDballpripwd = rows[3]
nDefaultdbname = rows[4]
nUpdateType = 1
nTaskSql = "insert into sql_update_task_info(user_id,db_id,update_type,sql_data) values(%s,%s,%s,%s)"
cursor = connection.cursor()
cursor.execute(nTaskSql, [nUserid, nDbid, nUpdateType, nSqlcontent])
taskid = cursor.lastrowid
cursor.close()
connection.commit()
connection.close()
nIncepsql = '/*--user=%s;--password=%s;--host=%s;--enable-check;--port=%d;*/' % (
nDBuser, nDballpripwd, nNet, nDbport) + '\n' \
+ 'inception_magic_start;' + '\n' \
+ nSqlcontent.encode('utf8') + '\n' \
+ 'inception_magic_commit;'
nIncepsql = nIncepsql.encode('utf-8').decode('latin-1')
conn = MySQLdb.connect(host="127.0.0.1",
user="******",
passwd="12345678",
db="Biz",
port=6669)
cursor = conn.cursor()
cursor.execute(nIncepsql)
result = cursor.fetchall()
cursor.close()
conn.close()
for row in result:
nCheckid = row[0]
nCheckstage = row[1]
nErrlevel = row[2]
nStagestatus = row[3]
nErrormessage = row[4].encode('utf8')
nChecksql = row[5].encode('utf8')
nAffectedrows = row[6]
checkSql = 'insert into sql_check_task_info(task_id,check_id,check_stage,err_level,stage_status,error_messge,check_sql,affected_row) values (%s,%s,%s,%s,%s,%s,%s,%s)'
cursor = connection.cursor()
cursor.execute(checkSql, [
taskid, nCheckid, nCheckstage, nErrlevel, nStagestatus,
nErrormessage, nChecksql, nAffectedrows
])
cursor.close()
connection.commit()
connection.close()
def delOperation(self,tablename,id):
sql = "delete from "+tablename+" where id =%s"
print "execute sql : %s" %(sql)
params=[id]
connection = self.get_connection()
cursor=connection.cursor()
cursor.execute(sql,params)
connection.commit()
pass
def exe_sql(sql):
from django.db import connection
cr = connection.cursor()
cr.execute(sql)
try:
connection.commit()
except:
pass
return cr
def register(request):
print('I am in sign up')
usr = None
try:
user_logout(request)
except:
print('sign up please')
print('couldnt make it')
if request.method == "POST":
profileid = request.POST.get('profileid')
username = request.POST.get('username')
emailid = request.POST.get('emailid')
dateofbirth = request.POST.get('dateofbirth')
city = request.POST.get('city')
street = request.POST.get('street')
password = request.POST.get('password')
role = request.POST.get('Role')
salt = os.urandom(32)
# password = '******'
key = hashlib.pbkdf2_hmac(
'sha256',
password.encode('utf-8'),
salt,
100000, # 100,000 iterations of SHA-256
# dklen=128 #128 byte key
)
is_teacher = None
is_student = None
if (role == "Teacher"):
is_teacher = 1
is_student = 0
elif (role == "Student"):
is_student = 1
is_teacher = 0
sql = "INSERT INTO PEOPLE(PROFILE_ID, NAME, EMAIL_ID, DATE_OF_BIRTH, CITY, STREET , KEY, SALT,IS_STUDENT, IS_TEACHER) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
try:
cursor = connection.cursor()
cursor.execute(sql, [
profileid, username, emailid, dateofbirth, city, street, key,
salt, is_student, is_teacher
])
connection.commit()
cursor.close()
print('trying')
return redirect('login/homepage.html')
except:
print('except')
return render(
request, 'login/homepage.html',
{'message': "Thanks! your Resgistration is successful!"})
else:
print('nothing')
return render(request, 'login/registration.html', {})
def regis_relawan(request):
print("----> Masuk regis_relawan")
if request.method == 'POST':
print("masuk post")
email=request.POST['email']
password=request.POST['password']
nama = request.POST['name']
alamat=request.POST['alamat']+', '+request.POST['kecamatan']+', '+request.POST['kabupaten']+", "+request.POST['provinsi']+", "+request.POST['kodepos']
nohp=request.POST['nohp']
ttl=request.POST['ttl']
keahlian=request.POST['keahlian']
print(type(ttl))
isValid=regis_user(request, email)
if ( isValid & (nohp!="") & (ttl!="") & (keahlian!="")):
query = 'SELECT * FROM "USER" WHERE email IN (SELECT email FROM RELAWAN WHERE email=%s)'
data = (email, )
cur.execute(query, data)
getUser=dictfetchall(cur)
print("sponsor udah ada: ",getUser)
if(len(getUser)!=0):
print("masuk false")
user=getUser[0]
messages.error(request, "Tidak dapat menggunakan username tersebut")
else:
insert_user(email, password, nama, alamat)
query = 'INSERT INTO RELAWAN (email, no_hp, tanggal_lahir) VALUES(%s, %s, %s)'
data = (email, nohp, ttl)
cur.execute(query, data)
connection.commit()
query = 'INSERT INTO KEAHLIAN_RELAWAN (email, keahlian) VALUES(%s, %s)'
data = (email, keahlian)
cur.execute(query, data)
connection.commit()
print("masuk relawan commit")
query='SELECT * FROM RELAWAN WHERE email=%s'
data=(email,)
cur.execute(query, data)
user = dictfetchall(cur)[0]
user['tanggal_lahir']=user.get('tanggal_lahir').isoformat()
request.session['user']=user
print("blabla ", request.session['user'])
request.session['role']='relawan'
messages.success(request, "Selamat! Kamu berhasil mendaftar.")
return HttpResponseRedirect(reverse('login:auth-login'))
return HttpResponseRedirect(reverse('registrasi-user:form-relawan'))
elif not(isValid):
return HttpResponseRedirect(reverse('registrasi-user:form-relawan'))
else:
messages.error(request,'Gak boleh ada field yang kosong yaa')
return HttpResponseRedirect(reverse('registrasi-user:form-relawan'))
def add_company(request):
user_id = request.session.get('user_id', 0)
try:
comp_name = request.POST['comp_name']
area = request.POST['area']
div_id = request.POST['divid']
dist_id = request.POST['distid']
# cursor = connection.cursor()
# sql = "SELECT AREA_ID FROM AREAS WHERE AREA_NAME = %s AND DIST_ID = %s"
# cursor.execute(sql, [area, dist_id])
# result = cursor.fetchall()
# cursor.close()
cursor = connection.cursor()
sql = "SELECT NEW_AREA_ID (%s, %s) FROM DUAL" # using function NEW_AREA_ID
cursor.execute(sql, [area, dist_id])
result = cursor.fetchall()
cursor.close()
area_id = result[0][0]
if area_id == -1:
cursor = connection.cursor()
sql = "SELECT area_id_seq.nextval FROM DUAL"
cursor.execute(sql)
result = cursor.fetchall()
cursor.close()
area_id = result[0][0]
cursor = connection.cursor()
# insertsql = "INSERT INTO AREAS VALUES ((SELECT COUNT(AREA_ID)+1 FROM AREAS), %s, %s)"
insertsql = "INSERT INTO AREAS VALUES (%s, INITCAP(%s), %s)"
cursor.execute(insertsql, [area_id, area, dist_id])
connection.commit()
cursor.close()
area_id = result[0][0]
cursor = connection.cursor()
sql = "SELECT comp_id_seq.nextval FROM DUAL"
cursor.execute(sql)
result = cursor.fetchall()
cursor.close()
comp_id = result[0][0]
cursor = connection.cursor()
insertsql = "INSERT INTO COMPANY VALUES (%s, %s, %s, %s)"
cursor.execute(insertsql, [comp_id, comp_name, area_id, user_id])
connection.commit()
cursor.close()
except KeyError:
return render(request, 'error.html', {"message": "Registration failed. Please provide all the necessary details"})
return HttpResponseRedirect(reverse("offered_jobs", args = (comp_id, )))
def updateUser(email, name, about):
cursor = connection.cursor()
try:
cursor.execute("UPDATE users SET about=%s, name=%s WHERE email=%s", (about, name, email))
connection.commit()
cursor.close()
except Exception:
raise
finally:
cursor.close()
def psql_update_ratings():
cur = connection.cursor()
array_to_predict = select_not_asses(cur)
predicted_array = hybrid.predict(array_to_predict)
insert_predicted(cur, predicted_array)
connection.commit()
def insertsysmsg(userid):
from django.db import connection
cursor = connection.cursor()
cursor.execute("select * from microblog_msgtext where id not in (select msgText_id from microblog_message where user_id=%s)" ,[userid])
rows = cursor.fetchall()
if rows:
for i in rows:
cursor.execute("insert into microblog_message (user_id,msgText_id,status_id) values(%s,%s,1)" ,[userid,i[0]])
connection.commit()
connection.close()
def updatePost(id, message):
cursor = connection.cursor()
try:
cursor.execute("UPDATE posts SET message=%s WHERE id = %s ", (message, id,))
connection.commit()
cursor.close()
except Exception:
raise
finally:
cursor.close()
def save_edit_customer(request):
id = request.POST['id']
name = request.POST['name']
country = request.POST['country']
operation_area = request.POST['operation_area']
cursor = conn.cursor()
cursor.execute("update aep_customer set name = '{0}', country = '{1}', operation_area = '{2}' where id = {3}".
format(name, country, operation_area, id))
conn.commit()
return customer(request)
def delete_meeting(meeting_id):
with connection.cursor() as cursor:
result1 = cursor.execute("DELETE FROM meetings WHERE id=%s",
[meeting_id])
result2 = cursor.execute(
"DELETE FROM users_meetings WHERE meeting_id=%s", [meeting_id])
result = result1 and result2
if result:
connection.commit()
return result
def update(self, request, pk=None):
paid_status = request.data.get('paidStatus')
paid_date = request.data.get('paidDate')
with connection.cursor() as cursor:
cursor.execute(
'update monthly_salary set paid_status=%s, paid_date=%s where employee_id_id=%s',
[paid_status, paid_date, pk])
connection.commit()
return Response({'message': 'row updated!'}, status=200)
def add_new_rental(rental):
sql = ''' INSERT INTO rental (CustID, VehicleID, StartDate, OrderDate, RentalType, Qty, ReturnDate, TotalAmount,
PaymentDate, returned)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''
try:
cur = connection.cursor()
cur.execute(sql, rental)
connection.commit()
except:
print("vehicle")
def insert_key(email, public_key ):
cursor=initialize_connection()
if has_key(email) == 0:
insert_key= """ insert into public."api_config" (email, public_key ) values (%s, %s) """
cursor.execute(insert_key, tuple([email, public_key]))
connection.commit()
else:
update_key = """ update public."api_config" set public_key= %s where email = %s """
cursor.execute(update_key, (public_key, email))
connection.commit()
def create_session(response, username):
cursor = conn.cursor()
cursor.execute("insert into aep_session (username, login_date) values('{0}', now()) returning id".format(username))
session_id = cursor.fetchone()[0]
conn.commit()
print 'session id {0} created'.format(session_id)
response.set_cookie("aep_session", session_id)
response.set_cookie("aep_username", username)
return response
def commit_open_transactions():
"""
Commit all open transactions.
"""
if connection.transaction_state:
# Since MySQl does not have nested transactions we just need
# to do one commit to commit all.
# However, we do not call leave_transaction_management()
# because any surrounding context managers or decorators
# expect to handle that themselves when they exit.
connection.commit()
def _vacuum_analyse(cursor):
"""
Vacuum analyse needs to be run from a different isolation level.
"""
print('Vacuum Analyse')
with Timer():
old_iso_level = connection.connection.isolation_level
connection.connection.set_isolation_level(0)
cursor.execute('VACUUM ANALYZE')
connection.commit()
connection.connection.set_isolation_level(old_iso_level)
def save_edit_user(request):
id = request.POST['id']
name = request.POST['name']
username = request.POST['username']
email = request.POST['email']
profile = request.POST['profile']
cursor = conn.cursor()
cursor.execute("update aep_user set name = '{0}', username = '******', email = '{2}', profile_id = {3} where id = {4}".
format(name, username, email, profile, id))
conn.commit()
return user(request)
def fix_emails(self, realm: Optional[Realm], emails: List[Text]) -> None:
for email in emails:
try:
user_profile = self.get_user(email, realm)
except CommandError:
print("e-mail %s doesn't exist in the realm %s, skipping" % (email, realm))
return
fix(user_profile)
connection.commit()
def save_edit_project(request):
id = request.POST['id']
name = request.POST['name']
customer = request.POST['customer']
cursor = conn.cursor()
cursor.execute("update aep_project set name = '{0}', customer_id = {1} where id = {2}".format(name, customer, id))
ProjectUser.objects.filter(project__id=id).delete()
for user in request.POST.getlist('users[]'):
cursor.execute("insert into aep_user_project (user_id, project_id) values({0},{1})".format(user, id))
conn.commit()
return project(request)
def save_user(request):
name = request.POST['name']
username = request.POST['username']
password = request.POST['password']
password = crypt_utils.encrypt(password)
email = request.POST['email']
profile = request.POST['profile']
cursor = conn.cursor()
cursor.execute("insert into aep_user (name, username, password, email, profile_id) "+
"values('{0}','{1}','{2}', '{3}', {4})".format(name, username, password, email, profile))
conn.commit()
return user(request)
def addForum(name, short_name, user):
cursor = connection.cursor()
try:
cursor.execute(
"INSERT INTO forums (name, short_name, user_id) "
"VALUES (%s, %s, (SELECT id FROM users WHERE email=%s))",
(name, short_name, user,))
connection.commit()
except Exception:
raise
finally:
cursor.close()
def save_sprint(request):
project = request.POST['project']
description = request.POST['description']
start_date = request.POST['start_date']
end_date = request.POST['end_date']
estimated_points = request.POST['estimated_points']
cursor = conn.cursor()
cursor.execute("insert into aep_sprint (project_id, description, start_date, end_date, points_estimated) "+
"values({0},'{1}','{2}','{3}',{4})".format(project, description, start_date, end_date, estimated_points))
conn.commit()
return sprint(request)
def delete_session(request, response):
cursor = conn.cursor()
session_id = get_session_id(request)
print 'removing session id {0}'.format(session_id)
cursor.execute("delete from aep_session where id = {0}".format(session_id))
conn.commit()
response.delete_cookie('aep_session')
response.delete_cookie('aep_username')
return response
def dispatching_page(request):
(op_type, table, payload) = ("", "", "")
try:
table = request.POST['table']
except KeyError:
table = "Person"
try:
op_type = request.POST['type']
except KeyError:
op_type = "select"
operation = operation_prefix[op_type]
try:
payload = request.POST['parameter']
except KeyError:
payload = 'uid = -1'
query = (operation % table) + payload + ";"
# opening connection to the database
cur = connection.cursor()
# do the selection
status = "Query executed properly!"
(results, columns) = ("", "")
try:
cur.execute(query)
connection.commit()
if "select" == op_type:
results = cur.fetchall()
columns = [col[0] for col in cur.description]
except Error as e:
status = "Error ! " + str(e)
finally:
cur.close()
connection.close()
if "select" == op_type and "Error" not in status:
context = {'queries': required_queries,
'query_name': query,
'query_result': results, 'col_title': columns}
return render(request, 'application/result.html', context)
else:
context = {'queries': required_queries,
'query_name': query,
'status': status}
return render(request, 'application/status.html', context)
def add_tweet(id, created_dt, coordinates, text, county, sentiment_index):
try:
cursor = connection.cursor()
query = "INSERT INTO tweets_tweet (id, created_dt, coordinates, " \
"text, county, sentiment_index) VALUES (%s, %s, %s, %s, %s, %s);"
data = (id, created_dt, coordinates, text, county, sentiment_index)
cursor.execute(query, data)
connection.commit()
print 'Tweet saved to DB'
cursor.close()
except Exception as e:
print "Encountered error: (%s)" % e.message
def _do_vacuum(self, full=False):
cursor = connection.cursor()
if full:
print("Vacuuming (full) table {}...".format(Revision._meta.db_table))
cursor.execute("VACUUM FULL {}".format(Revision._meta.db_table))
print("Vacuuming (full) table {}...".format(Version._meta.db_table))
cursor.execute("VACUUM FULL {}".format(Version._meta.db_table))
else:
print("Vacuuming table {}...".format(Revision._meta.db_table))
cursor.execute("VACUUM {}".format(Revision._meta.db_table))
print("Vacuuming table {}...".format(Version._meta.db_table))
cursor.execute("VACUUM {}".format(Version._meta.db_table))
connection.commit()
def createUser(email, username, name, about, isAnonymous):
cursor = connection.cursor()
try:
cursor.execute(
"INSERT INTO users (email, username, name, about, isAnonymous) " "VALUES (%s, %s, %s, %s, %s)",
(email, username, name, about, isAnonymous),
)
connection.commit()
cursor.close()
except Exception:
raise
finally:
cursor.close()
def _import_data(cursor):
"""
Import the data created by osmosis found in the 'import' dir to postgres.
"""
for table in _TABLES:
try:
with open(_IMPORT_DIR + table + '.txt') as f:
print("Importing: " + table)
cursor.copy_from(f, table)
connection.commit()
except IOError:
sys.stderr.write('Could not open import files. Please make sure Osmosis managed to create them properly!\n')
sys.exit(1)
def ExecuteSql(sql,isQuery=True):
if sql is None or sql=="":
return
from django.db import connection, transaction
cursor = connection.cursor()
cursor.execute(sql)
if isQuery:
fetchall = cursor.fetchall()
return fetchall
else:
connection.commit()
cursor.close()
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())
