def create(tory):
"""
Funckja tworzy nowy obiekt tory
:param tory:
:return:
"""
nazwa = tory.get("nazwa")
dlugosc = tory.get("dlugosc")
numer = tory.get("numer")
u_id = current_user.id
print(nazwa, dlugosc, numer)
cur.execute(
"SELECT b.b_id FROM bocznica b WHERE b.nazwa = %s AND b.u_id = %s",
(nazwa, u_id))
b_id = cur.fetchone()[0]
print(b_id)
cur.execute(
"SELECT EXISTS(SELECT 1 from tory t, bocznica b where t.numer = %s AND t.b_id = %s);",
(numer, b_id))
value = cur.fetchone()[0]
print(value)
if not value:
cur.execute("INSERT INTO tory (b_id,dlugosc,numer) VALUES (%s,%s,%s);",
(b_id, dlugosc, numer))
connection.commit()
return "Added successfully", 201
else:
abort(406, "Bocznica o tej nazwie już istnieje")
def get_project_by_id(project_id):
with connection.cursor() as cursor:
query = """ SELECT * FROM project WHERE id=%s """
cursor.execute(query, (project_id))
connection.commit()
result = cursor.fetchone()
return result
def insert_new(obj,
type_): # type_ is binary: 0 -> business owner, 1 -> client
return_em = obj.email
with connection.cursor() as cursor:
result = get_user(obj.email)
if result is not None:
raise UserAlreadyExists()
else:
password = '******' + obj.hashed_pass + '"'
if type_ == 0:
query = f"INSERT INTO owners (email, categories, info) VALUES " \
f"('{obj.email}', '{obj.cat}', '{obj.info}')"
cursor.execute(query)
query = f"INSERT INTO users (owner, name, email, city, zip_code, phone, type, img_url, pass_hash) VALUES " \
f"('{return_em}', '{obj.name}', '{obj.email}', '{obj.city}', '{obj.zip_code}', '{obj.phone}', " \
f"{type_}, '{obj.img_url}'," \
f" {password})"
else:
query = f"INSERT INTO users (name, email, city, zip_code, phone, type, img_url, pass_hash) VALUES " \
f"('{obj.name}', '{obj.email}', '{obj.city}', '{obj.zip_code}', '{obj.phone}', " \
f"{type_}, '{obj.img_url}'," \
f" {password})"
cursor.execute(query)
connection.commit()
return return_em
def add_book(book_title, book_purchase_link, book_audio_link):
book_title = escape_single_quote(book_title)
if not utils.is_valid(book_title):
raise DBException("Invalid book details.")
try:
with connection.cursor() as cursor:
cols = '(title, link_to_buy, audio_book)'
vals = "values ('{}', '{}', '{}')".format(book_title,
book_purchase_link,
book_audio_link)
if book_purchase_link is None and book_audio_link is None:
vals = "values ('{}', NULL, NULL);".format(book_title)
elif book_purchase_link is None:
vals = "values ('{}', NULL, '{}');".format(
book_title, book_audio_link)
elif book_audio_link is None:
vals = "values ('{}', '{}', NULL);".format(
book_title, book_purchase_link)
query = 'INSERT into books {} {}'.format(cols, vals)
print(query)
cursor.execute(query)
connection.commit()
return True
except Exception as e:
message = "Error Occurred: " + str(e)
raise DBException(message)
return False
def delete_from_DB(query):
try:
with connection.cursor() as cursor:
cursor.execute(query)
connection.commit()
except:
print("Error,Could not delete from database")
def answer_question(teacher_chat_id, parent_chat_id, answer):
with connection.cursor() as cursor:
query = "SELECT * FROM parentsQuestions"
cursor.execute(query)
result = cursor.fetchone()
if result is not None:
ques = "'" + result['question'] + "'"
lques = [result['question']]
keywords = get_keywords(lques)
query2 = f"INSERT INTO QA VALUES({result['chat_id']}, '{answer}', {ques}, '{keywords}')"
cursor.execute(query2)
query3 = f"DELETE FROM parentsQuestions WHERE question = {ques}"
cursor.execute(query3)
connection.commit()
requests.get(
TELEGRAM_SEND_MESSAGE_URL.format(
TOKEN, parent_chat_id, "The teacher says:\n" + answer))
requests.get(
TELEGRAM_SEND_MESSAGE_URL.format(
TOKEN, teacher_chat_id,
"Is this a general or a private question?"
"(general/ private)"))
else:
requests.get(
TELEGRAM_SEND_MESSAGE_URL.format(
TOKEN, teacher_chat_id,
"There are no questions to answer! "
"Thank you and have a nice day! :) "))
return ques
def get_all_available():
with connection.cursor() as cursor:
query = """ SELECT * FROM employee WHERE STATUS = 1 """
cursor.execute(query, ())
connection.commit()
result = cursor.fetchall()
return result
def get_all_employees_works_in_project(project_id):
with connection.cursor() as cursor:
query = """ SELECT * FROM employee WHERE project_id=%s """
cursor.execute(query, (project_id))
connection.commit()
result = cursor.fetchall()
return result
def get_all_employees():
with connection.cursor() as cursor:
query = """ SELECT * FROM employee """
cursor.execute(query, ())
connection.commit()
result = cursor.fetchall()
return result
def get_all_history_time_line():
with connection.cursor() as cursor:
query = """ SELECT * FROM time_line"""
cursor.execute(query, ())
connection.commit()
result = cursor.fetchall()
return result
def get_constractor():
with connection.cursor() as cursor:
query = """ SELECT * FROM constractor """
cursor.execute(query, ())
connection.commit()
result = cursor.fetchone()
return result
def insert_to_DB(query):
try:
with connection.cursor() as cursor:
cursor.execute(query)
connection.commit()
except pymysql.DatabaseError as err:
print("Insert record Error", err)
return False
def set_employee_status(employee_id, project_id):
with connection.cursor() as cursor:
query = """ UPDATE employee set status=%s , project_id=%s WHERE id=%s """
cursor.execute(query, (
'false',
project_id,
employee_id,
))
connection.commit()
def add_new_employee(employee_id, employee_name, employee_phone_number,
employee_job):
constractor_id = get_constractor()['id']
with connection.cursor() as cursor:
query = """ INSERT INTO employee (id,name,phone_number,job,constructor_id) VALUES(%s,%s,%s,%s,%s)"""
cursor.execute(query,
(employee_id, employee_name, employee_phone_number,
employee_job, constractor_id))
connection.commit()
def add_user(chat_id, role, class_, has_job):
with connection.cursor() as cursor:
if has_job is None:
sql = "INSERT INTO `Users` VALUES (%s,%s,%s,NULL)"
cursor.execute(sql, (role, class_, chat_id))
else:
sql = "INSERT INTO `Users` VALUES (%s,%s,%s,%s)"
cursor.execute(sql, (role, class_, chat_id, has_job))
connection.commit()
def get_employee_by_id(employee_id):
with connection.cursor() as cursor:
query = """ SELECT * FROM employee WHERE id = %s """
cursor.execute(query, (employee_id, ))
connection.commit()
result = cursor.fetchone()
if result == None:
raise Exception("Error, this employee does not exist")
return result
def delete(nazwa):
cur.execute("SELECT EXISTS(SELECT 1 from wagony where nazwa = %s);",
(nazwa, ))
val = cur.fetchone()[0]
if val:
cur.execute("DELETE FROM wagony WHERE nazwa = %s CASCADE;", (nazwa, ))
connection.commit()
return "Deleted successfully", 201
else:
abort(404, "Wagon o tej nazwie nie znaleziona")
def update_item(item_attr, email, name):
with connection.cursor() as cursor:
query = f"update items set "
for key, val in item_attr.items():
if val:
query += f"{key} = '{val}', "
query = query.strip(', ')
query += f" where owner = '{email}' and name = '{name}'"
cursor.execute(query)
connection.commit()
def remove_question(teacher_chat_id, ques):
with connection.cursor() as cursor:
query = f"SELECT * FROM QA where question = {ques}"
cursor.execute(query)
result = cursor.fetchone()
if result is not None:
query3 = f"DELETE FROM QA WHERE question = {ques}"
cursor.execute(query3)
connection.commit()
requests.get(
TELEGRAM_SEND_MESSAGE_URL.format(TOKEN, teacher_chat_id,
"okay 🤗"))
def insert_data(sql):
try:
with connection.cursor() as cursor:
# 执行sql语句，进行查询
sql = sql
cursor.execute(sql)
# 获取查询结果
#result = cursor.fetchall()
# 没有设置默认自动提交，需要主动提交，以保存所执行的语句
connection.commit()
except:
print("插入失败")
def add_user(username, userpass):
base = user_in_base(username)
if base == False:
try:
with connection.cursor() as cursor:
sql = "INSERT INTO user (username, password) VALUES (%s, %s)"
cursor.execute(sql, (username, userpass))
connection.commit()
return (check_user(username, userpass))
finally:
cursor.close()
else:
return (0)
def opusc_tor(numer_wagonu):
"""
Funkcja ustawia czas wyjazdu, czas pobytu i wartosc true dla
pola opuscil_tor
:param numer_wagonu:
:return:
"""
cur.execute("SELECT opusc_bocznice(%s)", (numer_wagonu, ))
value = cur.fetchone()[0]
connection.commit()
if value:
return "Wagon opuscil bocznice", 201
else:
abort(404, "Wagon o tym numerze nie znaleziony")
def add_book_review(book_title, user_id, review):
book_title = escape_single_quote(book_title)
try:
with connection.cursor() as cursor:
condition = "user_id = '{}' and book_title = '{}'".format(
user_id, book_title)
query = "UPDATE reviews SET review = '{}' WHERE {}".format(
review, condition)
cursor.execute(query)
connection.commit()
return True
except Exception as e:
message = "Error Occurred: " + str(e)
raise DBException(message)
return False
def firma_uzytkownika(NIP):
"""
Funckja aktualizuje f_id podanej firmy do uzytkownika
:param NIP:
:return:
"""
u_id = current_user.id
cur.execute("SELECT dodaj_firme_uzytkownika(%s,%s)", (NIP, u_id))
value = cur.fetchone()[0]
print(value)
connection.commit()
if value:
return "Firma dodana do uzytkownika", 201
else:
abort(404, "Firma o tym numerze nie znaleziona")
def delete(NIP):
"""
Funckja usuwa firme o podanym NIPie
:param NIP:
:return:
"""
cur.execute("SELECT EXISTS(SELECT 1 from firmy where NIP = %s);", (NIP, ))
print(NIP)
value = cur.fetchone()[0]
if value:
cur.execute("DELETE FROM firmy WHERE NIP=%s;", (NIP, ))
connection.commit()
return "Usunieto pomyslnie", 201
else:
abort(404, "Firma o tym NIPie nie znaleziona")
def create_user(user_id, user_first_name, user_last_name):
if not utils.is_valid(user_id, user_first_name, user_last_name):
raise DBException("Invalid user details.")
try:
with connection.cursor() as cursor:
cols = '(user_id, first_name, last_name)'
vals = "values ('{}', '{}', '{}')".format(user_id, user_first_name,
user_last_name)
query = 'INSERT into users {} {}'.format(cols, vals)
cursor.execute(query)
connection.commit()
return True
except Exception as e:
message = "Error Occurred: " + str(e)
raise DBException(message)
return False
def add_book_rating_review(book_title, user_id, rating=None, review=None):
book_title = escape_single_quote(book_title)
try:
with connection.cursor() as cursor:
like_ = 1 if rating else 0
cols = '(user_id, book_title, like_, review)'
vals = "('{}', '{}', b'{}', '{}')".format(user_id, book_title,
like_, review)
query = 'INSERT into reviews {} values {}'.format(cols, vals)
cursor.execute(query)
connection.commit()
return True
except Exception as e:
message = "Error Occurred: " + str(e)
raise DBException(message)
return False
def answer_add_question(answer, chat_id):
global the_question_to_answer
with connection.cursor() as cursor:
query = f"SELECT * FROM QA WHERE question='{the_question_to_answer}' and chat_id={chat_id}"
cursor.execute(query)
result = cursor.fetchone()
if result is None:
lquest = [the_question_to_answer]
keywords = get_keywords(lquest)
query = f"INSERT INTO QA VALUES({chat_id}, '{answer}', '{the_question_to_answer}' , '{keywords}')"
cursor.execute(query)
connection.commit()
requests.get(
TELEGRAM_SEND_MESSAGE_URL.format(
TOKEN, chat_id, "question added successfully!"))
the_question_to_answer = ''
def delete(numer_wagonu):
"""
:param date:
:return:
"""
cur.execute(
"SELECT EXISTS(SELECT 1 from wagony_na_torze where numer_wagonu = %s;",
(numer_wagonu, ))
val = cur.fetchone()[0]
if val:
cur.execute("DELETE FROM wagony_na_torze WHERE numer_wagonu = %s;",
(numer_wagonu))
connection.commit()
return "Deleted successfully", 201
else:
abort(404, "Bocznica o tej nazwie nie znaleziona")
def insert_user(name, email, city, zip_code, phone, img_url, password):
with connection.cursor() as cursor:
query = f"SELECT * FROM users where name = '{name}' and phone = '{phone}'"
cursor.execute(query)
result = cursor.fetchone()
if result is not None:
raise UserAlreadyExists()
else:
query = f"INSERT INTO users (name, email, city, zip_code, phone, img_url, pass_hash) VALUES " \
f"('{name}', '{email}', '{city}', '{zip_code}', '{phone}', '{img_url}', '{password}')"
cursor.execute(query)
connection.commit()
query = f"SELECT * FROM users where name = '{name}' and phone = '{phone}'"
cursor.execute(query)
result = cursor.fetchone()
return result["id"]
continue
file_path = os.path.join(data_path, fn)
print file_path
f = codecs.open(file_path, 'r', encoding='utf-8')
for line in f:
count += 1
if count <= 0:
continue
email = parse_email(line)
table_name = get_table_name(email)
try:
sql = "INSERT INTO `{0}` VALUE (0, '{1}','', 0, '', '', '' , '', '', 0, {2})".format(table_name, email,
priority)
# print sql
cursor.execute(sql)
success_count += 1
except pymysql.err.IntegrityError:
pass
except pymysql.err.ProgrammingError:
pass
except Exception, e:
print e
pass
if count % batch == 0:
connection.commit()
print '{0} commit success , count : {1} , insert : {2}'.format(datetime.now(), count, success_count)
connection.begin()
connection.commit()
connection.close()
print '{0} commit success , count : {1}, insert : {2}'.format(datetime.now(), count, success_count)
