import sys
import cx_Oracle
import pandas as pd
from PyQt5 import QtWidgets, QtCore, QtGui
from PyQt5.QtWidgets import QMainWindow, QApplication, QLabel, QDateEdit, QVBoxLayout, QPushButton, QProgressBar, QMessageBox
from PyQt5.QtCore import QDate
class MainWindow(QMainWindow):
def __init__(self):
super().__init__()
self.setWindowTitle('Oracle Query Runner')
self.setGeometry(100, 100, 400, 200)
layout = QVBoxLayout()
self.label = QLabel('Select Start Date:', self)
layout.addWidget(self.label)
self.start_date_edit = QDateEdit(self)
self.start_date_edit.setCalendarPopup(True)
self.start_date_edit.setDate(QDate.currentDate())
layout.addWidget(self.start_date_edit)
self.label_end = QLabel('Select End Date:', self)
layout.addWidget(self.label_end)
self.end_date_edit = QDateEdit(self)
self.end_date_edit.setCalendarPopup(True)
self.end_date_edit.setDate(QDate.currentDate())
layout.addWidget(self.end_date_edit)
self.progress_bar = QProgressBar(self)
layout.addWidget(self.progress_bar)
self.button = QPushButton('Run Queries', self)
self.button.clicked.connect(self.run_queries)
layout.addWidget(self.button)
container = QtWidgets.QWidget()
container.setLayout(layout)
self.setCentralWidget(container)
def run_queries(self):
start_date = self.start_date_edit.date().toString('yyyy-MM-dd')
end_date = self.end_date_edit.date().toString('yyyy-MM-dd')
self.progress_bar.setValue(0)
try:
# Establish connection to the Oracle database
dsn_tns = cx_Oracle.makedsn('host', 'port', service_name='service_name')
connection = cx_Oracle.connect(user='username', password='password', dsn=dsn_tns)
queries = [
# Add your 10 queries here. For example:
f"SELECT * FROM table WHERE date_column BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')",
# ... more queries
]
# Read existing Excel file
excel_file = 'path_to_existing_excel_file.xlsx'
writer = pd.ExcelWriter(excel_file, engine='openpyxl', mode='a', if_sheet_exists='overlay')
workbook = writer.book
sheet_name = workbook.sheetnames[2] # Sheet number 3
# Clear the existing data in the sheet except the header
sheet = workbook[sheet_name]
for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row):
for cell in row:
cell.value = None
for idx, query in enumerate(queries, start=1):
self.status_update(f"Running query {idx}...")
df = pd.read_sql(query, con=connection)
if idx == 1:
df.to_excel(writer, sheet_name=sheet_name, index=False, startrow=0)
else:
df.to_excel(writer, sheet_name=sheet_name, index=False, header=False, startrow=writer.sheets[sheet_name].max_row)
self.progress_bar.setValue((idx / len(queries)) * 100)
writer.save()
connection.close()
self.show_popup('Success', 'All queries executed successfully.')
except Exception as e:
self.show_popup('Error', str(e))
def status_update(self, message):
self.progress_bar.setFormat(message)
QtCore.QCoreApplication.processEvents()
def show_popup(self, title, message):
msg = QMessageBox()
msg.setIcon(QMessageBox.Information)
msg.setText(message)
msg.setWindowTitle(title)
msg.setStandardButtons(QMessageBox.Ok)
msg.exec_()
if name == ‘main‘:
app = QApplication(sys.argv)
main_window = MainWindow()
main_window.show()
sys.exit(app.exec_())
How to post this code from my mobile to a VDI?
New contributor
Sekhar K is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1