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) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Fifo table is feeded with: