PyMYSQL example fifo queue

mysqlfifo.py

#!/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)

Compresion does not work on pymysql

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