python访问MySQL的几种方式

 

客户端程序访问MySQL数据库，一般有3种方式：

1. MySQL Connectors

Oracle develops a number of connectors:

• Connector/C++ enables C++ applications to connect to MySQL.

• Connector/J provides driver support for connecting to MySQL from Java applications using the standard Java Database Connectivity (JDBC) API.

• Connector/NET enables developers to create .NET applications that connect to MySQL. Connector/NET implements a fully functional ADO.NET interface and provides support for use with ADO.NET aware tools. Applications that use Connector/NET can be written in any supported .NET language.

  MySQL for Visual Studio works with Connector/NET and Microsoft Visual Studio 2012, 2013, 2015, and 2017. MySQL for Visual Studio provides access to MySQL objects and data from Visual Studio. As a Visual Studio package, it integrates directly into Server Explorer providing the ability to create new connections and work with MySQL database objects.

• Connector/ODBC provides driver support for connecting to MySQL using the Open Database Connectivity (ODBC) API. Support is available for ODBC connectivity from Windows, Unix, and macOS platforms.

• Connector/Python provides driver support for connecting to MySQL from Python applications using an API that is compliant with the Python DB API version 2.0. No additional Python modules or MySQL client libraries are required.

 

2. 原生访问方式The MySQL C API，即通过libmysqlclient客户端库。

For direct access to using MySQL natively within a C application, the C API provides low-level access to the MySQL client/server protocol through the libmysqlclient client library. This is the primary method used to connect to an instance of the MySQL server, and is used both by MySQL command-line clients and many of the MySQL Connectors and third-party APIs detailed here.

libmysqlclient is included in MySQL distributions distributions.

 

3. Third-Party MySQL APIs

All the third-party language APIs are developed using one of two methods, using libmysqlclient or by implementing a native driver. The two solutions offer different benefits:

• Using libmysqlclient offers complete compatibility with MySQL because it uses the same libraries as the MySQL client applications. However, the feature set is limited to the implementation and interfaces exposed through libmysqlclient and the performance may be lower as data is copied between the native language, and the MySQL API components.

• Native drivers are an implementation of the MySQL network protocol entirely within the host language or environment. Native drivers are fast, as there is less copying of data between components, and they can offer advanced functionality not available through the standard MySQL API. Native drivers are also easier for end users to build and deploy because no copy of the MySQL client libraries is needed to build the native driver components.

 

常见不同语言环境访问MySQL的方式（仅列出部分）

Environment	API	Type
C	C API	libmysqlclient
C++	Connector/C++	libmysqlclient
C++	MySQL++	libmysqlclient
C++	MySQL wrapped	libmysqlclient
Java/JDBC	Connector/J	Native Driver
Lua	LuaSQL	libmysqlclient
ODBC	Connector/ODBC	libmysqlclient
Python	Connector/Python	Native Driver
Python	Connector/Python C Extension	libmysqlclient
Python	MySQLdb	libmysqlclient
Ruby	MySQL/Ruby	libmysqlclient

 

以Python为例

 

常用第三方库MySQLdb实现客户端访问

 

MySQLdb基于C API实现了接口的封装。这里可以看MySQLdb的描述：

 

A DB API v2.0 compatible interface to MySQL

-------------------------------------------

 

This package is a wrapper around _mysql, which mostly implements the MySQL C API.

 

See the C API specification and the MySQL documentation for more info on other items.

 

For information on the DB API, see PEP-249.

 

For information on how MySQLdb handles type conversion, see the MySQLdb.converters module.

 

 

connector连接器（官方提供的访问方法）

Connector/Python implements the MySQL client/server protocol two ways（实现MySQL协议的2种方式）:

• 纯Python实现 As pure Python. This implementation of the protocol does not require any other MySQL client libraries or other components.

• C扩展实现 As a C Extension that interfaces with the MySQL C client library. This implementation of the protocol is dependent on the client library, but can use the library provided by MySQL Server packages (see MySQL C API Implementations). The C Extension is available as of Connector/Python 2.1.1.

显然，纯Python实现的可移植性更好。

 

虽然未经测试，但基于C扩展实现效率会更高，从下面的描述可以看出这点。

Connector/Python offers two implementations: a pure Python interface and a C extension that uses the MySQL C client library. This can be configured at runtime using the use_pure connection argument. It defaults to False as of MySQL 8, meaning the C extension is used. If the C extension is not available on the system then use_pure defaults to True. Setting use_pure=False causes the connection to use the C Extension if your Connector/Python installation includes it, while use_pure=True to False means the Python implementation is used if available.The use_pure option and C extension were added in Connector/Python 2.1.1.

 

Connector/Python can use a pure Python interface to MySQL, or a C Extension that uses the MySQL C client library. The use_pure mysql.connector.connect() connection argument determines which. The default changed in Connector/Python 8 from True (use the pure Python implementation) to False. Setting use_pure changes the implementation used.

引入C扩展的版本：The use_pure argument is available as of Connector/Python 2.1.1. For more information about the C extension, see Chapter 8, The Connector/Python C Extension.

 

如何安装：

$ pip install mysql-connector-python

$ python

>>> import mysql.connector

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

>>> cnx.close()