摘要：insert_sql = "INSERT INTO users (username, email) VALUES (%s, %s)"

在Python中操作MySQL数据库可以通过多个库实现，以下是使用PyMySQL库的详细步骤和示例：

步骤1：安装PyMySQL

bash

pip install pymysql

步骤2：连接数据库

python

import pymysql

# 建立数据库连接

connection = pymysql.connect(

host='localhost', # 数据库服务器地址

user='root', # 用户名

password='password', # 密码

database='testdb', # 数据库名

charset='utf8mb4', # 字符编码

cursorclass=pymysql.cursors.DictCursor # 结果以字典形式返回

)

步骤3：执行SQL操作

创建表

python

with connection.cursor as cursor:

create_table_sql = """

CREATE TABLE IF NOT EXISTS users (

id INT AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(50) NOT NULL,

email VARCHAR(100) NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

)

"""

cursor.execute(create_table_sql)

print("表创建成功")

插入数据

python

with connection.cursor as cursor:

insert_sql = "INSERT INTO users (username, email) VALUES (%s, %s)"

cursor.execute(insert_sql, ('user1', 'user1@example.com'))

connection.commit # 提交事务

print("数据插入成功，ID:", cursor.lastrowid)

批量插入

python

data = [('user2', 'user2@example.com'), ('user3', 'user3@example.com')]

with connection.cursor as cursor:

cursor.executemany(insert_sql, data)

connection.commit

print("批量插入成功，影响行数:", cursor.rowcount)

查询数据

python

with connection.cursor as cursor:

select_sql = "SELECT * FROM users"

cursor.execute(select_sql)

results = cursor.fetchall

for row in results:

print(row) # 输出字典形式的数据行

更新数据

python

with connection.cursor as cursor:

update_sql = "UPDATE users SET email=%s WHERE username=%s"

cursor.execute(update_sql, ('new_email@example.com', 'user1'))

connection.commit

print("数据更新成功，影响行数:", cursor.rowcount)

删除数据

python

with connection.cursor as cursor:

delete_sql = "DELETE FROM users WHERE username=%s"

cursor.execute(delete_sql, ('user1',))

connection.commit

print("数据删除成功，影响行数:", cursor.rowcount)

步骤4：异常处理与资源释放

python

try:

# 执行数据库操作...

except pymysql.MySQLError as e:

print("数据库错误:", e)

connection.rollback # 出错时回滚

finally:

connection.close # 确保连接关闭

print("数据库连接已关闭")

完整示例

try:

connection = pymysql.connect(

host='localhost',

user='root',

password='password',

database='testdb',

charset='utf8mb4',

cursorclass=pymysql.cursors.DictCursor

)

with connection.cursor as cursor:

# 创建表

cursor.execute("""

CREATE TABLE IF NOT EXISTS users (

id INT AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(50) NOT NULL,

email VARCHAR(100) NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

)

""")

print("表已创建")

# 插入数据

cursor.execute("INSERT INTO users (username, email) VALUES (%s, %s)", ('alice', 'alice@example.com'))

connection.commit

print("插入数据成功，ID:", cursor.lastrowid)

# 查询数据

cursor.execute("SELECT * FROM users")

users = cursor.fetchall

print("当前用户:")

for user in users:

print(user)

# 更新数据

cursor.execute("UPDATE users SET email=%s WHERE username=%s", ('alice_new@example.com', 'alice'))

connection.commit

print("更新成功，影响行数:", cursor.rowcount)

# 删除数据

cursor.execute("DELETE FROM users WHERE username=%s", ('alice',))

connection.commit

print("删除成功，影响行数:", cursor.rowcount)

except pymysql.MySQLError as e:

print("操作出错:", e)

connection.rollback

finally:

connection.close

print("连接已关闭")

关键注意事项

通过以上步骤，你可以安全高效地在Python中操作MySQL数据库。

来源：老客数据一点号