An error has been reported while accessing images in the mysql database using Pymysql. The code is as follows
Error message: (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 ‘\xff\xd8\xff\xe0\x00\x10JFIF\x00\x01\x01\x00\x00\x01\x00\x01\x00\x00\xff\xdb\x00′ at line 1”)
I found a lot of methods on the Internet, but I couldn’t solve the problem. Later, I tried again and again and finally solved it
Note that the size of the binary BLOB in mysql is smaller than the size of the binary BLOB when designing a table, change the binary data to mediumBLOB
The code is as follows:
import pymysql
from datetime import datetime
To create the database, first connect to the mysql database
Conn = Pymysql.connect (host=’127.0.0.1′, user=’root’, Database =’ smart_apartment_DB ‘,
password=’160507pcsd’, charset=’utf8′)
Create a cursor
cursor = conn.cursor()
The # ExCute () function can execute simple SQL statements
f = open(file=’./img/imageOne.jpg’, mode=’rb’)
dataimg = f.read()
f.close()
nowtime = datetime.now().strftime(“%Y-%m-%d, %H:%M:%S”)
argdata = pymysql.Binary(dataimg)
sqlone = “insert into img_pack(imgtime,imgdata) values(‘%s’,’%s’)” % (
nowtime, argdata)
try:
cursor.execute(sqlone)
conn.commit()
except Exception as e:
conn.rollback()
Print (” Error message: “, e)
Close the cursor first
cursor.close()
Close the database connection again
conn.close()
 
After many attempts, the problem was found
sqlone = “insert into img_pack(imgtime,imgdata) values(‘%s’,’%s’)” % (
Remove the ‘%s’ single quotation mark from nowtime, argdata and change to
sqlone = “insert into img_pack(imgtime,imgdata) values(%s,%s)”
Cursor. Execute (SQlone) changed to CURSOR. Execute (SQlone, (nowtime, argdata))
You can store images in a binary stream like mysql
The correct code is as follows
import pymysql
from datetime import datetime
 
To create the database, first connect to the mysql database
Conn = Pymysql.connect (host=’127.0.0.1′, user=’root’, Database =’ smart_apartment_DB ‘,
password=’160507pcsd’, charset=’utf8′)
Create a cursor
cursor = conn.cursor()
The # ExCute () function can execute simple SQL statements
 
f = open(file=’./img/imageOne.jpg’, mode=’rb’)
dataimg = f.read()
f.close()
nowtime = datetime.now().strftime(“%Y-%m-%d, %H:%M:%S”)
argdata = pymysql.Binary(dataimg)
 
sqlone = “insert into img_pack(imgtime,imgdata) values(%s,%s)”
 
try:
cursor.execute(sqlone, (
nowtime, argdata)
)
conn.commit()
except Exception as e:
conn.rollback()
Print (” Error message: “, e)
 
 
Close the cursor first
cursor.close()
Close the database connection again
conn.close()