def get_answers_by_id(cursor, _id):
cursor.execute("""
SELECT * FROM answer
WHERE question_id= %(_id)s ORDER BY id DESC;
""", {"_id": _id})
answers = cursor.fetchall()
return utils.get_readable_date(answers)
def get_answers_by_user_id(cursor, user_id):
cursor.execute("""
SELECT * FROM answer
WHERE user_id = %(user_id)s""",
{'user_id': user_id})
answers = cursor.fetchall()
return utils.get_readable_date(answers)
def get_comments_by_user_id(cursor, user_id):
cursor.execute("""
SELECT * FROM comment
WHERE user_id = %(user_id)s""",
{'user_id': user_id})
comments = cursor.fetchall()
return utils.get_readable_date(comments)
def get_questions(cursor):
cursor.execute("""
SELECT * FROM question
ORDER BY id;
""")
list_of_questions = cursor.fetchall()
return utils.get_readable_date(list_of_questions)
def get_questions_by_user_id(cursor, user_id):
cursor.execute("""
SELECT * FROM question
WHERE user_id = %(user_id)s""",
{'user_id': user_id})
questions = cursor.fetchall()
return utils.get_readable_date(questions)
def get_comments_by_question_id(cursor, question_id):
cursor.execute("""
SELECT * FROM comment
WHERE question_id = %(question_id)s
ORDER BY id;
""", {'question_id': question_id})
list_of_comments = cursor.fetchall()
return utils.get_readable_date(list_of_comments)
def order_list_by_key(cursor, col, order):
if order == "asc":
cursor.execute(
sql.SQL("""
SELECT * FROM question
ORDER BY {col} ASC; """, ).
format(col=sql.Identifier(col))
)
new_list = cursor.fetchall()
return utils.get_readable_date(new_list)
elif order == "desc":
cursor.execute(
sql.SQL("""
SELECT * FROM question
ORDER BY {col} DESC; """, ).
format(col=sql.Identifier(col))
)
new_list = cursor.fetchall()
return utils.get_readable_date(new_list)
def search_questions(cursor, searched_term):
cursor.execute("""
SELECT question.* FROM question
LEFT JOIN answer a on question.id = a.question_id
WHERE title LIKE %(word)s
OR question.message like %(word)s
OR a.message LIKE %(word)s;
""", {'word': '%' + searched_term + '%'})
questions = cursor.fetchall()
return utils.get_readable_date(questions)
def get_answers(cursor):
cursor.execute("""
SELECT * FROM answer;
""")
list_of_answers = cursor.fetchall()
return utils.get_readable_date(list_of_answers)
def get_album_comments(album_link):
page_id, album_id = album_link[20:].split('_')
api = utils.VKConnector(token=tokens[0])
comments_quantity = api.get_comments(page_id, album_id)['count']
if comments_quantity > 0:
wb = Workbook() # wb is for workbook
ws = wb.active # ws is for worksheet
ws['A1'] = 'Время'
ws['B1'] = 'Ссылка на фото'
ws['C1'] = 'Весь текст под фото'
ws['D1'] = 'Название'
ws['E1'] = 'Артикул'
ws['F1'] = 'Цена'
ws['G1'] = 'Имя комментатора'
ws['H1'] = 'Страница комментатора'
ws['I1'] = 'Текст комментария'
offset = 0
while offset < comments_quantity:
comments = api.get_comments(page_id,
album_id,
offset=offset,
count=MAX_COMMENTS)['items']
users_data = api.get_users_json([c['from_id'] for c in comments])
images_comments = api.get_images_comments(page_id, album_id)
texts = api.get_photo_text(page_id, album_id)
for row, comment in enumerate(comments, start=2 + offset):
date = utils.get_readable_date(comment['date'])
text = html.unescape(comment['text']).replace('+1', 'плюс 1')
pic_page = f"https://vk.com/photo{page_id}_{comment['pid']}"
try:
user_page = users_data[comment['from_id']]['user_page']
name = users_data[comment['from_id']]['name']
except KeyError:
uid = comment['from_id']
user_page = f'https://vk.com/id{uid}'
try:
s = api.get_user_data(uid)[0]
name = f"{s['last_name']} {s['first_name']}"
except IndexError:
name = ''
text = texts[comment['pid']].replace('\n', ' ')
try:
image_text = images_comments[comment['pid']]
except KeyError:
image_text = api.get_image_data_by_id(
page_id, comment['pid'])['text']
image_text_split = image_text.split('\n')
article_flag = 'Артикул: '
price_flag = 'Цена: '
product_name = image_text_split[0]
article = utils.find_by_flag(image_text_split, article_flag)
with suppress(ValueError):
article = int(article)
price = utils.find_by_flag(image_text_split, price_flag)
ws[f'A{row}'] = date
ws[f'B{row}'] = pic_page
ws[f'B{row}'].hyperlink = pic_page
ws[f'C{row}'] = image_text.replace('\n', ' ')
ws[f'D{row}'] = product_name
ws[f'E{row}'] = article
ws[f'F{row}'] = price
ws[f'G{row}'] = name
ws[f'H{row}'].hyperlink = user_page
ws[f'I{row}'] = text
offset += MAX_COMMENTS
try:
wb.save(
os.path.join(os.path.dirname(sys.argv[0]), 'VK Comments.xlsx'))
except PermissionError:
print(
'Пожалуйста, закройте таблицу с комментариями и запустите скрипт снова.'
)
exit(1)
else:
print(
'В этом альбоме ещё нет комментариев. Работа скрипта сейчас будет завершена.'
)
exit(1)