MySQL :: MySQL Connector/Python 开发者指南 :: 6.1 教程：使用缓冲游标提高员工工资

MySQL Connector/Python 开发者指南 / Connector/Python 教程 / 教程：使用缓冲游标提高员工工资

6.1 教程：使用缓冲游标提高员工工资

以下示例脚本为所有在 2000 年加入公司并至今仍在职的员工提供了期待已久的 15% 的加薪，从明天开始生效。

为了遍历选定的员工，我们使用缓冲游标。（缓冲游标在执行查询后获取并缓冲结果集的行；请参阅第 10.6.1 节 “cursor.MySQLCursorBuffered 类”。）这样，就不必在新变量中获取行。相反，游标可以用作迭代器。

注意

此脚本只是一个示例；还有其他方法可以完成这个简单的任务。

Press CTRL+C to copy
 
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()