用python和mysql.connector操作mysql
使用python和mysql官方提供驱动对数据库进行操作。
环境
windows7 64位
python 3.4.3
mysql5.0
下载安装驱动
驱动下载地址： http://dev.mysql.com/downloads/connector/python/
请根据自己的操作系统和python版本下载相应驱动。
我在win7下安装驱动的时候提示我找不到python环境，要求python必须是通过msi直接安装才可以。
没办法，卸载重新安装了才可以。
连接数据库
介绍这里mysql.connector支持的数据库连接方式。
使用connect()构造器创建连接：
Python
import mysql.connector
# Connect with the MySQL Server
cnx = mysql.connector.connect(user='root',
password='zhyea.com',
host='127.0.0.1',
database='test')
cnx.close()
import mysql.connector
# Connect with the MySQL Server
cnx = mysql.connector.connect(user='root',
password='zhyea.com',
host='127.0.0.1',
database='test')
cnx.close()
也可以使用connection.MySQLConnection()类：
Python
from mysql.connector import (connection)
# Connect with the MySQL Server
cnx = connection.MySQLConnection(user='root',
password='zhyea.com',
host='127.0.0.1',
database='test')
cnx.close()
from mysql.connectorimport (connection)
# Connect with the MySQL Server
cnx = connection.MySQLConnection(user='root',
password='zhyea.com',
host='127.0.0.1',
database='test')
cnx.close()
上面提到的两种方案都是可行的，而且性能一样。但是connect()用得更多一些，我个人也比较喜欢这种方式。
要处理连接异常的话，可以使用try/catch声明来捕捉异常：
Python
import mysql.connector
from mysql.connector import errorcode
# Connect with the MySQL Server
try:
cnx = mysql.connector.connect(user='root',
password='zhyea.com',
host='127.0.0.1',
database='test11')
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name and password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database dosen't exist")
else:
print(err)
else:
cnx.close()
import mysql.connector
from mysql.connectorimport errorcode
# Connect with the MySQL Server
try:
cnx = mysql.connector.connect(user='root',
password='zhyea.com',
host='127.0.0.1',
database='test11')
except mysql.connector.Erroras err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name and password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database dosen't exist")
else:
print(err)
else:
cnx.close()
如果连接参数很多的话，可以使用字典和**操作符：
Python
import mysql.connector
from mysql.connector import errorcode
# Connect with the MySQL Server
config = {
'user' : 'root',
'password' : 'zhyea.com',
'host' : '127.0.0.1',
'database' : 'test1'
}
try:
cnx = mysql.connector.connect(**config)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name and password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database dosen't exist")
else:
print(err)
else:
cnx.close()
import mysql.connector
from mysql.connectorimport errorcode
# Connect with the MySQL Server
config = {
'user' : 'root',
'password' : 'zhyea.com',
'host' : '127.0.0.1',
'database' : 'test1'
}
try:
cnx = mysql.connector.connect(**config)
except mysql.connector.Erroras err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name and password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database dosen't exist")
else:
print(err)
else:
cnx.close()
创建表结构
所有的DDL声明都是通过cursor完成的。
在下面的示例中描述了如何使用mysql.connector和python完成表的创建。
Python
from __future__ import print_function
import mysql.connector
from mysql.connector import errorcode
# 数据库名称
DB_NAME = 'employees'
# 建表语句字典
TABLES = {}
TABLES['employees'] = (
"CREATE TABLE <pre class="inline:true decode:1 " >employees
from __future__ import print_function
import mysql.connector
from mysql.connectorimport errorcode
# 数据库名称
DB_NAME = 'employees'
# 建表语句字典
TABLES = {}
TABLES['employees'] = (
"CREATE TABLE <pre class="inline:true decode:1 " >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”)
# 创建连接对象
try:
cnx = mysql.connector.connect(user=’root’, password=’zhyea.com’)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print(“Something is wrong with your user name and password!”)
exit(1)
else:
print(err)
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 to create database: {}”.format(err))
exit(1)
# 设置连接对象中的数据库，如果发生异常，根据异常号码进行判断，如果数据库不存在则创建数据库
try:
cnx.database = DB_NAME
except mysql.connector.Error as err:
if err.errno == errorcode.ER_BAD_DB_ERROR:
create_database(cursor)
cnx.database = DB_NAME
else:
print(err)
exit(1)
# 循环执行建表语句
for name, ddl in TABLES.items():
try:
print(“Creating table {}:”.format(name), end=”)
cursor.execute(ddl)
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()
插入数据
mysql中数据的插入和更新同样是由cursor完成的。如果使用的是事务存储引擎（比如Innodb），在插入、更新和删除声明后需要进行commit。
下面的示例演示了如何插入新数据.
Python
from __future__ import print_function
from datetime import date, datetime, timedelta
import mysql.connector
from mysql.connector import errorcode
from distutils.command.config import config
# 连接信息
config = {
'user' : 'root',
'password' : 'zhyea.com',
'host' : '127.0.0.1',
'database' : 'employees'
}
# 创建连接对象
try:
cnx = mysql.connector.connect(**config)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name and password!")
exit(1)
else:
print(err)
else:
print("Connect OK!")
# 获取cursor
cursor = cnx.cursor()
# 获取明天的时间
tomorrow = datetime.now().date() + timedelta(days=1)
# 插入员工信息语句
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)
# 获取刚插入的记录的ID
emp_no = cursor.lastrowid
# 薪资数据
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()
from __future__ import print_function
from datetime import date, datetime, timedelta
import mysql.connector
from mysql.connectorimport errorcode
from distutils.command.config import config
# 连接信息
config = {
'user' : 'root',
'password' : 'zhyea.com',
'host' : '127.0.0.1',
'database' : 'employees'
}
# 创建连接对象
try:
cnx = mysql.connector.connect(**config)
except mysql.connector.Erroras err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name and password!")
exit(1)
else:
print(err)
else:
print("Connect OK!")
# 获取cursor
cursor = cnx.cursor()
# 获取明天的时间
tomorrow = datetime.now().date() + timedelta(days=1)
# 插入员工信息语句
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)
# 获取刚插入的记录的ID
emp_no = cursor.lastrowid
# 薪资数据
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()
执行查询
以下演示了如何使用cursor()方法执行查询操作。
查询后的结果经过格式处理后打印在控制台上。
Python
'''
Created on 2015年8月10日
@author: robin
'''
import datetime
import mysql.connector
from mysql.connector import errorcode
# 连接信息
config = {
'user' : 'root',
'password' : 'zhyea.com',
'host' : '127.0.0.1',
'database' : 'employees'
}
# 创建连接对象
try:
cnx = mysql.connector.connect(**config)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name and password!")
exit(1)
else:
print(err)
else:
print("Connect OK!")
# 获取cursor
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(2016, 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(first_name, last_name, hire_date))
# 释放资源
cursor.close()
cnx.close()
'''
Created on 2015年8月10日
@author: robin
'''
import datetime
import mysql.connector
from mysql.connectorimport errorcode
# 连接信息
config = {
'user' : 'root',
'password' : 'zhyea.com',
'host' : '127.0.0.1',
'database' : 'employees'
}
# 创建连接对象
try:
cnx = mysql.connector.connect(**config)
except mysql.connector.Erroras err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name and password!")
exit(1)
else:
print(err)
else:
print("Connect OK!")
# 获取cursor
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(2016, 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(first_name, last_name, hire_date))
# 释放资源
cursor.close()
cnx.close()
最后展示下输出结果：
在django中使用的配置
Python
DATABASES = {
'default': {
'ENGINE': 'mysql.connector.django',
'NAME': 'mytest',
'USER': 'root',
'PASSWORD': 'zhyea.com',
'HOST': '127.0.0.1',
'PORT': '3306',
}
}
DATABASES = {
'default': {
'ENGINE': 'mysql.connector.django',
'NAME': 'mytest',
'USER': 'root',
'PASSWORD': 'zhyea.com',
'HOST': '127.0.0.1',
'PORT': '3306',
}
}
将这个方案列在这里是给大家一些提示。由于mysql对django的支持并不是实时的，因此1.8版本的diango在使用的时候还是会报错。这里有一个mysql对应django1.8的bug文档：https://dev.mysql.com/doc/connector-python/en/connector-python-installation-source-unix.html
因此使用django时，建议使用“ django . db . backends . mysql ”。
参考文档： http://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html
浙公网安备 33010602011771号