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'}

Scheduling An Event In MySQL Server From A Python Program

Overview:

Scheduling an Event in MySQL from a Python Program:

Example:

Output: