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