Python操作MySQL

本文介绍如何使用Python操作MySQL数据库，主要包含以下几点：

• 连接mysql
• 创建数据库
• 创建表
• 往表中插入数据
• 查询数据

1、安装对应Python包

pip install mysql-connector-python

2、连接mysql

from mysql import connector
from mysql.connector import errorcode

try:
    client = connector.connect(user='xxxx', password='xxxxxx', host='xxxxxx', database='xxxx')
    cursor = client.cursor()
except connector.Error as err:
    client = None
    cursor = None
    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)

3、创建数据库

with open("../../../../Database/MySQL/create_databases.sql", "r") as f:
    sql = f.read()
    try:
        cursor.execute(sql)
        print("Create databases successfully")
    except connector.Error as err:
        print(err)
        exit(1)

其中create_databases.sql中内容见
https://github.com/xiligey/Notes/blob/master/Database/MySQL/create_databases.sql

4、创建表

with open("../../../../Database/MySQL/create_tables.sql", "r") as f:
    sql = f.read()
    try:
        cursor.execute(sql)
        print("Create tables successfully")
    except connector.Error as err:
        print(err)
        exit(1)

其中create_tables.sql内容如下见
https://github.com/xiligey/Notes/blob/master/Database/MySQL/create_tables.sql

5、插入数据

from datetime import date, datetime, timedelta

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)")
data_employee = ('Geert', 'Jack', tomorrow, 'M', date(1977, 6, 14))
cursor.execute(add_employee, data_employee)

add_salary = ("INSERT INTO salaries "
              "(emp_no, salary, from_date, to_date) "
              "VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")

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)
client.commit()

6、查询数据

query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")

hire_start = date(1999, 1, 1)
hire_end = date(2999, 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))

7、关闭连接

最后记得关闭客户端连接

if cursor:
    cursor.close()
if client:
    client.close()

8、源码

见https://github.com/xiligey/Notes/blob/master/Python/basic/io/database/mysql_operate.py