通过python在mysql中插入和检索图像
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22141718/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting and retrieving images into mysql through python
提问by user3320033
I used this code to insert an image into mysql database and retrieve back the image.
This code works perfectly with no errors , but the problem is even after inserting an image into img table , when I execute the command select * from img ;in the mysql command line it shows no records.
我使用此代码将图像插入 mysql 数据库并取回图像。这段代码可以完美运行，没有错误，但问题是即使在将图像插入 img 表之后，当我select * from img ;在 mysql 命令行中执行命令时，它也没有显示任何记录。
Table created in database;create table img(images blob not null);
在数据库中创建的表；create table img(images blob not null);
import mysql.connector
import sys
from PIL import Image
import base64
import cStringIO
import PIL.Image
db = mysql.connector.connect(user='root', password='abhi',
host='localhost',
database='cbir')
#image = Image.open('C:\Users\Abhi\Desktop\cbir-p\images.jpg')
with open("C:\Users\Abhi\Desktop\cbir-p\images.jpg", "rb") as image_file:
encoded_string = base64.b64encode(image_file.read())
#blob_value = open('C:\Users\Abhi\Desktop\cbir-p\images.jpg', 'rb').read()
sql = 'INSERT INTO img(images) VALUES(%s)'
args = (encoded_string, )
cursor=db.cursor()
cursor.execute(sql,args)
sql1='select * from img'
cursor.execute(sql1)
data=cursor.fetchall()
#print type(data[0][0])
data1=base64.b64decode(data[0][0])
file_like=cStringIO.StringIO(data1)
img=PIL.Image.open(file_like)
img.show()
db.close()
回答by user3320033
import mysql.connector
import sys
from PIL import Image
import base64
import cStringIO
import PIL.Image
db = mysql.connector.connect(user='root', password='abhi',
host='localhost',
database='cbir')
image = Image.open('C:\Users\Abhi\Desktop\cbir-p\images.jpg')
blob_value = open('C:\Users\Abhi\Desktop\cbir-p\images.jpg', 'rb').read()
sql = 'INSERT INTO img(images) VALUES(%s)'
args = (blob_value, )
cursor=db.cursor()
cursor.execute(sql,args)
sql1='select * from img'
db.commit()
cursor.execute(sql1)
data=cursor.fetchall()
print type(data[0][0])
file_like=cStringIO.StringIO(data[0][0])
img=PIL.Image.open(file_like)
img.show()
db.close()
This code works fine
这段代码工作正常
回答by Arun Kumar
import mysql.connector
import base64
import io
import PIL.Image
with open('lemonyellow_logo.jpg', 'rb') as f:
photo = f.read()
encodestring = base64.b64encode(photo)
db= mysql.connector.connect(user="root",password="lyncup",host="localhost",database="demo")
mycursor=db.cursor()
sql = "insert into sample values(%s)"
mycursor.execute(sql,(encodestring,))
db.commit()
sql1="select * from sample"
mycursor.execute(sql1)
data = mycursor.fetchall()
data1=base64.b64decode(data[0][0])
file_like=io.BytesIO(data1)
img=PIL.Image.open(file_like)
img.show()
db.close()
