mysql-connector-python
什么是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()本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2019-07-29 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。