def export_data(self):
if (self.report_table.rowCount() == 0):
gui_helper.prompt_error("Please enter a query")
return
file_path = gui_helper.prompt_file_save()
if (not file_path):
return
with PdfPages(file_path) as pdf:
conn = database.get_db_connection()
for i in range(self.report_table.rowCount()):
query_text = self.report_table.item(i, 1).text()
graph_type = self.report_table.item(i, 0).text()
dict_values = query.manual_sql_query(conn, query_text)
try:
plt = self.export_graph_options[graph_type](graph_type,
dict_values)
fig = plt.gcf()
pdf.savefig()
plt.close(fig)
except Exception as e:
gui_helper.prompt_error(
"Failed to export graph {}: ".format(i) + repr(e))
conn.close()
gui_helper.prompt_information(
"Graphs has been successfully exported")
def get_viewable_accounts(user_id):
db = get_db_connection()
c = db.cursor()
c.execute(
"""SELECT a.account_id FROM accounts AS a JOIN account_permissions AS ap ON ap.account_id = a.account_id
JOIN permissions AS p ON p.permission_id = ap.permission_id WHERE ap.user_id = ? AND p.name = 'view'""",
(user_id, ))
return [x["account_id"] for x in c.fetchall()]
def parse_url(self):
db = database.get_db_connection()
image_path = ''
for item in item_list:
news ={}
title = item.find('title').text
href_link = item.find('link').text
publish_date = item.find('pubdate').text
description = item.find('description').text
match=re.search(r'(.*)\.<img', description)
if match:
description = match.group(1)
else:
description = ''
news['title'] = title
#print title
news['description'] = description
news['link'] = href_link
news['pubDate'] = publish_date
news['created_date'] = datetime.datetime.utcnow()
req = request.get(href_link, headers = appconfig. get_config(appconfig.REQ_HEADERS_MOZILLA), allow_redirects = True)
soup = Soup(req.content)
tag = soup.find("div",{"id": "hcenter"})
size = size = 256, 256
if tag:
image_link = (tag.find('img'))['src']
req = request.get(image_link)
image_name =regex.remove_white_spaces.sub('',title[:10]+".jpg")
path = appconfig.get_config(appconfig.IMAGE_PATH)
path = (path['thumbnail_path']).strip()
now = datetime.datetime.now()
folder_name = str(now.strftime("%Y_%m_%d"))
path = path+folder_name+"/"
if not os.path.exists(path):
os.makedirs(path)
try:
db.news.insert(news)
if image_link:
image_path = image.create_thumbnail(size, req.content, image_name, path)
news['thumbnail_path'] = image_path
except DuplicateKeyError :
print 'duplicate title found'
pass
'''
def __init__(self):
super(QWidget, self).__init__()
conn = database.get_db_connection()
if conn.is_connected():
print('Connected to MySQL database')
else:
print('Not connected to MySQL database')
self.layout = QGridLayout(self)
self.queries_label = QLabel('Preset Queries')
self.cb = QComboBox()
# NOTE THIS INTERFACE USES FUNCTIONALITY FROM PRESETQUERY.PY
# TO RUN THIS INTERFACE YOU MUST CREATE A TABLE AS DESCRIBED
# IN PRESETQUERY.PY AND EDIT CONFIG.PY SO THAT DATABASE CONNECTION
# OBJECTS POINTS TO THE DATABASE CONTAINING THAT QUERY
# finding number of preset queries
cursor = conn.cursor()
quer = "SELECT MAX(id) FROM Presets"
cursor.execute(quer)
row = cursor.fetchone()
strrow = str(row)[1:-2]
numvals = int(strrow)
# looping through preset queries and adding them to combobox
j = 1
while j <= numvals:
optioni = (presetquery.get_descriptin(conn, j))
self.cb.addItem(str(j) + ") " + str(optioni))
j += 1
conn.close()
# adding other elements
self.b1 = QRadioButton("CSV")
self.b1.setChecked(True)
self.b2 = QRadioButton("PDF")
self.fileName_label = QLabel("Save file name as:")
self.fileName_field = QLineEdit(self)
self.generate = QPushButton("Generate", self)
self.generate.clicked.connect(self.on_click)
self.layout.addWidget(self.queries_label, 0, 0)
self.layout.addWidget(self.cb, 0, 1)
self.layout.addWidget(self.b1, 1, 0)
self.layout.addWidget(self.b2, 1, 1)
self.layout.addWidget(self.fileName_label, 2, 0)
self.layout.addWidget(self.fileName_field, 2, 1)
self.layout.addWidget(self.generate, 3, 1)
self.setLayout(self.layout)
def remove_preset(query_id):
# to use this method you must pass in a connection, and
# what number the preset's id is
conn = database.get_db_connection()
conn.autocommit = True
cursor = conn.cursor()
quer = "DELETE FROM Presets WHERE id = %s"
cursor.execute(quer, (query_id, ))
conn.close()
def update_assigned_task(task_id: str, output_file_id: str):
log("Registrando resultados de la tarea...")
db = get_db_connection()
cur = db.cursor()
cur.execute(
"UPDATE public.operation_task SET finished_at = NOW(), output_file = %s WHERE id = %s;",
(output_file_id, task_id))
db.commit()
cur.close()
db.close()
def make_deposit(request):
amount = Currency(request.form_data["amount"])
account_id = request.params["account_id"]
user_id = request.session["user"]["user_id"]
db = get_db_connection()
c = db.cursor()
c.execute("INSERT INTO account_transactions (account_id, amount, transaction_type_id, timestamp, source_id) VALUES(?,?,1,CURRENT_TIMESTAMP,2)",
(account_id, int(amount)))
c.execute("INSERT INTO account_transaction_user (transaction_id, user_id) VALUES(?,?)", (c.lastrowid, user_id))
db.commit()
request.session["feedback"] = ["deposit amount" + str(amount) + " for account " + account_id]
response = Redirect('/')
return response
def edit_preset(query_id, query_text, description):
'''(int, str, str) -> None'''
# to use this method you must pass in a connection,
# the id of a preset query,
# a preset query, and a description of what the query achieves
# it will update the query and description at the given id with new values.
# if queryin or descriptin = "NA" then it will not update the values written so
conn = database.get_db_connection()
conn.autocommit = True
cursor = conn.cursor()
quer = "UPDATE Presets SET querval=%s, description=%s WHERE id=%s"
cursor.execute(quer, (query_text, description, query_id))
conn.close()
def export_data(self):
query_text = self.query.toPlainText()
if (len(query_text) == 0):
prompt_error("Please enter a query")
return
try:
conn = database.get_db_connection()
dataframe = query.manual_sql_query(conn, query_text)
dialog = QFileDialog()
dialog.setFileMode(QFileDialog.AnyFile)
if dialog.exec_():
filepaths = dialog.selectedFiles()
self.file_save(filepaths[0], dataframe)
except Exception as e:
gui_helper.prompt_error(str(e))
def export(self, file_path, query_text):
'''(ExportCSV, str, str) -> None
Given a file path and sql query, export the data from that sql query
into a csv format.
Arguments:
file_path {str} -- CSV file name
query_text {str} -- query string
Returns:
None
'''
conn = database.get_db_connection()
# export query to csv file
dataframe = query.manual_sql_query(conn, query_text)
if (not file_path.endswith(".csv")):
file_path += ".csv"
dataframe.to_csv(file_path, index=False)
def write_preset(query_text, query_desc):
# to use this method you must pass in a connection,
# a preset query, and a description of what the query achieves
# it will automatically write it to the bottom of the table
conn = database.get_db_connection()
conn.autocommit = True
cursor = conn.cursor()
extable = Table('Presets')
q = MySQLQuery.into(extable).columns("querval", "description").insert(
query_text, query_desc)
quer = str(q)
cursor.execute(quer)
conn.close()
def on_click(self):
file_name = self.fileName_field.text()
# getting respective query based on num in description
key = (self.cb.currentText())[:1]
conn = database.get_db_connection()
quer = presetquery.get_preset(conn, key)
query_result = query.manual_sql_query(conn, quer)
conn.close()
# if select CSV
if self.b1.isChecked() == True:
# export to CSV file
print("save as csv ", exportFile.exportCSV(file_name,
query_result))
# else if select PDF
elif self.b2.isChecked() == True:
# export to PDF file
print("save as pdf ",
exportPDF.exportToPDF(file_name + '.pdf', query_result))
def make_withdrawl(request):
amount = Currency(request.form_data["amount"])
account_id = request.params["account_id"]
user_id = request.session["user"]["user_id"]
db = get_db_connection()
c = db.cursor()
c.execute(
"INSERT INTO account_transactions (account_id, amount, transaction_type_id, timestamp, source_id) VALUES(?,?,2,CURRENT_TIMESTAMP,2)",
(account_id, int(amount)))
c.execute(
"INSERT INTO account_transaction_user (transaction_id, user_id) VALUES(?,?)",
(c.lastrowid, user_id))
db.commit()
request.session["feedback"] = [
"withdrawl amount" + str(amount) + " for account " + account_id
]
response = Redirect('/')
return response
def send_mails():
print("Sending out mails...")
db = database.get_db_connection()
with db.cursor() as cursor:
sql = (
"SELECT name, kindle_address, mail_address, chapter_id "
"FROM targets JOIN chapters ON chapters.chapter_id > targets.last_chapter"
)
cursor.execute(sql)
result = cursor.fetchone()
while result:
send_mail(result[1], result[3], result[0])
result = cursor.fetchone()
with db.cursor() as cursor:
sql = (
"UPDATE targets SET last_chapter=(SELECT max(chapter_id) FROM chapters)"
)
cursor.execute(sql)
db.commit()
def update_ebook(url):
number_of_chapters = find_number_of_chapters(url)
db = database.get_db_connection()
chapters = []
changes = False
for i in range(number_of_chapters):
if database.chapter_uptodate((i + 1), db):
chapter = (database.get_chapter_info_from_db((i + 1), db))
else:
chapter = (get_chapter_info(
get_chapter_from_page(get_page(url.format(i + 1)))))
database.add_chapter_to_db(chapter[0], chapter[1], chapter[2], db)
create_ebook([chapter], 'mol-' + str(i + 1),
' - Chapter ' + (str(i + 1)))
changes = True
chapters.append(chapter)
if changes:
create_ebook(chapters, 'mol-full', '')
def logged_out_post(request):
username = request.form_data["username"]
password = request.form_data["password"]
db = get_db_connection()
c = db.cursor()
c.execute("SELECT user_id, username, email, password FROM users where username = ?", (username,))
result = c.fetchall()
if len(result) > 0:
hashed_password_bytes = result[0]["password"].encode('utf-8')
password_bytes = password.encode('utf-8')
if bcrypt.hashpw(password_bytes, hashed_password_bytes) == hashed_password_bytes:
response = OriginRedirect(request, "/")
request.session["user"] = result[0]
request.session["accounts"] = account.get_viewable_accounts(result[0]["user_id"])
else:
response = Redirect('/login');
else:
response = Redirect("/login")
response.arguments = {}
return response
def retrieve_assigned_tasks(operation_id: str) -> []:
log("Recuperando tareas asignadas para la operación actual...")
tasks = []
db = get_db_connection()
cur = db.cursor()
cur.execute(
"SELECT id, file_id, filter FROM public.operation_task WHERE operation = %s AND assigned_worker = %s;",
(operation_id, os.environ["PYXEL_WORKER_ID"]))
rows = cur.fetchall()
for row in rows:
tasks.append({
"id": row[0],
"file_id": row[1],
"filter": row[2].replace("\"", "")
})
cur.close()
db.close()
return tasks
import query
from os import path
import pandas as pd
class ExportCSV:
def export(self, file_path, query_text):
'''(ExportCSV, str, str) -> None
Given a file path and sql query, export the data from that sql query
into a csv format.
Arguments:
file_path {str} -- CSV file name
query_text {str} -- query string
Returns:
None
'''
conn = database.get_db_connection()
# export query to csv file
dataframe = query.manual_sql_query(conn, query_text)
if (not file_path.endswith(".csv")):
file_path += ".csv"
dataframe.to_csv(file_path, index=False)
if __name__ == "__main__":
connection = database.get_db_connection("root", "12345678", "world")
# query.printDB(query.get_DBinfo())
results = query.manual_sql_query(
connection, "select Name, District from city")
exportCSV("E:\project", "test.csv", results)
