Python 连接 MySQL

• Python MySQL
• 简介
• 安装
• 使用mysql-connector
• 使用pymysql

Python MySQL

简介

python可以使用mysql-connector库连接到mysql数据库，mysql-connector库是mysql官方推荐的python驱动，也可以使用pymysql库连接到mysql数据库，pymysql库是第三方驱动，功能更加丰富。

安装

pip install mysql-connector
pip install pymysql

使用mysql-connector

import mysql.connector

# 连接数据库
cnx = mysql.connector.connect(
    user="root", password="password", host="localhost", database="test"
)


# 创建游标
cursor = cnx.cursor()

# 创建表
sql = "CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)"
cursor.execute(sql)

# 插入数据
sql = "INSERT INTO students (name, age) VALUES (%s, %s)"
values = [("Tom", 20), ("Jerry", 25), ("Mike", 30)]
cursor.executemany(sql, values)


# 查询数据
sql = "SELECT * FROM students"
cursor.execute(sql)
for row in cursor.fetchall():
    print(row)


# 更新数据
sql = "UPDATE students SET age = %s WHERE name = %s"
values = (28, "Tom")
cursor.execute(sql, values)


# 删除数据
sql = "DELETE FROM students WHERE age < %s"
values = (25,)
cursor.execute(sql, values)


# 关闭游标和数据库连接
cursor.close()
cnx.close()

使用pymysql

import pymysql

# 连接数据库
cnx = pymysql.connect(
    user="root", password="password", host="localhost", database="test"
)

# 创建游标
cursor = cnx.cursor()

# 创建表
sql = "CREATE TABLE teachers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)"
cursor.execute(sql)

# 插入数据
sql = "INSERT INTO teachers (name, age) VALUES (%s, %s)"
values = ("John", 25)
cursor.execute(sql, values)


# 查询数据
sql = "SELECT * FROM teachers"
cursor.execute(sql)
result = cursor.fetchall()
print(result)


# 更新数据
sql = "UPDATE teachers SET age = %s WHERE id = %s"
values = (30, 1)
cursor.execute(sql, values)


# 删除数据
sql = "DELETE FROM teachers WHERE id = %s"
values = (2,)
cursor.execute(sql, values)

# 关闭游标和数据库连接
cursor.close()
cnx.close()