How to store pictures to MySQL and display them using Python3.x?

python3 stores pictures in mysql and displays them

First we build the database, then enter cmd, enter ipconfig in the system operation box to display this The ip information of the machine

#The main thing is to know the ip address of the machine.

We need to create a table in mysql first, set the fields, and enter the following code:

create table image（id int（5）auto_increment primary key，image1 mediumblob）

Then we need to open python, I use pycharm.

The package we need to use is pymysql

We drag the image we want to import into pycharm, set the name for later use, we first read the image file (use binary reading Method, ‘rb’)

f = open('2.jpg','rb')
img = f.read()
f.close()

Then we connect with mysql:

gao = pymysql.connect(
                       host='157.142.13.27',#本机或其他机器的ip地址
                       port=3306,#接口
                       user='root',#用户名
                       passwd='******',#密码
                       db='gao',#你所使用的数据库
                       charset='utf8',#编码方式
                       use_unicode=True, )

Create a cursor and use the cursor function:

g = gao.cursor()

Read image information

f = open('2.jpg','rb')data = f.read()

Use the execute function to modify the database:

g.execute('insert into image(image1) values (%s)'%data)

Note: If it always shows that you do not have permission to connect to the database, we first log in to the database, enter your mysql database, and then change the host:

update user set host = '%' where user = 'root';mysql>flush rivileges

Note Be sure to close the cursor and connection at the end.

After writing, we can check the contents of the table we set in the database, but due to the decoding method, what we see is a bunch of garbled characters. We can display the picture in pycharm.

Python imports pictures into mysql database

# 今天需要用Python实现将图片导入MySQL数据库。看了看网上的一些写法，自己也过一遍，记录下来，以防忘记。
 
# 功能：将图片导入到MySQL数据库
import sys
import pymysql
from PIL import Image
import os
 
path = "./"
 
#读取图片文件
fp = open("./陈丹江-420381198212068517.JPG", 'rb')
img = fp.read()
fp.close()
 
#建立一个MySQL连接
database = pymysql.connect(host="10.31.143.6", user="root", passwd="******", db="aaa", charset='utf8')
# 存入图片
# 创建游标
cursor = database.cursor()
#注意使用Binary()函数来指定存储的是二进制
sql = "INSERT INTO vbp_person_ext (person_id, image, img_type) VALUES  (%s, %s, %s);"
args = ('39', img, 'JPG')
cursor.execute(sql, args)
 
database.commit()
# 关闭游标
cursor.close()
# 关闭数据库连接
database.close()
print("============")
print("Done! ")

The database table vbp_person_ext contains three fields: person_id, image, img_type (image is picture information)

Encountered problems during the process

Error:

UnicodeEncodeError: 'latin-1' codec can't encode characters in position 303-304: ordinal not in range(256)

Solution: Add charset='utf8

The above is the detailed content of How to store pictures to MySQL and display them using Python3.x?. For more information, please follow other related articles on the PHP Chinese website!