def list_version(server):
sqliteConnection, cursor = db_connection()
try:
if server == "MySQL":
list_version = """select DISTINCT name_version from {}""".format(
'Mysql_Scripts')
list_version = cursor.execute(list_version)
return list_version
else:
list_version = """select DISTINCT name_version from {}""".format(
'SqlServer_Scripts')
list_version = cursor.execute(list_version)
return list_version
except sqlite3.Error as e:
print(e)
def save_connection(db_type, host, port, user, password, dbname):
sqliteConnection, cursor = db_connection()
save_connection = False
if db_type == "MySQL":
try:
sql_update_query = """Update parametrage_serveur set host = ?, port = ?, user = ?, password = ?, dbName = ? where type = ?"""
val = (host, port, user, password, dbname, db_type)
cursor.execute(sql_update_query, val)
sqliteConnection.commit()
print("Mysql Record Updated successfully ")
cursor.close()
if (sqliteConnection):
sqliteConnection.close()
save_connection = True
return save_connection
except:
admin(
'SVP Lancer l\'application En tant qu\'Administrateur '
'pour que vous puissiez soumettre vos changement à la base de donnée !'
)
return save_connection
else:
try:
sql_update_query = """Update parametrage_serveur set host = ?, port = ?, user = ?, password = ?, dbName = ? where type = ?"""
val = (host, port, user, password, dbname, db_type)
cursor.execute(sql_update_query, val)
sqliteConnection.commit()
print("SQL server Record Updated successfully ")
cursor.close()
if (sqliteConnection):
sqliteConnection.close()
save_connection = True
return save_connection
except:
admin(
'SVP Lancer l\'application En tant qu\'Administrateur '
'pour que vous puissiez soumettre vos changement à la base de donnée !'
)
return save_connection
def add_version(self):
sqliteConnection, cursor = db_connection()
nom_version_txt = self.nom_version.text()
if nom_version_txt != "":
try:
add_query = """ INSERT INTO Version(name_version) VALUES(?);"""
name_to_add = (nom_version_txt, )
cursor.execute(add_query, name_to_add)
sqliteConnection.commit()
print("name added successfully ")
self.nom_version.clear()
except sqlite3.Error as error:
if str(error)[:6] == "UNIQUE":
my_fnc.show_popup(self, 'version_exist')
print('Version already exists')
else:
print('please enter version name')
my_fnc.show_popup(self, 'version_name')
def save_scripts(path):
script_saved = False
script = ""
jackpot_regex = r"(^\s*\*)|(^\s*/)|(^\s*-)|(^\s*\*)"
sqliteConnection, cursor = db_connection()
version_names_list = os.listdir(path)
for version_name in version_names_list:
version_dir_path = os.path.join(path, version_name)
for subdir, dirs, files in os.walk(version_dir_path):
if re.search(".*(\\\mysql)$", subdir) is not None:
for filename in os.listdir(subdir):
filepath = os.path.join(subdir, filename)
with open(filepath, 'r', errors='ignore') as f:
lines = f.readlines()
for line in lines:
clean_line = line.rstrip()
if not re.match(jackpot_regex, clean_line):
# if not clean_line.startswith('-') and not clean_line.startswith(
# '/') and not clean_line.startswith(' *'):
script += clean_line
# insert query here
try:
insert_script_query = """INSERT INTO Mysql_Scripts(name_version, script) values(?,?);"""
val = (version_name, script)
cursor.execute(insert_script_query, val)
sqliteConnection.commit()
print("MysqlServer Record Updated successfully ")
script = ""
script_saved = True
except sqlite3.Error as err:
print(err)
elif re.search(".*(\\\sqlserver)$", subdir) is not None:
for filename in os.listdir(subdir):
filepath = os.path.join(subdir, filename)
with open(filepath, 'r', errors='ignore') as f:
lines = f.readlines()
for line in lines:
clean_line = line.rstrip()
if not re.match(jackpot_regex, clean_line):
# if not clean_line.startswith('-') and not clean_line.startswith(
# '/') and not clean_line.startswith(' *'):
script += clean_line
# insert query here
try:
insert_script_query = """INSERT INTO SqlServer_Scripts(name_version, script) values(?,?);"""
val = (version_name, script)
cursor.execute(insert_script_query, val)
sqliteConnection.commit()
print("SqlServer Record Updated successfully")
script = ""
script_saved = True
except sqlite3.Error as err:
print(err)
return script_saved
def setupUi(self, Mysql):
Mysql.setObjectName("Mysql")
# Mysql.resize(419, 358)
Mysql.setFixedSize(419, 358)
palette = QtGui.QPalette()
brush = QtGui.QBrush(QtGui.QColor(255, 255, 255))
brush.setStyle(QtCore.Qt.SolidPattern)
palette.setBrush(QtGui.QPalette.Active, QtGui.QPalette.Base, brush)
brush = QtGui.QBrush(QtGui.QColor(0, 0, 127))
brush.setStyle(QtCore.Qt.SolidPattern)
palette.setBrush(QtGui.QPalette.Active, QtGui.QPalette.Window, brush)
brush = QtGui.QBrush(QtGui.QColor(255, 255, 255))
brush.setStyle(QtCore.Qt.SolidPattern)
palette.setBrush(QtGui.QPalette.Inactive, QtGui.QPalette.Base, brush)
brush = QtGui.QBrush(QtGui.QColor(0, 0, 127))
brush.setStyle(QtCore.Qt.SolidPattern)
palette.setBrush(QtGui.QPalette.Inactive, QtGui.QPalette.Window, brush)
brush = QtGui.QBrush(QtGui.QColor(0, 0, 127))
brush.setStyle(QtCore.Qt.SolidPattern)
palette.setBrush(QtGui.QPalette.Disabled, QtGui.QPalette.Base, brush)
brush = QtGui.QBrush(QtGui.QColor(0, 0, 127))
brush.setStyle(QtCore.Qt.SolidPattern)
palette.setBrush(QtGui.QPalette.Disabled, QtGui.QPalette.Window, brush)
Mysql.setPalette(palette)
self.verticalLayout = QtWidgets.QVBoxLayout(Mysql)
self.verticalLayout.setObjectName("verticalLayout")
self.param_serveur = QtWidgets.QGroupBox(Mysql)
palette = QtGui.QPalette()
brush = QtGui.QBrush(QtGui.QColor(255, 255, 255))
brush.setStyle(QtCore.Qt.SolidPattern)
palette.setBrush(QtGui.QPalette.Active, QtGui.QPalette.WindowText,
brush)
brush = QtGui.QBrush(QtGui.QColor(255, 255, 255))
brush.setStyle(QtCore.Qt.SolidPattern)
palette.setBrush(QtGui.QPalette.Inactive, QtGui.QPalette.WindowText,
brush)
brush = QtGui.QBrush(QtGui.QColor(120, 120, 120))
brush.setStyle(QtCore.Qt.SolidPattern)
palette.setBrush(QtGui.QPalette.Disabled, QtGui.QPalette.WindowText,
brush)
self.param_serveur.setPalette(palette)
self.param_serveur.setObjectName("param_serveur")
self.host_label = QtWidgets.QLabel(self.param_serveur)
self.host_label.setGeometry(QtCore.QRect(20, 50, 47, 13))
self.host_label.setObjectName("host_label")
self.port_label = QtWidgets.QLabel(self.param_serveur)
self.port_label.setGeometry(QtCore.QRect(20, 100, 47, 13))
self.port_label.setObjectName("port_label")
self.user_label = QtWidgets.QLabel(self.param_serveur)
self.user_label.setGeometry(QtCore.QRect(20, 150, 47, 13))
self.user_label.setObjectName("user_label")
self.pass_label = QtWidgets.QLabel(self.param_serveur)
self.pass_label.setGeometry(QtCore.QRect(20, 200, 60, 16))
self.pass_label.setObjectName("pass_label")
self.host_edit = QtWidgets.QLineEdit(self.param_serveur)
self.host_edit.setGeometry(QtCore.QRect(110, 40, 201, 31))
self.host_edit.setObjectName("host_edit")
self.user_edit = QtWidgets.QLineEdit(self.param_serveur)
self.user_edit.setGeometry(QtCore.QRect(110, 140, 201, 31))
self.user_edit.setObjectName("user_edit")
self.pass_edit = QtWidgets.QLineEdit(self.param_serveur)
self.pass_edit.setGeometry(QtCore.QRect(110, 190, 201, 31))
self.pass_edit.setEchoMode(QtWidgets.QLineEdit.Password)
self.pass_edit.setObjectName("pass_edit")
self.save_btn = QtWidgets.QPushButton(self.param_serveur)
self.save_btn.setGeometry(QtCore.QRect(160, 290, 120, 30))
self.save_btn.setCursor(QtGui.QCursor(QtCore.Qt.PointingHandCursor))
self.save_btn.setStyleSheet("QPushButton:hover:!pressed\n"
"{\n"
" border: 1px solid lime;\n"
"background-color: rgb(0, 0, 0);\n"
"font: 30 12pt \"Cambria\";\n"
"color: rgb(255, 255, 255);\n"
"}\n"
"\n"
"QPushButton\n"
"{\n"
"background-color: rgb(0, 0, 0);\n"
"font: 30 12pt \"Cambria\";\n"
"color: rgb(255, 255, 255);\n"
"padding:2px;\n"
"}\n"
"")
self.save_btn.setObjectName("save_btn")
self.db_label = QtWidgets.QLabel(self.param_serveur)
self.db_label.setGeometry(QtCore.QRect(20, 250, 51, 16))
self.db_label.setObjectName("db_label")
self.db_edit = QtWidgets.QLineEdit(self.param_serveur)
self.db_edit.setGeometry(QtCore.QRect(110, 240, 201, 31))
self.db_edit.setObjectName("db_edit")
self.port_nbr = QtWidgets.QSpinBox(self.param_serveur)
self.port_nbr.setGeometry(QtCore.QRect(110, 90, 71, 31))
self.port_nbr.setMaximum(65535)
self.port_nbr.setObjectName("port_nbr")
self.verticalLayout.addWidget(self.param_serveur)
self.retranslateUi(Mysql)
QtCore.QMetaObject.connectSlotsByName(Mysql)
""" my changes in view"""
self.save_btn.setIcon(QIcon('Save_16x16.png'))
self.save_btn.setIconSize(QSize(16, 16))
sqliteConnection, cursor = conn.db_connection()
try:
sql_select_query = """select host,port,user,password,dbName from parametrage_serveur where type='SQL Server'"""
col = cursor.execute(sql_select_query)
for i in col:
self.host = i[0]
self.port = i[1]
self.user = i[2]
self.password = i[3]
self.dbname = i[4]
self.host_edit.setText(str(self.host))
if self.port == "":
self.port_nbr.setValue(0)
else:
self.port_nbr.setValue(int(self.port))
self.user_edit.setText(str(self.user))
self.pass_edit.setText(str(self.password))
self.db_edit.setText(str(self.dbname))
except sqlite3.Error as error:
print("Error while connecting to sqlite", error)
def save_to_db(self):
conn.db_connection('sqlserver')
def run_script(self):
jackpot_regex = r"(^\s*\*)|(^\s*/)|(^\s*-)|(^\s*\*)"
queries = ""
nb_query_executed = 0
nb_query_failed = 0
dir_path = r'C:\users\%username%\Desktop\ScriptTool_Log'
timestump = datetime.today().strftime('%Y-%m-%d-%H_%M_%S')
errors = ""
creation_errors = False
except_to_use = ""
if self.mis_a_jour.isChecked():
self.log_area.clear()
version_debut = self.select_version_debut_val
version_fin = self.select_version_fin_val
server = self.select_server_val
# dir_path = r'C:\users\%username%\Desktop\ScriptTool_Log'
# timestump = datetime.today().strftime('%Y-%m-%d-%H_%M_%S')
sqliteConnection, cursor = conn.db_connection()
try:
sql_select_query = """select host,port,user,password,dbName from parametrage_serveur where type=?"""
val = (self.select_server_val, )
col = cursor.execute(sql_select_query, val)
for i in col:
self.host = i[0]
self.port = i[1]
self.user = i[2]
self.password = i[3]
self.dbname = i[4]
except sqlite3.Error as e:
my_fnc.db_errors(str(e))
if self.host == "" and self.port == "" and self.user == "" and self.password == "" and self.dbname == "":
# print('popup SVP parameter sever first')
my_fnc.show_popup(self, 'param_server')
else:
server_connection, server_cursor = conn.db_connection_server(
self.select_server_val, self.host, self.port, self.user,
self.password, self.dbname)
if str(server_connection) != 'None':
if version_debut != "" and version_fin != "":
end_with_err, errors, queries_executed, nb_query_executed, nb_query_failed, server_type = my_fnc.execute_script(
self, server, version_debut, version_fin,
server_connection, server_cursor, cursor)
if end_with_err and queries_executed != "":
# put errors in log file
my_fnc.createDirectory(
os.path.join(dir_path, server_type))
with open(
os.path.expandvars(
os.path.join(dir_path, server_type)) +
r'\Log_' + timestump + '.log',
'w') as log_file:
log_file.write(str(errors))
# self.log_area.setPlainText('Requêtes Exécutées avec Succès:\n\n' + str(queries_executed))
# self.log_area.setStyleSheet("QPlainTextEdit{color:green}")
# self.log_area.setPlainText('Résumé de l\'opération:\n\n' + 'Nombre de Requêtes éxecutées Avec Succès: '+str(nb_query_executed)+'\n'+
# 'Nombre de Requêtes échouées: '+str(nb_query_failed))
self.log_area.clear()
self.log_area.appendHtml(
'<html><p>Résumé de l\'opération:</p><p style="color:green;">Nombre de Requêtes éxecutées Avec Succès: '
+ str(nb_query_executed) +
'</p></br><p style="color:red;">Nombre de Requêtes échouées: '
+ str(nb_query_failed) + '</p></html>')
# self.log_area.setStyleSheet("QPlainTextEdit{color:green}")
my_fnc.end_with_err(os.path.expandvars(dir_path))
# my_fnc.show_popup(self, 'end_with_err')
elif end_with_err:
# put errors in log file
my_fnc.createDirectory(
os.path.join(dir_path, server_type))
with open(
os.path.expandvars(
os.path.join(dir_path, server_type)) +
r'\Log_' + timestump + '.log',
'w') as log_file:
log_file.write(str(errors))
self.log_area.setPlainText(
'Résumé de l\'opération:\n' +
'Nombre de Requêtes échouées: ' +
str(nb_query_failed))
my_fnc.end_with_err(os.path.expandvars(dir_path))
elif queries_executed != "" and not end_with_err:
self.log_area.setPlainText(
'Résumé de l\'opération:\n' +
'Nombre de Requêtes éxecutées Avec Succès: ' +
str(nb_query_executed))
my_fnc.show_popup(self, 'end_ok')
else:
my_fnc.show_popup(self, 'version_empty')
else:
print('server connection error !')
else:
""" Add creation logic here """
if self.select_server_val == "MySQL":
except_to_use = mysql.connector.Error
else:
except_to_use = pyodbc.Error
sqliteConnection, cursor = conn.db_connection()
try:
sql_select_query = """select host,port,user,password,dbName from parametrage_serveur where type=?"""
val = (self.select_server_val, )
col = cursor.execute(sql_select_query, val)
for i in col:
self.host = i[0]
self.port = i[1]
self.user = i[2]
self.password = i[3]
self.dbname = i[4]
except sqlite3.Error as e:
my_fnc.db_errors(str(e))
if self.host == "" and self.port == "" and self.user == "" and self.password == "" and self.dbname == "":
# print('popup SVP parameter sever first')
my_fnc.show_popup(self, 'param_server')
else:
server_connection, server_cursor = conn.db_connection_server(
self.select_server_val, self.host, self.port, self.user,
self.password, self.dbname)
if self.creation_edit.text() != "":
with open(self.creation_edit.text(), 'r') as f:
lines = f.readlines()
for line in lines:
clean_line = line.rstrip()
if not re.match(jackpot_regex, clean_line):
queries += clean_line
queries = filter(None, queries.split(';'))
for i in queries:
query = i.strip() + ';'
# insert query here
try:
server_cursor.execute(query)
server_connection.commit()
print("Script inserted successfully ")
QApplication.processEvents()
self.log_area.setPlainText(str(query))
nb_query_executed += 1
except except_to_use as err:
my_fnc.createDirectory(
os.path.join(dir_path, self.select_server_val))
errors += str(err) + '\n'
nb_query_failed += 1
creation_errors = True
if creation_errors:
with open(
os.path.expandvars(
os.path.join(dir_path,
self.select_server_val)) +
r'\Log_' + timestump + '.log',
'w') as log_file:
log_file.write(errors)
my_fnc.end_with_err(os.path.expandvars(dir_path))
self.log_area.clear()
self.log_area.appendHtml(
'<html><p>Résumé de l\'opération:</p><p style="color:green;">Nombre de Requêtes éxecutées Avec Succès: '
+ str(nb_query_executed) +
'</p></br><p style="color:red;">Nombre de Requêtes échouées: '
+ str(nb_query_failed) + '</p></html>')
else:
my_fnc.show_popup(self, 'script_creation')