利用python连接MySQL数据库并执行相关sql操作

利用python连接MySQL数据库并执行相关sql操作

一、新建MySQL数据库

1.启动MySQL服务

打开phpstudy,开启MySQL服务。如果开启失败的话,可以打开任务管理器,把正在运行的mysqld服务的进程进行关闭,再次打开MySQL服务即可启动。

2.新建MySQL数据库

选择数据库,点击创建数据库,在里面填写你准备创建数据库的名称、用户名和密码,填好后选择确定即可成功创建好一个新的MySQL数据库。

二、提升数据库用户的权限

1.复制root对应用户和密码

在刚才的数据库模块查看一个名为root的数据库,查看对应的用户和密码,如果是第一次使用的话,用户和密码应该也都是root,但密码可以点击右侧的操作进行修改。然后可以先复制粘贴到记事本内,后面的登入需要用到。

2.下载phpMyAdmin

选择软件管理,找到phpMyAdmin,如果没有下载的可以先进行下载。点击管理,打开phpMyAdmin

3.打开并登入phpMyAdmin

这里可以看到我们需要用户名和密码才能登入phpMyAdmin,用户和密码就是刚才查看并复制的,进行输入登入。

4.修改用户权限

选择账户,找到刚才你创建的数据库的用户名,然后点击右侧的修改权限。这里我就拿python用户举例,全局权限在新建数据库后对应的是USAGE,我这里是已经将权限提升过后的,所以才显示的ALL PRIVILEGES,这点不用担心。

全局权限这里的选择全部勾选,然后点击执行即可完成权限的提升。

三、连接MySQL数据库

1.建立MySQL连接

打开Navicat for MySQL,选择文件-->新建连接-->MySQL。

这里输入自己对应数据库的连接名、用户名和密码,输入完毕后可以先点击测试连接试试,没有问题最后点击确定。这样就建立起了和数据库的联系。

双击刚连接到的MySQL数据库,从原本的灰色变为绿色,说明数据库连接成功。找到我们的python库,点击新建表。

2.添加字段并新建表

点击添加字段,可以输入一些你想创建的名,比如说下面的id,name,count,添加完成后点击保存,并输入你想创建的表名。

3.添加数据

双击打开我们刚新建好的表,点击左下侧的小加号,进行数据创建,然后对应输入你自己想要输入的值即可,完成创建后点击小勾,进行保存。

四、执行python代码连接数据库

这里我会进行三套python代码连接MySQL数据库的演示。

1. 第一套python

其中user,password,database需要填写你自己新建数据库对应的数据库用户名,数据库密码,数据库名称。

import mysql.connector  # 导入MySQL连接器模块
from mysql.connector import Error  # 导入MySQL连接错误模块
    # 连接到MySQL数据库
    connection = mysql.connector.connect(
        host='localhost',  # 数据库主机地址
        user='xxx',  # 数据库用户名
        password='xxx',  # 数据库密码
        database='xxx'  # 数据库名称
    if connection.is_connected():  # 如果连接成功
        db_Info = connection.get_server_info()  # 获取数据库服务器信息
        print("成功连接到 MySQL 数据库,服务器版本:", db_Info)  # 打印成功连接信息
        cursor = connection.cursor()  # 创建游标对象
        cursor.execute("select database();")  # 执行SQL查询以获取当前数据库
        record = cursor.fetchone()  # 获取查询结果
        print("你连接到的数据库是:", record[0])  # 打印连接的数据库名称
except Error as e:  # 捕获可能的连接错误
    print("连接出错:", e)  # 打印连接错误信息
finally:
    if connection.is_connected():  # 如果连接处于打开状态
        cursor.close()  # 关闭游标
        connection.close()  # 关闭连接
        print("MySQL连接已关闭")  # 打印连接已关闭的信息

执行python代码,运行结果如下:

可以很明显的看到输入的内容:

成功连接到 MySQL 数据库,服务器版本: 5.7.26
你连接到的数据库是: python
MySQL连接已关闭

2. 第二套python

import tkinter as tk  # 导入 tkinter 库
from tkinter import ttk, messagebox  # 导入 ttk 和 messagebox 模块
import mysql.connector  # 导入 MySQL 连接器模块
from mysql.connector import Error  # 导入错误处理模块
# 连接到数据库的函数
def connect_to_database(host, user, password, database):
        # 尝试建立数据库连接
        connection = mysql.connector.connect(
            host=host, user=user, password=password, database=database)
        return connection  # 如果成功连接,返回连接对象
    except Error as e:
        # 如果连接失败,显示错误消息框
        messagebox.showerror("连接错误", str(e))
        return None  # 连接失败时返回 None
# 执行 SQL 查询的函数
def run_query(connection, query):
    cursor = connection.cursor()
        cursor.execute(query)  # 执行 SQL 查询
        if "select" in query.lower():
            results = cursor.fetchall()  # 如果是 SELECT 查询,则获取所有结果
            return results
        else:
            connection.commit()  # 提交其他类型的查询(INSERT、UPDATE、DELETE)的更改
            return cursor.rowcount  # 返回受影响的行数
    except Error as e:
        messagebox.showerror("SQL错误", str(e))  # 显示 SQL 错误的消息框
    finally:
        cursor.close()  # 关闭游标
# 当点击提交按钮时触发的函数
def on_submit():
    # 使用提供的凭据尝试连接到数据库
    connection = connect_to_database(host_var.get(), user_var.get(), pass_var.get(), db_var.get())
    if connection:
        messagebox.showinfo("连接成功", "成功连接到数据库")  # 如果连接成功,显示成功消息
        connection.close()  # 关闭连接
root = tk.Tk()  # 创建主窗口
root.title("数据库连接")  # 设置窗口标题
style = ttk.Style(root)  # 定义窗口样式
style.theme_use("clam")  # 使用主题样式
# 设置窗口大小和位置
window_width = 400
window_height = 400
screen_width = root.winfo_screenwidth()
screen_height = root.winfo_screenheight()
x = (screen_width - window_width) // 2
y = (screen_height - window_height) // 2
root.geometry(f"{window_width}x{window_height}+{x}+{y}")
center_frame = ttk.Frame(root)  # 创建一个居中的框架
center_frame.place(relx=0.5, rely=0.5, anchor=tk.CENTER)  # 放置在窗口中心
# 创建输入字段和标签
host_var = tk.StringVar()
user_var = tk.StringVar()
pass_var = tk.StringVar()
db_var = tk.StringVar()
ttk.Label(center_frame, text="主机地址").grid(row=0, column=0, padx=10, pady=10)
ttk.Entry(center_frame, textvariable=host_var).grid(row=0, column=1, padx=10, pady=10)
ttk.Label(center_frame, text="用户名").grid(row=1, column=0, padx=10, pady=10)
ttk.Entry(center_frame, textvariable=user_var).grid(row=1, column=1, padx=10, pady=10)
ttk.Label(center_frame, text="密码").grid(row=2, column=0, padx=10, pady=10)
ttk.Entry(center_frame, textvariable=pass_var, show="*").grid(row=2, column=1, padx=10, pady=10)
ttk.Label(center_frame, text="数据库名称").grid(row=3, column=0, padx=10, pady=10)
ttk.Entry(center_frame, textvariable=db_var).grid(row=3, column=1, padx=10, pady=10)
submit_btn = ttk.Button(center_frame, text="连接", command=on_submit)  # 创建连接按钮
submit_btn.grid(row=4, column=0, columnspan=2, pady=10)
root.rowconfigure(0, weight=1)
root.columnconfigure(0, weight=1)
root.mainloop()  # 运行主事件循环

执行python代码,运行结果如下:

在这个代码中,使用了messagebox.showerror和messagebox.showinfo这两个函数来创建弹框。

输入我们的数据库主机地址,数据库用户名,数据库密码,数据库名称。如果输入都是正确的,即弹出弹框,说明成功连接到数据库。

反之如果输入错误的话,即会返回连接错误。

3. 第三套python代码

最后一个才是所要讲解的python代码连接数据库后执行相关的sql操作,分别有静态和动态执行sql操作的python代码。代码需要注意的是xx部分是sql语句的执行内容,这个是要根据你自己数据库里的数据信息来填写再执行代码的。

a. 静态实现sql语句操作的python代码:

#静态执行sql语句操作
import mysql.connector # 导入MySQL连接器模块
from mysql.connector import Error
# 连接数据库
def connect_to_database(host, user, password, database):
        # 尝试建立数据库连接
        connection = mysql.connector.connect(
            host=host, user=user, password=password, database=database)
        print("数据库连接成功!")  # 连接成功提示
        return connection  # 返回连接对象
    except Error as e:
        print("数据库连接失败:", e)  # 连接失败提示
        return None
# 执行 SQL 查询
def run_query(connection, query):
    cursor = connection.cursor()
        cursor.execute(query)  # 执行 SQL 查询
        if query.lower().startswith("select"):
            return cursor.fetchall()  # 如果是 SELECT 查询,返回所有结果
        else:
            connection.commit()  # 提交更改(INSERT、UPDATE、DELETE)
            return cursor.rowcount  # 返回受影响的行数
    except Error as e:
        print("SQL错误:", e)  # SQL 错误提示
        return None
    finally:
        cursor.close()  # 关闭游标
# 主函数
def main():
    host = input("请输入主机地址: ")  # 获取用户输入的主机地址
    user = input("请输入用户名: ")  # 获取用户输入的用户名
    password = input("请输入密码: ")  # 获取用户输入的密码
    database = input("请输入数据库名称: ")  # 获取用户输入的数据库名称
    connection = connect_to_database(host, user, password, database)  # 连接数据库
    if connection is None:
        return
    while True:
        # 显示操作选项
        print("\n选择操作:")
        print("1. 查询")
        print("2. 插入")
        print("3. 更新")
        print("4. 删除")
        print("5. 退出")
        choice = input("请输入您的选择 (1-5): ")  # 获取用户选择
        if choice == "1":
            query = "SELECT * FROM view;"  # 查询语句
        elif choice == "2":
            query = "INSERT INTO view (id, name, count) VALUES (xx, 'xxx', xx);"  # 插入语句
        elif choice == "3":
            query = "UPDATE view SET name = 'xxx', count = xx WHERE id = xx;"  # 更新语句
        elif choice == "4":
            query = "DELETE FROM view WHERE id = xx;"  # 删除语句
        elif choice == "5":
            break  # 退出循环
        else:
            print("无效的选择!")  # 无效选择提示
            continue
        result = run_query(connection, query)  # 执行用户选择的操作
        if isinstance(result, list):
            for row in result:
                print(row)  # 打印查询结果
        elif result is not None:
            print("操作成功,影响行数:", result)  # 打印受影响的行数
    if connection.is_connected():
        connection.close()  # 关闭数据库连接
if __name__ == "__main__":
    main()  # 执行主函数

执行python代码,输入对应的数据库相关信息,输入正确即可显示以下内容:

数据库连接成功!
选择操作:
1. 查询
2. 插入
3. 更新
4. 删除
5. 退出
请输入您的选择 (1-5):

这里我先执行1操作,看输出结果。

执行2操作,看输出结果。

可以看到现在我们再次执行1操作查询时,已经比之前多出了一行数据,即(21, '忆梦_怀思', 39)。这个基于我数据库信息任意填写插入的一行信息,也可以改成其他的数据内容进行sql插入。

回到Navicat for MySQL,刷新一下刚才执行操作的数据库,可以看到现在数据库的信息内容更新了,就是多出的刚插入的那行信息内容。

这里还有一点需要强调一下,每次数据库进行刷新后,python是都要重新执行,并再次连接到数据库的。

执行3操作,看输出结果。

再次查看数据库内容,发现原本的(8, '怀思忆梦', 42)变为了现在的(8, '怀思_忆梦', 57),数据进行了更新。

再次刷新一下MySQL,可以看到里面的内容也进行了相应的更新操作。

执行4操作,看输出结果。

可以很明确的看到执行删除操作前后数据的变化,执行操作把(5, '怀思', 28)这行数据进行了删除。

同样再次刷新一下MySQL,可以看到里面的内容也进行了相应的删除操作。

最后执行5操作,就是直接退出连接数据库的意思。

这样我们就实现了利用python代码对于数据库的查询、插入、更新、删除等操作。

b. 动态实现sql语句操作的python代码:

#动态实现sql语句操作
import mysql.connector  # 导入MySQL连接器模块
from mysql.connector import Error
# 函数:连接到数据库
def connect_to_database(host, user, password, database):
        # 尝试连接到数据库
        connection = mysql.connector.connect(
            host=host, user=user, password=password, database=database)  # 使用给定的参数建立连接
        print("数据库连接成功!")  # 打印连接成功消息
        return connection  # 返回连接对象
    except Error as e:  # 捕获可能的错误
        print("数据库连接失败:", e)  # 打印连接失败消息
        return None  # 如果连接失败,返回空值
# 函数:执行查询或修改
def run_query(connection, query):
    cursor = connection.cursor()  # 创建游标对象
        cursor.execute(query)  # 执行传入的SQL查询
        if query.lower().startswith("select"):  # 如果是SELECT查询
            return cursor.fetchall()  # 返回查询结果集
        else:  # 对于其他类型的查询(INSERT、UPDATE、DELETE等)
            connection.commit()  # 提交修改
            return cursor.rowcount  # 返回受影响的行数
    except Error as e:  # 捕获可能的错误
        print("SQL错误:", e)  # 打印SQL错误消息
        return None  # 返回空值
    finally:
        cursor.close()  # 最终关闭游标
# 主函数
def main():
    host = input("请输入主机地址: ")  # 获取主机地址
    user = input("请输入用户名: ")  # 获取用户名
    password = input("请输入密码: ")  # 获取密码
    database = input("请输入数据库名称: ")  # 获取数据库名称
    connection = connect_to_database(host, user, password, database)  # 连接到数据库
    if connection is None:  # 如果连接失败,结束程序
        return
    while True:
        print("\n选择操作:") # query = 右侧字符串的内容才是举例的sql执行语句
        print("1. 查询") # query = "SELECT * FROM view;"
        print("2. 插入") # query = "INSERT INTO view (id, name, count) VALUES (xx, 'xxx', xx);"
        print("3. 更新") # query = "UPDATE view SET name = 'xxx', count = xx WHERE id = xx;"
        print("4. 删除") # query = "DELETE FROM view WHERE id = xx;"
        print("5. 退出")
        choice = input("请输入您的选择 (1-5): ")  # 获取用户选择
        if choice in ["1", "2", "3", "4"]:  # 如果选择为1至4之间的数字
            query = input("请输入SQL语句: ")  # 获取用户输入的SQL语句
            result = run_query(connection, query)  # 执行用户输入的SQL语句
            if result is not None:  # 如果执行成功
                if isinstance(result, list):  # 如果返回的是查询结果集
                    for row in result:  # 遍历结果并打印
                        print(row)
                else:  # 对于INSERT、UPDATE、DELETE等操作
                    if result > 0:
                        print("操作成功,影响行数:", result)  # 打印受影响的行数
                    else:
                        print("操作失败,影响行数:", result)  # 打印受影响的行数
            else:
                print("请检查您的SQL语句并重试。")  # 如果SQL执行失败,提示用户检查语句
        elif choice == "5":  # 如果选择为5,退出程序
            break
        else:
            print("无效的选择,请输入1-5之间的数字。")  # 如果选择无效,提示用户重新输入