MySQL Python

MySQL 可应用于多种语言，Python可以非常方便的实现MySQL数据库的各种操作，Python对数据库的支持有规范指导：
https://peps.python.org/pep-0249/ ，以下介绍的PyMySQL与MySQL官方连接器遵循该规范指导

PyMySQL

pymysql模块是一个第三方提供的mysql客户端模块，实现了mysql的大部分的接口，只有少量的低级接口并未实现。在各种Python的WEB、数据分析模块中被广泛使用

安装方法

python3 -m pip install PyMySQL

连接示例

import pymysql

connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             database='db',
                             cursorclass=pymysql.cursors.DictCursor)

查询示例

通过连接对象connection操作数据库

# 插入数据
with connection.cursor() as cursor:
    # Create a new record
    sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
    cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    connection.commit()

# 检索数据
with connection.cursor() as cursor:
    # Read a single record
    sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
    cursor.execute(sql, ('webmaster@python.org',))
    result = cursor.fetchone()
    print(result)

 

---

MySQL官方连接器

由MySQL官方提供的连接器，官方手册资料比较丰富

安装方法

pip install mysql-connector-python

连接示例

import mysql.connector

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

查询示例

通过cnx对象进行数据库操作

import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()
##################################################
# 插入数据
##################################################
# 插入语句
add_employee = ("INSERT INTO employees (first_name, last_name, hire_date, gender, birth_date) VALUES (%s, %s, %s, %s, %s)")
data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))

# 执行插入
cursor.execute(add_employee, data_employee) 
# 返回插入的id
emp_no = cursor.lastrowid

# 提交
cnx.commit()

################################################
# 检索数据
################################################
# 检索语句
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 row in cursor: 
    print("{}, {} was hired on {:%d %b %Y}".format(*row))

# 关闭资源
cursor.close()
cnx.close()