数据库MySQL存储图片数据

环境Ubuntu16.04   python2

转载自：http://www.cnblogs.com/sherlockhua/archive/2012/03/29/2423786.html

安装环境：

sudo apt-get install mysql-server

sudo apt-get install python-mysqldb

创建数据库：

mysql> CREATE DATABASE testdb;

mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test623';
mysql> USE testdb;
mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost';

创建数据存储表

mysql> CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);

mysql> quit;

保存图片到数据库：

#!/usr/bin/python


# -*- coding: utf-8 -*-
import MySQLdb as mdb
import sys


try:
    fin = open("chrome.png")
    img = fin.read()
    fin.close()


except IOError, e:
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
try:
    conn = mdb.connect(host='localhost',user='testuser',
       passwd='test623', db='testdb')
    cursor = conn.cursor()
    cursor.execute("INSERT INTO Images SET Data='%s'" % \
        mdb.escape_string(img))
    conn.commit()
    cursor.close()
    conn.close()


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

从数据库读取图片数据文件：

#!/usr/bin/python

# -*- coding: utf-8 -*-
import MySQLdb as mdb 
import sys
try:
    conn = mdb.connect(host='localhost',user='testuser', 
        passwd='test623', db='testdb')
    cursor = conn.cursor()
    cursor.execute("SELECT Data FROM Images LIMIT 1")
    fout = open('image.png','wb')
    fout.write(cursor.fetchone()[0])
    fout.close()
    cursor.close()
    conn.close()

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