Python with MySQL

在进行大数据分析的时候需要抓取大量数据，其中包括各种字段，特别是图片。在这中间，遇到了各种事情，包括SQL语句、图片的导入MySQL等易出错任务。

一、图片进MySQL

with open('E:/PythonWorking/befor523/AutomateTheBoringStuffWithPython/mv1/122600094811.JPG', 'rb') as fp:
	data = fp.read()

二、插入

with open('E:/PythonWorking/befor523/AutomateTheBoringStuffWithPython/mv1/122600094811.JPG', 'rb') as fp:
	data = fp.read()
cursor.execute("update EMPLOYEE set IMAGE = (%s) where ID = 3 ", [data])

三、更新数据

sql = "update EMPLOYEE set IMAGE = (%s) where ID = (%s) "
cursor.execute(sql, (image, 4))  # %

占位符统统是%s字符串类型，例如本例中ID为整数类型，但是仍为%s。

四、删除数据

根据Id删除数据

sql = "delete from EMPLOYEE where ID = (%s) "

五、从数据库读取图片

sql = "SELECT IMAGE FROM employee WHERE Id = (%s)"

cursor.execute(sql, Id)
# t = cursor.fetchone()[0]
with open('E:/PythonWorking/test/image3.JPG', 'wb') as file:
    file.write(cursor.fetchone()[0])
cursor.close()

根据ID从数据库中读取对应的图片并保存在本地