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)
