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)