Scheduling An Event In MySQL Server From A Python Program

Overview:

  • It is a common programming task to schedule an action to occur once or several times in the future.
  • In MySQL server too events can be scheduled to perform tasks like incrementing a counter, updating a set of columns in a database table, deleting a set of rows from a table and so on.
  • Events can be scheduled in MySQL Server using the SQL Statement CREATE EVENT.
  • An event in MySQL Server can be scheduled for only once at a specific time or several times upon expiry of a time interval.
  • To create an event the user should have the EVENT privilege on the database.
  • For the event to be executed the thread Event Scheduler should be running.

 

Scheduling an Event in MySQL from a Python Program:

  • Import the PyMySQL module and create a connection object to the MySQL database.
  • Obtain a cursor object and execute the CREATE EVENT statement, which will schedule an event.

Example:

# ----- Example Python program to schedule an event in MySQL server -----

import pymysql

# Method that returns a connection to the MySQL Server
def getDBConnection(ip, user, pwd, charset, cursorType):
    sqlCon  = pymysql.connect(host=ip, user=user, password=pwd, charset=charset, cursorclass=cursorType);
    return sqlCon;

# DB connection parameters
ip          = "127.0.0.1";  # MySQL server is running on local machine
user        = "root";        
pwd         = "";            
charset     = "utf8mb4";     
cursorType  = pymysql.cursors.DictCursor;

# Connect to MySQL database server
dbcon = getDBConnection(ip, user, pwd, charset, cursorType);

try:
    # Database cursor
    dbCursor    = dbcon.cursor();                                  

    # SQL statement to create an event in MySQL
    sqlCreateEvent = """CREATE EVENT test.StatusUpdate 
                        ON SCHEDULE EVERY 1 Hour DO DELETE FROM test.Orders where OrderStatus= 'Expired';"""

    # Execute the SQL statement 
    dbCursor.execute(sqlCreateEvent);

    sqlShowEvents = "SHOW EVENTS FROM test";
    dbCursor.execute(sqlShowEvents);

    # Fetch all the rows
    eventList = dbCursor.fetchall();
    
    print("List of events:");
    for event in eventList:
        print(event);

except Exception as e:
    print("Exception occured:{}".format(e));

finally:
    dbCursor.close();
    dbcon.close();

Output:

 

List of events:

{'Db': 'test', 'Name': 'StatusUpdate', 'Definer': 'root@localhost', 'Time zone': 'SYSTEM', 'Type': 'RECURRING', 'Execute at': None, 'Interval value': '1', 'Interval field': 'HOUR', 'Starts': datetime.datetime(2019, 6, 19, 1, 40, 50), 'Ends': None, 'Status': 'ENABLED', 'Originator': 0, 'character_set_client': 'utf8mb4', 'collation_connection': 'utf8mb4_unicode_ci', 'Database Collation': 'latin1_swedish_ci'}

 


Copyright 2020 © pythontic.com