How to solve the 1064 error when inserting binary images into mysql using python3

Insert binary image into mysql 1064 error

 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()

When the above code is run, mysql will report a 1064 error:

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")

Modification: Change ('%s', '%s', '%s') to (%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()

Successfully run again;

Due to the interference of some documents, I spent a long time on the Internet to find the cause of the problem. The parameter %s in pymysql does not need to be added with a '' sign. When we insert a string, an error may not be reported for this problem, because the character The string itself also has quotation marks. If you add quotation marks again, an error will not be reported, but if it is in binary format, an error will be reported.

How to save pictures in binary to mysql

MYSQL supports saving pictures to the database, and there are corresponding A special field BLOB (Binary Large Object)

First create a table in your mysql database to store pictures

CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);

Then use python code to save local pictures to the database

# coding=utf-8
 
import MySQLdb
import sys
 
try:
    fin = open("/home/dsq/tb/8.jpg") #打开本地图片，路径要写自己的
    img = fin.read()
    fin.close()   #读取结束，关闭文件
except IOError as e:
    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exit(1)   #出现错误打印错误并退出
 
 
try:
    conn = MySQLdb.connect(host="localhost", port=3306, user="root", passwd="#你的数据库密码#", db="数据库名")    #连接到数据库
 
    cursor = conn.cursor()    #获取cursor游标
    cursor.execute("INSERT INTO Images SET Data='%s'" % MySQLdb.escape_string(img))   #执行SQL语句
 
    conn.commit()   #提交数据
    cursor.close()
    conn.close()    #断开连接
except MySQLdb.Error,e:
    conn.rollback()
    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exit(1)    #出现错误，自动回滚，打印错误并退出

found that the picture was successfully stored in the database

The above is the detailed content of How to solve the 1064 error when inserting binary images into mysql using python3. For more information, please follow other related articles on the PHP Chinese website!