Python Data Access 简明教程

Python MySQL - Database Connection

要连接到 MySQL，（一种方法是）在你的系统中打开 MySQL 命令提示符，如下所示：

To connect with MySQL, (one way is to) open the MySQL command prompt in your system as shown below −

它会在这里询问密码；你需要输入在安装时为默认用户（root）设置的密码。

It asks for password here; you need to type the password you have set to the default user (root) at the time of installation.

然后会建立与 MySQL 的连接，并显示以下消息：

Then a connection is established with MySQL displaying the following message −

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

在 MySQL > 提示符处，你可以随时使用 exit 命令与 MySQL 数据库断开连接。

You can disconnect from the MySQL database any time using the exit command at mysql> prompt.

mysql> exit
Bye

Establishing connection with MySQL using python

在使用 Python 连接到 MySQL 数据库之前，假设：

Before establishing connection to MySQL database using python, assume −

That we have created a database with name mydb.
We have created a table EMPLOYEE with columns FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
The credentials we are using to connect with MySQL are username:* root*, password: password.

你可以使用 connect() 构造函数来建立连接。这接受用户名、密码、主机和你需要连接到的数据库名称（可选），并返回 MySQLConnection 类的对象。

You can establish a connection using the connect() constructor. This accepts username, password, host and, name of the database you need to connect with (optional) and, returns an object of the MySQLConnection class.

Example

以下是连接到 MySQL 数据库“mydb”的示例。

Following is the example of connecting with MySQL database "mydb".

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='mydb')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Executing an MYSQL function using the execute() method
cursor.execute("SELECT DATABASE()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print("Connection established to: ",data)

#Closing the connection
conn.close()

Output

在执行此脚本后，将生成以下输出：

On executing, this script produces the following output −

D:\Python_MySQL>python EstablishCon.py
Connection established to: ('mydb',)

你还可以通过将认证信息（用户名、密码、主机名和数据库名称）传递给 connection.MySQLConnection() 来建立连接，如下所示：

You can also establish connection to MySQL by passing credentials (user name, password, hostname, and database name) to connection.MySQLConnection() as shown below −

from mysql.connector import (connection)

#establishing the connection
conn = connection.MySQLConnection(user='root', password='password', host='127.0.0.1', database='mydb')

#Closing the connection
conn.close()