Last active
May 13, 2021 00:00
-
-
Save amon-ra/d8b5fdc25fbd7727d30205a52627e43a to your computer and use it in GitHub Desktop.
PyMYSQL example fifo queue
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/python | |
#pyinstaller mysql-python | |
# | |
import signal | |
import time | |
import logging | |
import pymysql | |
import sys | |
host1 = 'localhost' | |
user1 = 'user1' | |
password1 = 'prueba' | |
port1 = 3306 | |
db1 = 'moodle_svc' | |
compress1=False | |
host2 = 'localhost' | |
user2 = 'user2' | |
password2 = 'prueba' | |
port2 = 3306 | |
db2 = 'moodle' | |
compress2=False | |
# CREATE TABLE fifo ( | |
# id int NOT NULL AUTO_INCREMENT, | |
# data varchar(500) NOT NULL, | |
# PRIMARY KEY(id) | |
# ) | |
# | |
# conn = MySQLdb.Connection( | |
# host=host, | |
# user=user, | |
# passwd=password, | |
# port=port, | |
# db=db | |
# ) | |
# | |
# # Example of how to insert new values: | |
# conn.query("""INSERT INTO mytable VALUES ('foo3', 'bar2')""") | |
# conn.commit() | |
# | |
# # Example of how to fetch table data: | |
# conn.query("""SELECT * FROM mytable""") | |
# result = conn.store_result() | |
# for i in range(result.num_rows()): | |
# print(result.fetch_row()) | |
#logging.basicConfig( | |
#filename = 'c:\\moodle-service.log', | |
#level = logging.DEBUG, | |
#format = '[moodle-service] %(levelname)-7.7s %(message)s' | |
#) | |
class GracefulKiller: | |
kill_now = False | |
def __init__(self): | |
signal.signal(signal.SIGINT, self.exit_gracefully) | |
signal.signal(signal.SIGTERM, self.exit_gracefully) | |
def exit_gracefully(self,signum, frame): | |
self.kill_now = True | |
class MoodleSvc: | |
_svc_name_ = "Moodle-Service" | |
_svc_display_name_ = "Moodle Service" | |
def __init__(self,sec=5,debug=False): | |
self.killer = GracefulKiller() | |
self.trace = debug | |
self.sleep = sec | |
def log(self,msg): | |
print(msg) | |
def debug(self,msg): | |
if self.trace: | |
print(msg) | |
def conn(self): | |
try: | |
self.con1 = pymysql.connect( | |
host=host1, | |
user=user1, | |
passwd=password1, | |
port=port1, | |
compress=compress1, | |
db=db1 | |
) | |
self.con2 = pymysql.connect( | |
host=host2, | |
user=user2, | |
passwd=password2, | |
port=port2, | |
compress=compress2, | |
db=db2 | |
) | |
except Exception as e: | |
self.log("Exception on mysql connect") | |
self.log(e) | |
def main(self): | |
self.log(' ** Moodle sync started ** ') | |
# Simulate a main loop | |
rc = None | |
while not self.killer.kill_now: | |
# # Example of how to insert new values: | |
# conn.query("""INSERT INTO mytable VALUES ('foo3', 'bar2')""") | |
# conn.commit() | |
# | |
# # Example of how to fetch table data: | |
# conn.query("""SELECT * FROM mytable""") | |
# result = conn.store_result() | |
# for i in range(result.num_rows()): | |
# print(result.fetch_row()) | |
# get data from db1 | |
try: | |
self.conn() | |
cur1 = self.con1.cursor(pymysql.cursors.DictCursor) | |
cur2 = self.con2.cursor(pymysql.cursors.DictCursor) | |
cur1.execute("""SELECT id,data FROM fifo ORDER BY id DESC""") | |
max_id=0 | |
cmd_list="" | |
for row in cur1.fetchall(): | |
self.debug(row) | |
if row['data'][-1] == ";": | |
cmd_list+=row['data'] | |
else: | |
cmd_list+=row['data']+";" | |
max_id=int(row['id']) | |
cur1.close() | |
cur1 = self.con1.cursor() | |
if cmd_list != "": | |
self.debug(cmd_list) | |
self.debug("DELETE FROM fifo WHERE id <= "+str(max_id)) | |
cur2.execute(cmd_list) | |
self.con2.commit() | |
cur1.execute("DELETE FROM fifo WHERE id <= "+str(max_id)) | |
self.con1.commit() | |
cur2.close() | |
cur1.close() | |
except Exception as e: | |
self.log("Exception on execution") | |
self.log(e) | |
self.conn() | |
finally: | |
try: | |
self.con2.close() | |
except: | |
pass | |
try: | |
self.cur1.close() | |
except: | |
pass | |
try: | |
time.sleep(self.sleep) | |
except: | |
pass | |
return | |
# if __name__ == '__main__': | |
# win32serviceutil.HandleCommandLine(MoodleSvc) | |
if __name__ == '__main__': | |
sec = 5 | |
debug=False | |
if len(sys.argv) > 1: | |
sec=sys.argv[1] | |
if len(sys.argv) > 2: | |
debug=True | |
svc = MoodleSvc(sec,debug) | |
svc.main() | |
svc.log("Finished") | |
sys.exit(0) |
Fifo table is feeded with:
CREATE DEFINER=`root`@`%` PROCEDURE `actualizarUsuariosMoodle`(DNIempleado VARCHAR(45))
BEGIN
DECLARE user VARCHAR(45);
DECLARE pass VARCHAR(255);
DECLARE nombre VARCHAR(100);
DECLARE suspendido TINYINT(1);
DECLARE mail VARCHAR(100);
DECLARE idusuario VARCHAR(45);
DECLARE dni2 VARCHAR(45);
DECLARE activo2 BIT(1);
DECLARE nombre2 VARCHAR(100);
DECLARE user2 VARCHAR(45);
DECLARE pass2 VARCHAR(40);
DECLARE email2 VARCHAR(100);
DECLARE emailpreferido2 VARCHAR(100);
DECLARE done BOOLEAN;
DECLARE done2 BOOLEAN;
DECLARE email_val VARCHAR(100);
#DECLARE cursor_usuarios_moodle CURSOR FOR
# SELECT idnumber FROM moodle.mdl_user WHERE idnumber = DNIempleado COLLATE utf8_unicode_ci;
DECLARE cursor_vista_empleados CURSOR FOR
SELECT DNI,activo,nombreCompleto,usuario,password,email,emailPreferido FROM vista_empleados WHERE DNI = DNIempleado;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET done = FALSE;
SET done2 = FALSE;
OPEN cursor_vista_empleados;
bucle3: LOOP
FETCH cursor_vista_empleados INTO dni2,activo2,nombre2,user2,pass2,email2,emailpreferido2;
IF done THEN
LEAVE bucle3;
END IF;
SET user = (SELECT user2);
SET pass = (SELECT pass2) COLLATE utf8_general_ci;
SET nombre = (SELECT nombre2);
IF (select activo2) THEN
SET suspendido = 0;
ELSE
SET suspendido = 1;
END IF;
IF (SELECT emailpreferido2) IS NOT NULL and emailpreferido2 != '' THEN
SET mail = (SELECT emailpreferido2);
ELSE
SET mail = (SELECT email2);
END IF;
IF (SELECT dni2) = DNIempleado THEN
SET done = TRUE;
END IF;
END LOOP;
CLOSE cursor_vista_empleados;
#SET done = FALSE;
#
#OPEN cursor_usuarios_moodle;
#bucle: LOOP
# FETCH cursor_usuarios_moodle INTO idusuario;
# IF done THEN
# LEAVE bucle;
# END IF;
#
#IF (SELECT idusuario) = DNIempleado THEN
# SET done = TRUE;
# SET done2 = TRUE;
#END IF;
#
#END LOOP;
#CLOSE cursor_usuarios_moodle
SET email_val = "";
IF mail IS NOT NULL THEN
SET email_val = CONCAT(" ,email= '",mail,"'");
END IF;
INSERT INTO moodle_svc.fifo (data) VALUES (CONCAT("INSERT INTO moodle.mdl_user ",
"(mnethostid,username,password,idnumber,confirmed,suspended,firstname,lastname,email,",
"timecreated) SELECT 1,'",user,"','", pass,"','", DNIempleado COLLATE utf8_unicode_ci,"',","1",",",
suspendido,",'",nombre,"',","'.'",",","''",",", UNIX_TIMESTAMP(NOW( ))," FROM moodle.mdl_user ",
" WHERE NOT EXISTS (SELECT idnumber FROM moodle.mdl_user WHERE idnumber ='",DNIempleado,"') LIMIT 1;",
"UPDATE moodle.mdl_user SET username ='",user,"',password ='",pass,"',suspended =", suspendido,",firstname ='", nombre,"'",
email_val," WHERE idnumber = '",DNIempleado COLLATE utf8_unicode_ci,"' AND deleted = 0;"
));
END
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Compresion does not work on pymysql