def get_interval_category(category, data_inicial, data_final):
cnx = connection.connection()
cursor = cnx.cursor()
query = ("SELECT public_date FROM pessoas "
"WHERE categories LIKE %s AND public_date BETWEEN %s AND %s")
# query = ("SELECT * FROM pessoas "
# "WHERE categories LIKE %s AND public_date BETWEEN %s AND %s")
# str_data_inicial = data_inicial.strftime("%Y-%m-%d")
# str_data_final = data_final.strftime("%Y-%m-%d")
request_site = (
category,
data_inicial,
data_final,
)
cursor.execute(query, request_site)
rows = cursor.fetchall()
# print(len(rows))
# print('aqui')
for i in rows:
print(i)
# for (first_name, last_name, hire_date) in cursor:
# print("{}, {} was hired on {:%d %b %Y}".format(last_name, first_name, hire_date))
cursor.close()
cnx.close()
return rows
def save_news(news=Social_News):
cnx = connection.connection()
try:
cursor = cnx.cursor()
# timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
# now = datetime.datetime(2009, 5, 5)
# str_now = now.date().isoformat()
str_now = datetime.datetime.now().strftime("%Y-%m-%d")
cats = Util.join_categories(news.categories[0])
add_news = (
"INSERT INTO midia "
"(id, abstract, noticia, public_date, image, titulo, link, cheated_at, categories, Pinterest, fb_comment, fb_share, fb_reaction, fb_total) "
"VALUES (NULL, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
(news.abstract[0], news.news[0], news.date[0], news.media[0],
news.title[0], news.link[0], str_now, cats, '0',
news.fb_comment[0], news.fb_share[0], news.fb_reaction[0],
news.fb_total[0]))
cursor.execute(*add_news)
except Exception as e:
print(e)
cnx.close()
def select(site):
cnx = connection.connection()
cursor = cnx.cursor()
sql = "SELECT * FROM relevancia_site WHERE site = %s"
site = (site, )
cursor.execute(sql, site)
result = cursor.fetchall()[0]
cnx.close()
return result
def select_news(news=News):
cnx = connection.connection()
try:
cursor = cnx.cursor()
news.date[0] = parser.parse(news.date[0])
query = ("SELECT * FROM credito WHERE abstract= %s and titulo = %s",
(news.abstract[0], news.title[0]))
result = cursor.execute(*query)
cnx.close()
return result
except Exception as e:
print(e)
def update(relevancia_site=Relevancia_Site):
cnx = connection.connection()
cursor = cnx.cursor()
sql = "UPDATE relevancia_site SET relevancia = %s WHERE site = %s"
site = (
relevancia_site.relevancia[0],
relevancia_site.site[0],
)
cursor.execute(sql, site)
cnx.close()
def select_site(site):
cnx = connection.connection()
cursor = cnx.cursor()
query = ("SELECT * FROM pessoas WHERE site = %s")
request_site = (site, )
cursor.execute(query, request_site)
rows = cursor.fetchall()
# print(len(rows))
for row in rows:
print(row)
cursor.close()
cnx.close()
def check_news(news=News):
cnx = connection.connection()
cursor = cnx.cursor()
sql = "SELECT * FROM credito WHERE titulo = %s"
titulo = (news.title[0], )
cursor.execute(sql, titulo)
rows = cursor.fetchall()
if (len(rows) > 0):
news_in_db = True
else:
news_in_db = False
cnx.close()
return news_in_db
def check(relevancia_site=Relevancia_Site):
cnx = connection.connection()
cursor = cnx.cursor()
sql = "SELECT * FROM relevancia_site WHERE site = %s"
site = (relevancia_site.site[0], )
cursor.execute(sql, site)
rows = cursor.fetchall()
if (len(rows) > 0):
site_in_db = True
else:
site_in_db = False
cnx.close()
return site_in_db
def select_news_source_categories(categorie):
cnx = connection.connection()
cursor = cnx.cursor()
query = ("SELECT site FROM pessoas WHERE categories LIKE %s")
formated_string = '%' + categorie + '%'
request_site = (formated_string, )
cursor.execute(query, request_site)
rows = cursor.fetchall()
for row in rows:
print(row)
cursor.close()
cnx.close()
return rows
def save(relevancia_site=Relevancia_Site):
cnx = connection.connection()
try:
cursor = cnx.cursor()
add_news = ("INSERT INTO relevancia_site "
"(id, site, relevancia, relevancia_inicial) "
"VALUES (NULL, %s, %s, %s)",
(relevancia_site.site[0], relevancia_site.relevancia[0],
relevancia_site.relevancia_inicial[0]))
cursor.execute(*add_news)
except Exception as e:
print(e)
cnx.close()
def select_form_date(request_date):
cnx = connection.connection()
cursor = cnx.cursor()
query = ("SELECT * FROM pessoas " "WHERE public_date = %s")
str_date = request_date.strftime("%Y-%m-%d")
# hire_start = datetime.date(1999, 1, 1)
# hire_end = datetime.date(1999, 12, 31)
str_date = (str_date, )
cursor.execute(query, str_date)
rows = cursor.fetchall()
print(len(rows))
cursor.close()
cnx.close()
return len(rows)
def update_site(site):
cnx = connection.connection()
cursor = cnx.cursor()
query = ("UPDATE pessoas SET site = %s WHERE link LIKE %s")
formated_string = '%' + site + '%'
val = (
site,
formated_string,
)
cursor.execute(query, val)
cnx.commit()
print(cursor.rowcount, "record(s) affected")
cursor.close()
cnx.close()
def select_site_in_link(site):
cnx = connection.connection()
cursor = cnx.cursor()
# query = ("SELECT * FROM pessoas WHERE id = %s AND link LIKE %s")
# formated_string = '%' + site + '%'
# request_site = ('1', formated_string, )
query = ("SELECT * FROM pessoas WHERE link LIKE %s")
formated_string = '%' + site + '%'
request_site = (formated_string, )
cursor.execute(query, request_site)
rows = cursor.fetchall()
# print(len(rows))
for row in rows:
print(row)
cursor.close()
cnx.close()
def save_news(news = News):
cnx = connection.connection()
try:
cursor = cnx.cursor()
# timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
# now = datetime.datetime(2009, 5, 5)
# str_now = now.date().isoformat()
str_now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
cats = Util.join_categories(news.categories[0])
add_news = ("INSERT INTO pessoas "
"(id, abstract, noticia, public_date, image, titulo, link, cheated_at, categories) "
"VALUES (NULL, %s, %s, %s, %s, %s, %s, %s, %s)", (news.abstract[0], news.news[0],
news.date[0], news.media[0],
news.title[0], news.link[0], str_now, cats))
cursor.execute(*add_news)
except Exception as e:
print(e)
cnx.close()
