使用pymsql存取图片

 

使用pymsql存取图片

2017-08-18 16:30:18 VeeLe 阅读数 606更多

分类专栏： python 数据库

 

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文链接：https://blog.csdn.net/ml_1019/article/details/77371781

0.遇到的问题

0.1. 在执行插入语句的时候报错：

Error 2013: Lost connection to MySQL server during query ([WinError 10054] An existing connection was forcibly closed by the remote ho

• 1

Error 1153: Got a packet bigger than 'max_allowed_packet' bytes

• 1

可能的原因：MYSQL参数 max_allowed_packet设置太小 
修改my.ini文件（Windows系统）一般在这个目录下C:\Program Files\MySQL\MySQL Server 5.5 
可参考： 
1.mysql:1153 Got a packet bigger than max_allowed_packet’bytes的解决方法 
2.Lost connection to MySQL server during query 
3.mysql下有好几个配置文件，但是没有my.ini

---

0.2 . 无法编码：因为中文

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

• 1

连接MYSQL的时候：加上charset,因为mysql默认以“latin”编码

db=pymysql.connect("localhost","pymysql","123123","TESTDB",charset='utf8')

• 1

---

0.3.关闭服务的报错：

Error 2003: Can't connect to MySQL server on 'localhost' ([WinError 10061] No connection could be made because the target machine actively refused it)

• 1

可能原因：mysql未启动

---

0.4.外部软件或者已存在连接 导致终止连接

Error 2013: Lost connection to MySQL server during query ([WinError 10053] An established connection was aborted by the software in your host machine

• 1

Error 2013: Lost connection to MySQL server during query ([WinError 10054] An existing connection was forcibly closed by the remote host)

• 1

可能原因：

1. 可视化软件导致–>重启软件
2. 插入的数据不符合要求以及其他可能原因：见 
   An existing connection was forcibly closed by the remote host 
   

0.5文件编码错误

UnicodeDecodeError: 'gbk' codec can't decode byte 0x80 in position 205: illegal multibyte sequence

• 1

原因：未以二进制形式打开—–>>f1 = open('result2\\' + filename,'rb')

---

1. 保存图片

• 建表
• 遍历本地目录
• 读取文件名和文件
• 写入数据库

建表语句：用的是中等blob类型存储。
sql_creat_tb='CREATE TABLE image(name VARCHAR(100), data mediumblob) 

• 1
• 2

blob类型大小可参考：MySQL Blob,MediumBlob,Longblob的字符长度

#存储部分代码
import pymysql
import sys
import os.path
import os
from io import StringIO

db=pymysql.connect("localhost","pymysql","123123","TESTDB",charset='utf8')
cursor = db.cursor()
sql_creat_tb='CREATE TABLE image(name VARCHAR(100), data mediumblob) '

try:
    cursor.execute(sql_creat_tb)
except:
    pass


rootdir = r'D:\Study_data\分词可视化\result2'
for parent,dirnames,filenames in os.walk(rootdir):
     for filename in filenames:

        try:
            f1 = open('result2\\' + filename,'rb')
            # filename = os.path.basename(filename)
            filename = filename[:-4]
            img = f1.read()
            f1.close()
            print("1")
            print(filename)


        except IOError as e:
            print ("Error %d: %s" % (e.args[0],e.args[1]))
            print("2")
            print(filename)
            sys.exit(1)

        try:

            print(len(img))

            cursor.execute("INSERT INTO Image SET Data=%s,name=%s",(img,filename))

            cursor.execute('SET CHARACTER SET utf8;')
            db.commit()

        except pymysql.Error as e:
            print ("Error %d: %s" % (e.args[0],e.args[1]))
            sys.exit(1)
        cursor = db.cursor()
db.close()

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• 51

---

2. 读取图片

• 查询图片数据和名字
• 以元祖形式解包
• 以本地路径+文件名+后缀打开文件并写入

import pymysql
import sys
import os.path
import os
from io import StringIO


db=pymysql.connect("localhost","pymysql","123123","TESTDB",charset='utf8')
cursor = db.cursor()

try:
    cursor.execute("SELECT Data,name FROM images")
    results_data=cursor.fetchall()
    db.commit()

except pymysql.Error as e:
    print ("Error %d: %s" % (e.args[0],e.args[1]))
    sys.exit(1)

try:
    for (data,filename) in results_data:
        f1 = open('result2_read\\' + filename+'.png','wb')
        f1.write(data)
        f1.close()
        #print(filename+"YES")


except IOError as e:
    print ("Error %d: %s" % (e.args[0],e.args[1]))
    print("2")
    #print(filename+"NO")
    sys.exit(1)

cursor = db.cursor()
db.close()

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35

---

其它存取图片的参考内容： python 向mysql中存储图片以及读取图片