python3 往mysql插入二进制图片 1064错误

一时失言乱红尘 2023-06-04 13:55 11阅读 0赞

conn = pymysql.connect(*)
     cur = conn.cursor()
     os.chdir('/home/jibo/zxcsSpider/images/full/')
     list = os.listdir()
     count1 = 0
     for imagename in list:
         count1 += 1
         f = open(imagename, 'rb')
         data = f.read()
         f.close()
         imagepath = '/home/images/full/' + imagename
         imagebin = pymysql.Binary(data)
         sql = "insert into images(imagename,imagepath,imagebin) values('%s', '%s', '%s')"
         cur.execute(sql, (imagename, imagepath, imagebin))
         conn.commit()
     print('image:' + str(count1))
     cur.close()
     conn.close()

上面代码运行时，mysql会报1064错误：

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cecedd67b6d4716bdbab9fac36e0b2ad1c81cbac.jpg'', ''/home/images/f' at line 1")

修改：将('%s', '%s', '%s')改为（%s, %s, %s）

conn = pymysql.connect(*)
     cur = conn.cursor()
     os.chdir('/home/images/full/')
     list = os.listdir()
     count1 = 0
     for imagename in list:
         count1 += 1
         f = open(imagename, 'rb')
         data = f.read()
         f.close()
         imagepath = '/home/images/full/' + imagename
         imagebin = pymysql.Binary(data)
         sql = "insert into images(imagename,imagepath,imagebin) values(%s, %s, %s)"
         cur.execute(sql, (imagename, imagepath, imagebin))
         conn.commit()
     print('image:' + str(count1))
     cur.close()
     conn.close()

再次运行成功；  
因为某些文档干扰，在网上找了大半天才找倒原因问题，pymysql中的参数%s是不用加''号的，当我们插入字符串的时候可能不会应为这个问题报错，因为字符串本身也有引号，再次加引号不会报错，但二进制格式的话就会报错