21.6.5.1. Tutorial: Raise Employee's Salary Using a Buffering Cursor

The following example script gives a long-overdue 15% raise effective tomorrow to all employees who joined in the year 2000 and are still with the company.

We are using buffered cursors to iterate through the selected employees. This way we do not have to fetch the rows in a new variables, but can instead use the cursor as an iterator.

Note that the script is an example; there are other ways of doing this simple task.

from __future__ import print_functionfrom decimal import Decimalfrom datetime import datetime, date, timedeltaimport mysql.connector# Connect with the MySQL Servercnx = mysql.connector.connect(user='scott', database='employees')# Get two buffered cursorscurA = cnx.cursor(buffered=True)curB = cnx.cursor(buffered=True)# Query to get employees who joined in a period defined by two datesquery = (  "SELECT s.emp_no, salary, from_date, to_date FROM employees AS e "  "LEFT JOIN salaries AS s USING (emp_no) "  "WHERE to_date = DATE('9999-01-01')"  "AND e.hire_date BETWEEN DATE(%s) AND DATE(%s)")# UPDATE and INSERT statements for the old and new salaryupdate_old_salary = (  "UPDATE salaries SET to_date = %s "  "WHERE emp_no = %s AND from_date = %s")insert_new_salary = (  "INSERT INTO salaries (emp_no, from_date, to_date, salary) "  "VALUES (%s, %s, %s, %s)")# Select the employees getting a raisecurA.execute(query, (date(2000, 1, 1), date(2000, 12, 31)))# Iterate through the result of curAfor (emp_no, salary, from_date, to_date) in curA:  # Update the old and insert the new salary  new_salary = int(round(salary * Decimal('1.15')))  curB.execute(update_old_salary, (tomorrow, emp_no, from_date))  curB.execute(insert_new_salary,               (emp_no, tomorrow, date(9999, 1, 1,), new_salary))  # Commit the changes  cnx.commit()cnx.close()