Python的Mysql操作

网上好多的帖子感觉比较老了，而且千篇一律。我到mysql看了一下官网上python驱动的操作，发现与大部分网站说的都不一样。

首先安装的驱动是：

pip install mysql-connector-python

上面是在ubuntu上的命令。

安装之后，开发的样例代码如下：

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()