python - 将 python 字节对象写入 pgSQL bytea 列：字节字符串中的语法错误

Question

我正在编写一个工具来解码和分析测试车辆的 CAN 数据，并将其与摄像机镜头一起显示。

对于摄像机镜头，我使用 openCV 从提供的 .avi 文件中获取帧，然后将其传递给 PIL 以降低质量并裁剪图像的一角，其中毫秒纪元时间戳被 pytesseract OCR 识别。

接下来我想将图像作为 bytea 存储在我的 postgreSQL 数据库中。

我的代码是这样工作的：这是上述代码的简化版本：

from io import BytesIO
from PIL import Image

# Image actually comes from cv2, in this case i just use a local jpg as example
image_path = 'meme.jpg'
img = Image.open(image_path)

# Dictionary for the processed Data
img_dict = {'TestDriveID': [], 'TimeStamp':[], 'Data':[]}

# here i store the image to a BytesIO(), reducing quality to 20%
buffer = BytesIO()
img.save(buffer, 'JPEG', quality=20)

# the timestamp is actually obtained via ocr, here i use an arbitrary one.
timestamp = 1619435452340

# write the information to a dictionary to later pass it on to the uploading
# code and/or further uses
img_dict['TestDriveID'].append('test123')
img_dict['TimeStamp'].append(timestamp)
img_dict['Data'].append(buffer)

然后将此字典传递给应该上传图像的函数：

import psycopg2
from sqlalchemy import create_engine
import io

# set up connection definition
sql_con_str = f'{driver}://{user}:{password}@{host}:{port}/{dbname}'
# create engine
sql_con = create_engine(sql_con_str)
# create a raw_connection object from the pgsql engine
raw_con = sql_con.raw_connection()
# define a cursor for the insertion
cur = raw_con.cursor()

for i in range(0, len(img_dict['TestDriveID'])):
    buffer = (img_dict['Data'][i])
    buffer.seek(0)

    cur.execute("""INSERT INTO images ("TestDriveID", "TimeStamp", "Data") 
                    VALUES ('%s', %s, %s)""" %(img_dict['TestDriveID'][i],img_dict['TimeStamp'][i],buffer.read()))
    raw_con.commit()

raw_con.close()

当我执行此代码时，出现以下异常：

---------------------------------------------------------------------------
SyntaxError                               Traceback (most recent call last)
<ipython-input-77-25a01ac78ad2> in <module>
     52     buffer.seek(0)
     53 
---> 54     cur.execute("""INSERT INTO svd_images ("TestDriveID", "TimeStamp", "Data") 
     55                     VALUES ('%s', %s, %s)""" %(img_dict['TestDriveID'][i],img_dict['TimeStamp'][i],buffer.read()))
     56     raw_con.commit()

SyntaxError: syntax error at or near "("
LINE 2: ...1\x15R\xd1\xf0$3br\x82\t\n\x16\x17\x18\x19\x1a%&\'()*456789:...
                                                             ^

我该如何解决这个问题？或者有没有更有效的方法来做到这一点？我打算像这样一次插入 45000 张图像。（30 分钟视频，25fps，27kb/帧）

在此先感谢，我希望我提供了足够的信息，我对此很陌生。

标签： pythonpostgresqlimagebytebytea