Scheduling an event in MySQL Server from a Python Program

An event can be scheduled in a MySQL Server instance using the SQL Statement CREATE EVENT. The Python example program creates an event in a MySQL server running on the local host and retrieves it using SHOW EVENT to print the event details.

MySQL Updated 19 Jun 2019 2 min read

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

python
# ----- 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

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