mysql-connector-pyth

什么是MySQL Connector/Python？

Mysql Connector/python enables Python programs to access mysql databases, using an api that is compliant with the Python Database API Specification v2.0 (PEP 249). It is written in pure Python and does not have any dependencies except for the Python Standard Library.

简单来说就是Python用来连接并访问Mysql的第三方库；

安装mysql-connector-python

sht-sgmhadoopcm-01.telenav.cn:mysqladmin:/usr/local/virtualenv/test1_env:>source test1_env/bin/activate

(test1_env) sht-sgmhadoopcm-01.telenav.cn:mysqladmin:/usr/local/virtualenv/test1_env:>pip install mysql-connector-python

(test1_env) [root@sht-sgmhadoopcm-01 software]# pip list|grep mysql

mysql-connector-python 8.0.15

检查安装是否成功

(test1_env) [root@sht-sgmhadoopcm-01 software]# ls -ltrh test1_env/lib/python2.7/site-packages/mysql_connector_python-8.0.15.dist-info/

total 124K

-rw-r--r-- 1 root root  105 Feb 13 00:04 WHEEL

-rw-r--r-- 1 root root 1.5K Feb 13 00:04 METADATA

-rw-r--r-- 1 root root  94K Feb 13 00:04 LICENSE.txt

-rw-r--r-- 1 root root   40 Feb 13 00:04 top_level.txt

-rw-r--r-- 1 root root    4 Feb 13 00:04 INSTALLER

-rw-r--r-- 1 root root 8.4K Feb 13 00:04 RECORD

>>> from distutils.sysconfig import get_python_lib

>>> print get_python_lib()

/opt/software/test1_env/lib/python2.7/site-packages

连接MySQL

方法1: connect()

import mysql.connector
cnx = mysql.connector.connect(user='root', passWord='agm43gadsg',
                              host='172.16.101.54',port='3306',
                              database='testdb')
cnx.close()

方法2：MySQLConnection()

from mysql.connector import (connection)
cnx = connection.MySQLConnection(user='root', password='agm43gadsg',
                              host='172.16.101.54',port='3306',
                              database='testdb')
cnx.close()

方法3：配置文件

import mysql.connector
config = {
  'user': 'root',
  'password': 'agm43gadsg',
  'host': '172.16.101.54',
  'port': '3306',
  'database': 'testdb'
}
cnx = mysql.connector.connect(**config)
cnx.close()

创建表

from __future__ import print_function
import mysql.connector
from mysql.connector import errorcode

DB_NAME = 'testdb2'
TABLES = {}
TABLES['employees'] = (
    "CREATE TABLE `employees` ("
    "  `emp_no` int(11) NOT NULL AUTO_INCREMENT,"
    "  `birth_date` date NOT NULL,"
    "  `first_name` varchar(14) NOT NULL,"
    "  `last_name` varchar(16) NOT NULL,"
    "  `gender` enum('M','F') NOT NULL,"
    "  `hire_date` date NOT NULL,"
    "  PRIMARY KEY (`emp_no`)"
    ") ENGINE=InnoDB")
TABLES['departments'] = (
    "CREATE TABLE `departments` ("
    "  `dept_no` char(4) NOT NULL,"
    "  `dept_name` varchar(40) NOT NULL,"
    "  PRIMARY KEY (`dept_no`), UNIQUE KEY `dept_name` (`dept_name`)"
    ") ENGINE=InnoDB")
TABLES['salaries'] = (
    "CREATE TABLE `salaries` ("
    "  `emp_no` int(11) NOT NULL,"
    "  `salary` int(11) NOT NULL,"
    "  `from_date` date NOT NULL,"
    "  `to_date` date NOT NULL,"
    "  PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`),"
    "  CONSTRaiNT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) "
    "     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE"
    ") ENGINE=InnoDB")
TABLES['dept_emp'] = (
    "CREATE TABLE `dept_emp` ("
    "  `emp_no` int(11) NOT NULL,"
    "  `dept_no` char(4) NOT NULL,"
    "  `from_date` date NOT NULL,"
    "  `to_date` date NOT NULL,"
    "  PRIMARY KEY (`emp_no`,`dept_no`), KEY `emp_no` (`emp_no`),"
    "  KEY `dept_no` (`dept_no`),"
    "  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) "
    "     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,"
    "  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) "
    "     REFERENCES `departments` (`dept_no`) ON DELETE CASCADE"
    ") ENGINE=InnoDB")
TABLES['dept_manager'] = (
    "  CREATE TABLE `dept_manager` ("
    "  `dept_no` char(4) NOT NULL,"
    "  `emp_no` int(11) NOT NULL,"
    "  `from_date` date NOT NULL,"
    "  `to_date` date NOT NULL,"
    "  PRIMARY KEY (`emp_no`,`dept_no`),"
    "  KEY `emp_no` (`emp_no`),"
    "  KEY `dept_no` (`dept_no`),"
    "  CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) "
    "     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,"
    "  CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_no`) "
    "     REFERENCES `departments` (`dept_no`) ON DELETE CASCADE"
    ") ENGINE=InnoDB")
TABLES['titles'] = (
    "CREATE TABLE `titles` ("
    "  `emp_no` int(11) NOT NULL,"
    "  `title` varchar(50) NOT NULL,"
    "  `from_date` date NOT NULL,"
    "  `to_date` date DEFAULT NULL,"
    "  PRIMARY KEY (`emp_no`,`title`,`from_date`), KEY `emp_no` (`emp_no`),"
    "  CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`)"
    "     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE"
    ") ENGINE=InnoDB")
    
cnx = mysql.connector.connect(user='root', password='agm43gadsg', host='172.16.101.54',port='3306', database='testdb')
cursor = cnx.cursor()
def create_database(cursor):
    try:
        cursor.execute("CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".fORMat(DB_NAME))
    except mysql.connector.Error as err:
        print("Failed creating database: {}".format(err))
        exit(1)
try:
    cursor.execute("USE {}".format(DB_NAME))
except mysql.connector.Error as err:
    print("Database {} does not exists.".format(DB_NAME))
    if err.errno == errorcode.ER_BAD_DB_ERROR:
        create_database(cursor)
        print("Database {} created successfully.".format(DB_NAME))
        cnx.database = DB_NAME
    else:
        print(err)
        exit(1)
        
for table_name in TABLES:
    table_description = TABLES[table_name]
    try:
        print("Creating table {}: ".format(table_name), end='')
        cursor.execute(table_description)
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
            print("already exists.")
        else:
            print(err.msg)
    else:
        print("OK")
cursor.close()
cnx.close()

插入数据

from __future__ import print_function
from datetime import date, datetime, timedelta
import mysql.connector
cnx = mysql.connector.connect(user='root', password='agm43gadsg', host='172.16.101.54',port='3306', database='testdb')
cursor = cnx.cursor()
tomorrow = datetime.now().date() + timedelta(days=1)

#下面是两种格式的insert,第一种data_employee要写成tuple类型，第二种data_salary要写成字典类型
add_employee = ("INSERT INTO employees "
"(first_name, last_name, hire_date, gender, birth_date) "
"VALUES (%s, %s, %s, %s, %s)")

add_salary = ("INSERT INTO salaries "
"(emp_no, salary, from_date, to_date) "
"VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")

data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))

cursor.execute(add_employee, data_employee)
emp_no = cursor.lastrowid #打印最后一次插入emp_no列值，作为salaries表的emp_no列值,要求employees表的emp_no必须是自增主键才行，

data_salary = {
'emp_no': emp_no,
'salary': 50000,
'from_date': tomorrow,
'to_date': date(9999, 1, 1),
}

cursor.execute(add_salary, data_salary)
cnx.commit()
cursor.close()
cnx.close()

查询数据

import datetime
import mysql.connector

cnx = mysql.connector.connect(user='root', password='agm43gadsg', host='172.16.101.54',port='3306', database='testdb')
cursor = cnx.cursor()
query = ("SELECT first_name, last_name, hire_date FROM employees "
"WHERE hire_date BETWEEN %s AND %s")

hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(1999, 12, 31)

cursor.execute(query, (hire_start, hire_end))

for (first_name, last_name, hire_date) in cursor:
print("{}, {} was hired on {:%d %b %Y}".format(last_name, first_name, hire_date))

cursor.close()
cnx.close()

MySQL Connector/Python