def get_user(email): result = None with connection.cursor() as cursor: query = f"SELECT * FROM users where email = '{email}'" cursor.execute(query) result = cursor.fetchone() return result
def get_all_pos_ratting_books_by_user(user_id): with connection.cursor() as cursor: condition = "user_id like '{}' and like_ = '1'".format(user_id) query = "SELECT * FROM reviews WHERE {};".format(condition) cursor.execute(query) res = cursor.fetchall() return list(set([review['book_title'] for review in res]))
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 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 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 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
async def on_message_delete(self, ctx): if ctx.author.bot or ctx.author.bot: return with connection.cursor() as cursor: cursor.execute( f"SELECT log_channel_id FROM ServerConfig WHERE guild_id = {ctx.guild.id}" ) channel_id = cursor.fetchone() print(channel_id) channel = self.client.get_channel(id=channel_id.get('log_channel_id')) if channel != ctx.channel: embed = discord.Embed(title='Messages delete', color=0xff3434, timestamp=datetime.datetime.now()) embed.add_field(name='Author:', value=ctx.author, inline=False) embed.add_field(name='Message:', value=f'"{ctx.content}"', inline=False) embed.set_thumbnail( url= 'https://cdn.discordapp.com/attachments/669159790961754112/705912066648834068/Dont.png' ) for picture in ctx.attachments: if picture.filename.endswith( ('.png', '.jpg', '.gif', '.jpeg', '.ico', '.svg')): embed.set_image(url=picture.proxy_url) await channel.send(embed=embed)
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_review_without_rating(book_title, user_id): book_title = escape_single_quote(book_title) with connection.cursor() as cursor: query = "SELECT * FROM reviews WHERE user_id like '{}' and book_title like '{}'" \ .format(user_id, book_title) cursor.execute(query) return cursor.fetchone()
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 get_review_by_user_id(user_id): if not utils.is_valid(user_id): raise DBException("Invalid user details.") with connection.cursor() as cursor: query = "SELECT * FROM reviews WHERE user_id like '{}'".format(user_id) cursor.execute(query) return cursor.fetchall()
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_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 delete_from_DB(query): try: with connection.cursor() as cursor: cursor.execute(query) connection.commit() except: print("Error,Could not delete from database")
def is_user_exist(user_id): ust = type(user_id) with connection.cursor() as cursor: condition = "user_id like '{}' ".format(user_id) query = "SELECT * FROM users WHERE {}".format(condition) cursor.execute(query) return len(cursor.fetchall()) > 0
def is_book_review_exist(book_title, user_id): book_title = escape_single_quote(book_title) with connection.cursor() as cursor: condition = "user_id like '{}' and book_title like '{}' ".format( user_id, book_title) query = "SELECT * FROM reviews WHERE {}".format(condition) cursor.execute(query) return len(cursor.fetchall()) > 0
def create_task(user_id, title, begin, end, status): try: with connection.cursor() as cursor: sql = "INSERT INTO task (title, begin, end, status) VALUES (%s, %s, %s, %s)" cursor.execute(sql, (title, begin, end, status)) connection.commit() with connection.cursor() as cursor: sql = "SELECT MAX(task_id) FROM task" cursor.execute(sql) connection.commit() task_id = cursor.fetchone() task_id = task_id[0] sql = "INSERT INTO user_has_task (fk_user_id, fk_task_id) VALUES (%s, %s)" cursor.execute(sql, (user_id, task_id)) connection.commit() finally: cursor.close()
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 show_table(table_name): if not utils.is_valid(table_name): raise DBException("Invalid table name details.") with connection.cursor() as cursor: query = "SELECT * FROM {}".format(table_name) cursor.execute(query) result = cursor.fetchall() print(result)
def get_data_from_DB(query): try: with connection.cursor() as cursor: cursor.execute(query) result = cursor.fetchall() return result except: print("Failed to get data")
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 get_task(user_id): try: with connection.cursor() as cursor: sql = "SELECT * FROM task INNER JOIN user_has_task WHERE user_has_task.fk_user_id = %s and task.task_id = user_has_task.fk_task_id" cursor.execute(sql, (user_id)) result = cursor.fetchall() return (result) finally: cursor.close()
def get_all_negative_reviews(): with connection.cursor() as cursor: condition = "WHERE like_ = '0' " query = "SELECT * FROM reviews {}".format(condition) cursor.execute(query) res = cursor.fetchall() for review in res: review['like_'] = False return res
def get_book(book_title): book_title = escape_single_quote(book_title) if not utils.is_valid(book_title): raise DBException("Invalid book title details.") with connection.cursor() as cursor: condition = "title like '{}' ".format(book_title) query = "SELECT * FROM books WHERE {}".format(condition) cursor.execute(query) return cursor.fetchone()
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_items(owner_em, item_name=None): with connection.cursor() as cursor: if item_name: query = f"SELECT * FROM items where owner = '{owner_em}' and name = '{item_name}'" else: query = f"SELECT * FROM items where owner = '{owner_em}'" cursor.execute(query) result = cursor.fetchall() return result
def get_review_with_specific_rating(book_title, user_id, rating: bool): book_title = escape_single_quote(book_title) with connection.cursor() as cursor: rating = 1 if rating else 0 condition = "user_id like '{}' and book_title like '{}' and like_ = b'{}'"\ .format(user_id, book_title, rating) query = "SELECT * FROM reviews WHERE {}".format(condition) cursor.execute(query) return cursor.fetchone()
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 get_owners(cat=None): with connection.cursor() as cursor: if cat is None: query = f"select * from users join owners on owners.email=users.email" else: query = f"select * from users join owners on owners.email=users.email where owners.categories = '{cat}'" cursor.execute(query) result = cursor.fetchall() return result
def update_exist_email(table_name, email, profile_url): try: with connection.cursor() as cursor: # print '### update {}, {}'.format(email, profile_url) cursor.execute('select * from {0} where email = "{1}"'.format(table_name, email)) row = cursor.fetchone() # print row update_sql = False if row['status'] == 201 or row['status'] == 403: update_sql = 'update {0} set status = 200 , message = "mockup" ,profile_url = "{1}" ' \ 'where email = "{2}" and profile_url = "" ' elif row['status'] == 404 and row['level'] == 10: update_sql = 'update {0} set status = 0 , message = "mockup" ,profile_url = "{1}" ' \ 'where email = "{2}" and profile_url = "" ' if update_sql: return cursor.execute(update_sql.format(table_name, profile_url, email)) else: return 0 except Exception, e: print e return 0
def get_table_name(m): first = m[:1] alphabet = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z'] if first in alphabet: return 'people_' + first return 'people_0' count = 0 success_count = 0 batch = 10000 priority = 7 data_path = '/home/forge/email_list/dropbox/' with connection.cursor() as cursor: for fn in os.listdir(data_path): if not fn.endswith('.txt'): 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)