使用Python操作MySQL数据库
AI-摘要
切换
小郭 GPT
AI初始化中...
介绍自己
生成本文简介
推荐相关文章
前往主页
前往tianli博客
一、创建数据库连接
最基础写法
"""
pymysql创建数据库连接 最基础写法
"""
from pymysql import Connection
# 创建数据库连接
con = Connection(
host="localhost", # 主机名
port=3306, # 端口号
user="root", # 用户名
password="******", # 密码
database="studyPymysql", # 数据库名
)
print(type(con)) # <class 'pymysql.connections.Connection'>
print(con.get_host_info()) # socket localhost:3306
print(con.get_server_info()) # 5.7.26
# 关闭数据库连接
con.close()
改进版
"""
pymysql创建数据库连接 改进版
"""
from pymysql import Connection
con = None # 先定义为全局变量防止下方finally中无法访问
try:
# 创建数据库连接
con = Connection(
host="localhost", # 主机名
port=3306, # 端口号
user="root", # 用户名
password="******", # 密码
)
print(type(con)) # <class 'pymysql.connections.Connection'>
print(con.get_host_info()) # socket localhost:3306
print(con.get_server_info()) # 5.7.26
except Exception as e:
print("异常:", e)
finally:
if con:
# 关闭数据库连接
con.close()
二、执行DDL语句
"""
pymysql执行DDL语句
DDL(Data Definition Language)语句是一组用于定义和管理数据库结构和对象的SQL语句。
"""
from pymysql import Connection
con = None # 先定义为全局变量防止下方finally中无法访问
try:
# 创建数据库连接
con = Connection(
host="localhost", # 主机名
port=3306, # 端口号
user="root", # 用户名
password="******", # 密码
database="studyPymysql", # 数据库名
)
# 选择要操作的数据库
# 方法一:就是上方创建连接时指定的database参数
# 方法二:con.select_db("studyPymysql")
# 方法三:cursor.execute("USE studyPymysql")
# 创建游标对象
cursor = con.cursor()
# 定义一个建表的SQL语句
sql = """
CREATE TABLE t_student (
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""
# 执行SQL语句
cursor.execute(sql)
# 关闭游标
cursor.close() # 关闭游标后,游标对象不可再使用,实际操作中不需要关闭其实,关闭数据库时会自动关闭游标
# 修改列的数据类型或属性
sql1 = "ALTER TABLE t_student MODIFY COLUMN age VARCHAR(3);"
# 重命名列
sql2 = "ALTER TABLE t_student CHANGE COLUMN age age1 VARCHAR(3);"
# 添加新列
sql3 = "ALTER TABLE t_student ADD COLUMN email VARCHAR(50);"
# 删除列
sql4 = "ALTER TABLE t_student DROP COLUMN email;"
# 设置默认值
sql5 = "ALTER TABLE t_student ALTER COLUMN age1 SET DEFAULT 18;"
# 删除默认值
sql6 = "ALTER TABLE t_student ALTER COLUMN age1 DROP DEFAULT;"
# 修改表名
sql7 = "ALTER TABLE t_student RENAME TO t_student1;"
# 删除表
sql8 = "DROP TABLE t_student1;"
except Exception as e:
print("异常:", e)
finally:
if con:
# 关闭数据库连接
con.close()
三、执行DML语句
"""
pymysql执行DML语句
DML(Data Manipulation Language)数据操作语言,用于对数据库中的数据进行操作,包括增、删、改、查等操作。
"""
from pymysql import Connection
con = None # 先定义为全局变量防止下方finally中无法访问
try:
# 创建数据库连接
con = Connection(
host="localhost", # 主机名
port=3306, # 端口号
user="root", # 用户名
password="******", # 密码
database="studyPymysql", # 数据库名
autocommit=True, # 自动提交事务
)
# 创建游标对象
cursor = con.cursor()
# —————————— 插入数据 ——————————
cursor.execute("INSERT INTO t_student VALUES (1, '张三', 18);")
# 插入多条数据
cursor.executemany("INSERT INTO t_student VALUES (%s, %s, %s);", [(2, '李四', 19), (3, '王五', 20)])
# 插入一条数据并返回自增ID
cursor.execute("INSERT INTO t_student (name, age) VALUES ('赵六', 21);")
print("使用 cursor.lastrowid 获取自增ID:", cursor.lastrowid)
print("使用 con.insert_id() 获取自增ID:", con.insert_id())
# 插入一条数据并返回受影响的行数
cursor.execute("INSERT INTO t_student (name, age) VALUES ('孙七', 22);")
print(cursor.rowcount) # 1
# 注意:
# 在MySQL中,插入数据操作未生效的常见原因是未提交事务。
# 默认情况下,MySQL数据库的连接是以自动提交模式工作的,但在某些情况下,特别是在使用一些库如pymysql时,默认设置可能会关闭自动提交。这意味着你需要显式地提交事务,以确保数据操作生效。
# 可以在插入操作之后添加 con.commit() 来提交事务。
# —————————— 查询数据 ——————————
cursor.execute("SELECT * FROM t_student;")
# 获取所有数据
print(cursor.fetchall()) # ((1, '张三', 18), (2, '李四', 19), (3, '王五', 20), (4, '赵六', 21), (5, '孙七', 22))
for row in cursor.fetchall():
print(row)
# —————————— 更新数据 ——————————
cursor.execute("UPDATE t_student SET age=23 WHERE name='孙七';")
# —————————— 删除数据 ——————————
cursor.execute("DELETE FROM t_student WHERE name='孙七';")
except Exception as e:
print("异常:", e)
finally:
if con:
# 关闭数据库连接
con.close()
四、调用存储过程
在 MySQL 中,存储过程是一组预编译的 SQL 语句,可以通过名称和参数进行调用。使用存储过程可以简化复杂的数据库操作,并提高性能和可维护性。pymysql 允许你调用 MySQL 的存储过程,执行一系列的操作。
下面是一个创建和调用存储过程的示例。
1.创建存储过程
假设我们要创建一个简单的存储过程,它接受一个学生的名字并返回该学生的年龄。
DELIMITER //
CREATE PROCEDURE GetStudentAge(IN student_name VARCHAR(20), OUT student_age INT)
BEGIN
SELECT age INTO student_age
FROM t_student
WHERE name = student_name;
END //
DELIMITER ;
这个存储过程 GetStudentAge 接受一个输入参数 student_name,并将查询到的年龄值存储到输出参数 student_age 中。
2. 使用 pymysql 调用存储过程
下面是一个示例代码,展示如何使用 pymysql 调用上述存储过程并获取结果。
import pymysql
# 创建数据库连接
con = pymysql.connect(
host="localhost",
port=3306,
user="root",
password="******",
database="your_database_name"
)
try:
# 创建游标对象
cursor = con.cursor()
# 定义输入参数和输出参数
student_name = '张三'
student_age = 0
# 调用存储过程
cursor.callproc('GetStudentAge', [student_name, student_age])
# 获取存储过程的输出参数
cursor.execute("SELECT @_GetStudentAge_1")
result = cursor.fetchone()
student_age = result[0]
print(f"{student_name} 的年龄是: {student_age}")
except Exception as e:
print("异常:", e)
finally:
if con:
# 关闭数据库连接
con.close()
创建数据库连接:使用 pymysql.connect() 创建与 MySQL 数据库的连接。
创建游标对象:通过 con.cursor() 创建一个游标对象。
调用存储过程:使用 cursor.callproc('GetStudentAge', [student_name, student_age]) 调用存储过程。注意,这里 student_age 只是一个占位符,实际的输出参数值需要通过后续步骤获取。
获取输出参数:存储过程的输出参数可以通过查询会话变量(如 @_GetStudentAge_1)获取。这里使用 cursor.execute("SELECT @_GetStudentAge_1") 执行查询,并通过 cursor.fetchone() 获取结果。
处理异常和关闭连接:在 try-except-finally 块中处理可能的异常,并确保在操作结束后关闭数据库连接。
通过这种方式,可以使用 pymysql 调用 MySQL 的存储过程,并处理输入和输出参数。
- 感谢你赐予我前进的力量
赞赏者名单
因为你们的支持让我意识到写文章的价值🙏
本文是原创文章,采用 CC BY-NC-ND 4.0 协议,完整转载请注明来自 郭同学的笔记本
评论
匿名评论
隐私政策
你无需删除空行,直接评论以获取最佳展示效果