14.python保存Blob
使用BLOB之前的设置
Windows
-
临时修改:
mysql>set global max_allowed_packet=524288000; #修改 512M
-
修改my.cnf(windows下my.ini),需重启mysql。在 [MySQLd] 部分添加一句(如果存在,调整其值就可以):
max_allowed_packet=256M (根据实际情况调整数值)
可通过命令:
show VARIABLES like '%max_allowed_packet%’;
查看是否修改成功!
Linux
max_allowed_packet 如果不设置,默认值在不同的 MySQL 版本表现不同,有的版本默认1M,有的版本默认4M。
vi /etc/my.cnf 在mysqld节点下增加:128M或者256M,最大1G
[mysqld]
max_allowed_packet = 128M
保存BLOB到数据库
import pymysql
config = {
'host':'localhost',
'user':'wzq',
'password':'2923322454',
'charset':'utf8',
'db':'music_test'
}
connection = pymysql.connect(**config)
cursor = connection.cursor()
fin = open("./musics/马吟吟-溯.mp3",'rb')
music = fin.read()
fin.close()
sql = "INSERT INTO test VALUES(%s,%s)"
args = ('1',music)
cursor.execute(sql,args)
connection.commit()
cursor.close()
connection.close()
读取BLOB
import pymysql
config = {
'host':'localhost',
'user':'wzq',
'password':'2923322454',
'charset':'utf8',
'db':'music_test'
}
conn=pymysql.connect(**config)
cursor = conn.cursor()
cursor.execute("SELECT data FROM test LIMIT 1")
fout = open('./1.mp3', 'wb')
fout.write(cursor.fetchone()[0])
fout.close()
cursor.close()
conn.close()