Python将图片存入MySQL数据库遇PermissionError权限错误求助

问题背景 #

编写了一段Python代码，意图通过tkinter文件对话框选取本地图片并保存至MySQL数据库，但运行时触发权限错误。

原代码 #

from tkinter import filedialog
from tkinter import *
from tkinter import messagebox
import pymysql


def connectDB():
    global mycursor, con
    try:
        con=pymysql.connect(host='localhost', user='root', password='password', db='dbname')
        mycursor=con.cursor()
        messagebox.showinfo('Success!', 'Connected')
    except:
        messagebox.showerror('Error!', 'Unable To Connect  To server')


def savedata():
    fn = filedialog.askopenfile(title='Select File', filetypes=(('Image File', '*.jpg'), ('All Files', '*.*')))
    with open(fn, "rb") as f:
        data = f.read()
    query = 'INSERT INTO files(id, file_data, date) VALUES(NULL, %s, NOW())'
    mycursor.execute(query, (data,))
    con.commit()
    messagebox.showinfo('success', 'succesfuly saved')

win =Tk()
Button(win, text='save data to DB', command=savedata).pack()
win.mainloop()

报错信息 #

C:\Users\Lael\AppData\Local\Programs\Python\Python312\python.exe C:\Users\Lael\PycharmProjects\pythonProject4\prac.py 
Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\Lael\AppData\Local\Programs\Python\Python312\Lib\tkinter\__init__.py", line 1948, in __call__
    return self.func(*args)
           ^^^^^^^^^^^^^^^^
  File "C:\Users\Lael\PycharmProjects\pythonProject4\prac.py", line 22, in savedata
    fn = filedialog.askopenfile(title='Select File', filetypes=(('Image File', '*.jpg'), ('All Files', '*.*')))
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Lael\AppData\Local\Programs\Python\Python312\Lib\tkinter\filedialog.py", line 410, in askopenfile
    return open(filename, mode)
           ^^^^^^^^^^^^^^^^^^^^
PermissionError: [Errno 13] Permission denied: 'C:/Users/Lael/Downloads/lele.jpg'

问题分析与解决方案 #

1. 权限错误核心原因 #

filedialog.askopenfile()会直接以默认模式打开文件并返回文件对象，后续又用open(fn, "rb")重复打开该文件，导致权限冲突。同时原代码未调用connectDB()，数据库连接未初始化，即使解决权限问题也会报错。

2. 修正后的代码 #

from tkinter import filedialog
from tkinter import *
from tkinter import messagebox
import pymysql


def connectDB():
    global mycursor, con
    try:
        con = pymysql.connect(host='localhost', user='root', password='password', db='dbname')
        mycursor = con.cursor()
        messagebox.showinfo('Success!', 'Connected to Database')
    except Exception as e:
        messagebox.showerror('Error!', f'Unable To Connect To Server: {str(e)}')


def savedata():
    # 检查数据库连接状态
    if 'con' not in globals() or not con.open:
        messagebox.showwarning('Warning', 'Please connect to database first')
        return
    
    # 获取文件路径而非直接打开文件
    file_path = filedialog.askopenfilename(title='Select Image File', 
                                          filetypes=(('JPG Images', '*.jpg'), ('All Files', '*.*')))
    if not file_path:  # 用户取消选择时直接返回
        return
    
    try:
        with open(file_path, "rb") as f:
            data = f.read()
        
        query = 'INSERT INTO files(id, file_data, date) VALUES(NULL, %s, NOW())'
        mycursor.execute(query, (data,))
        con.commit()
        messagebox.showinfo('Success', 'Image saved to database successfully')
    except PermissionError:
        messagebox.showerror('Permission Error', 'No permission to access the selected file')
    except Exception as e:
        messagebox.showerror('Error', f'Failed to save image: {str(e)}')
        con.rollback()  # 出错时回滚操作


win = Tk()
win.title("Image to MySQL")

# 添加数据库连接按钮
Button(win, text='Connect to Database', command=connectDB).pack(pady=5)
Button(win, text='Save Image to DB', command=savedata).pack(pady=5)

win.mainloop()

3. 额外配置要求 #

确保MySQL表files结构正确，file_data字段需设为LONGBLOB类型以存储大图片：

CREATE TABLE files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_data LONGBLOB NOT NULL,
    date DATETIME NOT NULL
);

4. 权限补充说明 #

• 避免选择系统保护目录（如C:\Windows）下的文件
• 若仍有权限问题，可尝试以管理员身份运行Python程序

内容的提问来源于stack exchange，提问作者user24367451