本文共 4264 字,大约阅读时间需要 14 分钟。
MYSQL 是支持把图片存入数据库的,也相应的有一个专门的字段 BLOB (Binary Large Object),即较大的二进制对象字段
CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB); CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data LongBlob);
以上两条语句分别创建两个不同大小的BLOB表
测试如下:
#coding:utf-8#获取版本import MySQLdb as mdbimport sysdb= Nonetry: db = mdb.connect("localhost","root","chejian","TESTDB") cursor = db.cursor() cursor.execute("SELECT VERSION()") data = cursor.fetchone() print "Database version : %s" %(data)finally: if db: db.close()#创建表并插入数据db = mdb.connect("localhost","root","chejian","TESTDB")cursor = db.cursor()try: cursor.execute("DROP TABLE IF EXISTS Writers") cursor.execute("CREATE TABLE IF NOT EXISTS \ Writers(Id INT PRIMARY KEY AUTO_INCREMENT,Name VARCHAR(25))") cursor.execute("INSERT INTO Writers(Name) VALUES('Jack London')") cursor.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')") cursor.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')") cursor.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')") cursor.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')") #当对数据库数据改变时,提交到数据库执行 db.commit()except: #若有错误,则回滚 db.rollback()finally: if db: db.close()#提取数据 元组db = mdb.connect("localhost","root","chejian","TESTDB")cursor = db.cursor()sql = "SELECT * FROM Writers"try: cursor.execute(sql) #获取结果集的条数 num = int(cursor.rowcount) for i in range(num): row = cursor.fetchone() print row[0],row[1]except: print "Error: unable to fetcg data"finally: if db: db.close()#使用字典来获取数据db = mdb.connect("localhost","root","chejian","TESTDB")cursor = db.cursor(mdb.cursors.DictCursor)try: cursor.execute(sql) rows = cursor.fetchall() for row in rows: print row["Id"],row["Name"]except: print "Error2: unable to fetcg data"finally: if db: db.close()#获取单个表的字段名和信息的方法db = mdb.connect("localhost","root","chejian","TESTDB")cursor = db.cursor()try: cursor.execute(sql) rows = cursor.fetchall() desc = cursor.description print 'cur.description :',desc for row in rows: print rowexcept: print "Error3: unable to fetcg data" sys.exit(1)finally: if db: db.close()#查询影响了多少行db = mdb.connect("localhost","root","chejian","TESTDB")cursor = db.cursor()#sql = "UPDATE Writers SET Name = %s WHERE Id = %s" %("Guy de Mau","4")try: cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s" ,("Guy de Mau","4")) #cursor.execute(sql) db.commit() num = int(cursor.rowcount) print numexcept: db.rollback() print "Error3: unable to fetcg data" sys.exit(1)finally: if db: db.close()#CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);import MySQLdb as mdbimport systry:# 用读文件模式打开图片 fin = open("C:\Users\chejian\Pictures\lovewallpaper\9006-106.jpg")# 将文本读入 img 对象中 img = fin.read()# 关闭文件 fin.close()except IOError, e:# 如果出错,打印错误信息 print "Error %d: %s" % (e.args[0], e.args[1]) sys.exit(1)try:# 链接 mysql,获取对象 conn = mdb.connect(host='localhost', user='root', passwd='chejian', db='testdb')# 获取执行 cursor cursor = conn.cursor()# 直接将数据作为字符串,插入数据库 cursor.execute("INSERT INTO Images SET Data='%s'" % mdb.escape_string(img))# 提交数据 conn.commit()#释放资源 cursor.close()except mdb.Error, e:# 若出现异常,打印信息 conn.rollback() print "Error %d: %s" % (e.args[0], e.args[1]) sys.exit(1)finally: if conn: conn.close()try:#连接 mysql,获取连接的对象 conn = mdb.connect(host='localhost', user='root', passwd='chejian',db='testdb'); cursor = conn.cursor()#执行查询该图片字段的 SQL cursor.execute("SELECT Data FROM Images LIMIT 1")#使用二进制写文件的方法,打开一个图片文件,若不存在则自动创建 fout = open('9006-106.jpg','wb') #直接将数据如文件 fout.write(cursor.fetchone()[0])#关闭写入的文件 fout.close()#释放查询数据的资源 cursor.close()except IOError, e:#捕获 IO 的异常 ,主要是文件写入会发生错误 print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)finally: if conn: conn.close()对取出来的图片进行查看,发现只有左上角的非常小的一部分,继续测试中
转载地址:http://cfgji.baihongyu.com/