python---(4) win10 环境下访问MYSQL 数据库

最新推荐文章于 2024-07-31 19:26:35 发布

zhangbijun1230

最新推荐文章于 2024-07-31 19:26:35 发布

阅读量958

点赞数

分类专栏： python

本文链接：https://blog.csdn.net/zhangbijun1230/article/details/79625574

版权

python 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

1. 安装MySQL

从MySQL官方网站下载最新的Community Server 5.6.x版本。MySQL是跨平台的，选择对应的平台下载安装文件，安装即可。

安装时，MySQL会提示输入root用户的口令，请务必记清楚。如果怕记不住，就把口令设置为。

在Windows上，安装时请选择UTF-8编码，以便正确地处理中文。

在Mac或Linux上，需要编辑MySQL的配置文件，把数据库默认的编码全部改为UTF-8。MySQL的配置文件默认存放在/etc/my.cnf或者/etc/mysql/my.cnf：

[client]
default-character-set = utf8

[mysqld]
default-storage-engine = INNODB
character-set-server = utf8
collation-server = utf8_general_ci

重启MySQL后，可以通过MySQL的客户端命令行检查编码：

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor...
...

mysql> show variables like '%char%';
+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | utf8                                                   |
| character_set_connection | utf8                                                   |
| character_set_database   | utf8                                                   |
| character_set_filesystem | binary                                                 |
| character_set_results    | utf8                                                   |
| character_set_server     | utf8                                                   |
| character_set_system     | utf8                                                   |
| character_sets_dir       | /usr/local/mysql-5.1.65-osx10.6-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+
8 rows in set (0.00 sec)

2 安装Python MySQL 连接器驱动

由于MySQL服务器以独立的进程运行，并通过网络对外服务，所以，需要支持Python的MySQL驱动来连接到MySQL服务器。MySQL官方提供了mysql-connector-python驱动，但是安装的时候需要给pip命令加上参数--allow-external：

$ pip install mysql-connector-python --allow-external mysql-connector-python

如果上面的命令安装失败，可以试试另一个驱动：$ pip install mysql-connector

3 MySQL连接器介绍

Python使Python程序能够使用符合Python数据库API规范v2.0（PEP 249）的API访问MySQL数据库。它是用纯Python编写的，除了Python

标准库之外没有任何依赖关系.

MySQL Connector / Python包含对以下内容的支持：

几乎所有由MySQL服务器提供的功能直到MySQL服务器版本5.7。
Connector / Python 8.0还支持X DevAPI。有关使用X DevAPI的MySQL Connector / Python的概念和用法的文档，请参阅 X DevAPI用户指南。
在Python和MySQL数据类型之间来回转换参数值，例如Python datetime 和MySQL DATETIME。您可以打开自动转换功能以方便使用
标准SQL语法的所有MySQL扩展。
协议压缩，它可以压缩客户端和服务器之间的数据流。
连接使用TCP / IP套接字和在Unix上使用Unix套接字。
使用SSL保护TCP / IP连接。
自包含的驱动程序。连接器/ Python不需要MySQL客户端库或标准库之外的任何Python模块。

连接器/ Python版本参考

连接器/ Python版本	MySQL服务器版本	Python版本	连接器状态
8.0（延续2.2）	8.0，5.7，5.6，5.5	3.6，3.5，3.4，2.7	发布候选
2.2（继续为8.0）	5.7，5.6，5.5	3.5，3.4，2.7	开发人员里程碑，没有发布
2.1	5.7，5.6，5.5	3.5，3.4，2.7，2.6	推荐版本
2.0	5.7，5.6，5.5	3.5，3.4，2.7，2.6	GA，2016-10-26最终发布
1.2	5.7,5.6,5.5（5.1,5.0,4.1）	3.4,3.3,3.2,3.1,2.7,2.6	GA，于2014-08-22最终发布

4 连接器/ Python使用

4.1连接器/ Python编码示例

该connect()构造函数创建到MySQL服务器的连接并返回一个 MySQLConnection对象。

以下示例显示如何连接到MySQL服务器：

import mysql.connector

cnx = mysql.connector.connect(user='scott', password='password',
                              host='127.0.0.1',
                              database='employees')
cnx.close()

也可以使用connection.MySQLConnection（）类创建连接对象：

from mysql.connector import (connection)

cnx = connection.MySQLConnection(user='scott', password='password',
                                 host='127.0.0.1',
                                 database='employees')
cnx.close()

这两种使用connect()构造函数或直接使用类的方法都是有效且功能相同的，但使用 connector()是首选，本手册中的大多数示例都使用这种方法。

为了处理连接错误，使用try 的语句和追赶使用的所有错误 errors.Error 例外：

import mysql.connector
from mysql.connector import errorcode

try:
  cnx = mysql.connector.connect(user='scott',
                                database='testt')
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
  cnx.close()

如果您有很多连接参数，最好将它们保存在字典中并使用**运算符：

import mysql.connector  config = { 'user': 'scott', 'password': 'password', 'host': '127.0.0.1', 'database': 'employees', 'raise_on_warnings': True, }  cnx = mysql.connector.connect(**config)  cnx.close()

`使用连接器/ Python C扩展`

从Connector / Python 2.1.1开始，use_pure连接参数确定是使用纯Python接口连接到MySQL还是使用MySQL C客户端库的C扩展。默认值是True（使用纯Python实现）。设置use_pure为False 使连接使用C扩展如果您的连接器/ Python安装包含它。以下示例与前面显示的其他示例类似，但包含了 use_pure=False。

通过在connect() 呼叫中命名参数进行连接：

import mysql.connector

cnx = mysql.connector.connect(user='scott', password='password',
                              host='127.0.0.1',
                              database='employees',
                              use_pure=False)
cnx.close()

使用参数字典进行连接：

import mysql.connector

config = {
  'user': 'scott',
  'password': 'password',
  'host': '127.0.0.1',
  'database': 'employees',
  'raise_on_warnings': True,
  'use_pure': False,
}

cnx = mysql.connector.connect(**config)

cnx.close()

也可以通过导入_mysql_connector模块而不是 mysql.connector模块直接使用C扩展。

4.2 使用连接器/ Python创建表格

所有的DDL（数据定义语言）语句都是使用称为游标的句柄结构来执行的。以下示例显示如何创建Employee Sample Database的表。你需要他们为其他例子。

在MySQL服务器中，表格是非常长寿命的对象，并且通常由多个用不同语言编写的应用程序访问。您通常可以使用已经设置好的表格，而不是在您自己的应用程序中创建它们。避免重复设置和删除表格，因为这是一项昂贵的操作。临时表是一个例外，它可以在应用程序中快速创建和删除。

from __future__ import print_function

import mysql.connector
from mysql.connector import errorcode

DB_NAME = 'employees'

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

上面的代码显示了我们如何将这些CREATE语句存储在称为的Python字典中 TABLES。我们还在一个名为的全局变量中定义数据库DB_NAME，这使您可以轻松使用不同的模式。

cnx = mysql.connector.connect(user='scott')
cursor = cnx.cursor()

一台MySQL服务器可以管理多个数据库。通常，您指定连接到MySQL服务器时切换到的数据库。此示例在连接时不会连接到数据库，以便它可以确保数据库存在，如果不存在，则创建该数据库：

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

我们首先尝试使用database连接对象的属性更改为特定的数据库 cnx。如果出现错误，我们检查错误编号以检查数据库是否不存在。如果是这样，我们称这个 create_database函数为我们创建它。

出现任何其他错误时，应用程序退出并显示错误消息。

在成功创建或更改为目标数据库后，我们通过遍历TABLES字典的项目来创建表格：

for name, ddl in TABLES.iteritems():
    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()

为了在表格已经存在时处理错误，我们通知用户它已经在那里。其他错误是打印的，但我们继续创建表格。（这个例子展示了如何处理 “ 已经存在的表 ”的情况，在实际的应用程序中，我们通常通过使用语句的IF NOT EXISTS子句来避免错误情况CREATE TABLE。）

输出将是这样的：

Creating table employees: already exists.
Creating table salaries: already exists.
Creating table titles: OK
Creating table departments: already exists.
Creating table dept_manager: already exists.
Creating table dept_emp: already exists.

要填充员工表，请使用员工示例数据库的转储文件。请注意，您只需要在名为like的存档中找到的数据转储文件 employees_db-dump-files-1.0.5.tar.bz2。下载转储文件后，执行以下命令，并在必要时向mysql命令添加连接选项：

shell> tar xzf employees_db-dump-files-1.0.5.tar.bz2 shell> cd employees_db shell> mysql employees < load_employees.dump shell> mysql employees < load_titles.dump shell> mysql employees < load_departments.dump shell> mysql employees < load_salaries.dump shell> mysql employees < load_dept_emp.dump shell> mysql employees < load_dept_manager.dump

4.3使用连接器/ Python插入数据

插入或更新数据也使用称为游标的处理程序结构完成。当您使用事务性存储引擎，如InnoDB（在MySQL 5.5和更高的默认设置），则必须提交的序列后的数据 INSERT， DELETE以及 UPDATE报表。

这个例子展示了如何插入新的数据。第二个 INSERT取决于第一个新创建的主键的值。该示例还演示了如何使用扩展格式。任务是添加一个新员工，明天开始工作，薪水设置为50000。

注意

以下示例使用示例第5.2节“使用连接器/ Python创建表”中创建的表。表格AUTO_INCREMENT主键的列选项对于employees确保可靠，易于搜索的数据很重要。

from __future__ import print_function
from datetime import date, datetime, timedelta
import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')
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))

# Insert new employee
cursor.execute(add_employee, data_employee)
emp_no = cursor.lastrowid

# Insert salary information
data_salary = {
  'emp_no': emp_no,
  'salary': 50000,
  'from_date': tomorrow,
  'to_date': date(9999, 1, 1),
}
cursor.execute(add_salary, data_salary)

# Make sure data is committed to the database
cnx.commit()

cursor.close()
cnx.close()

我们首先打开一个到MySQL服务器的连接并将连接对象存储在变量中cnx。然后，我们使用连接的方法创建一个新的游标，默认为MySQLCursor对象 cursor()。

我们可以通过调用数据库函数来计算明天，但为了清楚起见，我们使用datetime模块在Python中执行。

这两个INSERT语句都存储在变量called add_employee和中 add_salary。请注意，第二条 INSERT语句使用扩展的Python格式代码。

新员工的信息存储在元组中 data_employee。执行插入新员工的查询，并使用游标对象的属性检索emp_no列（ AUTO_INCREMENT列）的新插入值lastrowid。

接下来，我们为新员工插入新薪水，在emp_no保存数据的字典中使用变量。execute()如果发生错误，该字典将传递给游标对象的方法。

由于默认情况下Connector / Python会关闭自动提交，并且默认情况下 MySQL 5.5和更高版本使用事务InnoDB表，所以需要使用连接的commit()方法提交更改。您也可以使用该方法回滚rollback()。

4.4使用连接器/ Python查询数据

以下示例显示如何使用使用连接方法创建的游标来查询数据 cursor()。返回的数据格式化并打印在控制台上。

任务是选择在1999年雇用的所有雇员，并将他们的姓名和雇佣日期输出到控制台。

import datetime
import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')
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服务器的连接并将连接对象存储在变量中cnx。然后，我们使用连接的方法创建一个新的游标，默认为MySQLCursor对象 cursor()。

在前面的例子中，我们将SELECT 语句存储在变量中query。请注意，我们正在使用未%s加引号的标记，其日期应该是。连接器/ Python的转换hire_start和 hire_end从Python的类型，MySQL的了解，并添加所需的引号的数据类型。在这种情况下，它取代了第一%s与 '1999-01-01'和第二位 '1999-12-31'。

然后我们query使用该execute() 方法执行存储在变量中的操作。用于替换%s查询中的标记的数据作为元组传递：(hire_start, hire_end)。

执行查询后，MySQL服务器准备好发送数据。结果集可以是零行，一行或一亿行。根据预期的音量，您可以使用不同的技术来处理该结果集。在这个例子中，我们使用该 cursor对象作为迭代器。行中的第一列存储在变量中 first_name，第二列 last_name和第三列中hire_date。

我们打印结果，使用Python的内置format()函数格式化输出。请注意， hire_date由Connector / Python自动转换为Python datetime.date对象。这意味着我们可以用更易读的格式轻松格式化日期。

输出应该是这样的：

.. Wilharm, LiMin was hired on 16 Dec 1999 Wielonsky, Lalit was hired on 16 Dec 1999 Kamble, Dannz was hired on 18 Dec 1999 DuBourdieux, Zhongwei was hired on 19 Dec 1999 Fujisawa, Rosita was hired on 20 Dec 1999