from elasticsearch import Elasticsearch
from connection import connect_sql, execute
es = Elasticsearch()
db = connect_sql()
cursor = db.cursor()
query = "select * from naukri_banglore where elasticsearch=0"
cursor.execute(query)
jd = cursor.fetchall()
columns = [col[0] for col in cursor.description]
for i in jd:
job = dict(zip(columns, i))
# print(job)
data = {}
for j in job:
if job[j] != 'NULL':
if isinstance(job[j], str):
data[j] = job[j].decode("latin1")
else:
data[j] = job[j]
try:
doc = es.index(index='jobs',
doc_type='job_description',
id=data['id'],
body=data)
query1 = 'update naukri_banglore set elasticsearch=1 where job_id={}'.format(
data['job_id'])
execute(query1)
def get_all_sql_answers_by_question_id(id):
query = connection.connect_sql(f"""SELECT * FROM answers WHERE question_id = '{id}' """)
print(id)
results = convert_query_to_dictionary(query)
print('to są wynii',results)
return results
def add_sql_answer(form_data, question_id):
id = uuid.uuid4()
print('dione?')
query = (f"""INSERT INTO answers (id, vote_number, question_id, message)
VALUES ('{id}', 0, '{question_id}', '{form_data['answer']}') """)
connection.connect_sql(query)
def update_question_view_increase_count(id):
connection.connect_sql(f"""UPDATE questions
SET view_number = view_number + 1
WHERE id = '{id}' """)
def get_all_sql_answers():
query = connection.connect_sql("""SELECT * FROM answers""")
results = convert_query_to_dictionary(query)
return results
def update_question_sql(id, form_data):
connection.connect_sql(f"""
UPDATE questions
SET title = '{form_data['title']}', message = '{form_data['description']}'
WHERE id = '{id}';
""")
def add_question_sql(data):
id = uuid.uuid4()
query = f"""INSERT INTO questions (id, vote_number, view_number, title, message) """ \
f"""VALUES ('{id}', 0, 1, '{data['title']}', '{data['description']}');"""
connection.connect_sql(query)
def get_question_sql_by_id(id):
query = connection.connect_sql(f"""SELECT * FROM questions WHERE id = '{id}'""")
formatted_question = query
result = convert_query_to_dictionary(formatted_question)
return result[0]
def get_questions_sorted(order_by, order_direction):
query = connection.connect_sql(f"""SELECT * FROM questions ORDER BY {order_by} {order_direction};""")
result = convert_query_to_dictionary(query)
return result
def get_5_questions_sql_sorted_by_submission_time():
query = connection.connect_sql("""SELECT * FROM questions ORDER BY submission_time DESC LIMIT 5;""")
result = convert_query_to_dictionary(query)
return result