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_function from decimal import Decimal from datetime import datetime, date, timedelta import mysql.connector # Connect with the MySQL Server cnx = mysql.connector.connect(user='scott', database='employees') # Get two buffered cursors curA = cnx.cursor(buffered=True) curB = cnx.cursor(buffered=True) # Query to get employees who joined in a period defined by two dates query = ( "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 salary update_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 raise curA.execute(query, (date(2000, 1, 1), date(2000, 12, 31))) # Iterate through the result of curA for (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()
Copyright © 2012, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices