python - 使用mysql在python中插入图像

Question

我只是想使用 mysql 在 python 中插入图像，这是我的代码

import mysql.connector
import base64

conn = mysql.connector.Connect(user="root",passwd="solomon",host="localhost",db="binarymanipulation",port="3306")

cursor = conn.cursor();
with open('/home/solomon/Downloads/javapic.jpeg', 'rb') as image:
    imager = image.read()

cursor.execute("INSERT INTO images(id,size,image) VALUES('PYTHON',245,imager)")
conn.commit();
conn.close()

运行此代码时出现以下错误:

error/usr/bin/python2.7 /home/solomon/PycharmProjects/untitled/Helloworrld.py
Traceback (most recent call last):
  File "/home/solomon/PycharmProjects/untitled/Helloworrld.py", line 10, in <module>
    cursor.execute("INSERT INTO images(id,size,image) VALUES('PYTHON',245,imager)")
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 507, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 722, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 640, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'imager' in 'field list'

Answer 1

import mysql.connector
import base64

conn = mysql.connector.Connect(user="root",passwd="solomon",host="localhost",db="binarymanipulation",port="3306")

cursor = conn.cursor();
with open('/home/solomon/Downloads/javapic.jpeg', 'rb') as image:
    imager = image.read()

cursor.execute("INSERT INTO images(id,size,image) VALUES('PYTHON',245,imager)")
conn.commit();
conn.close()

因为sql不是格式，它是Python中的变量，尝试执行sql:

INSERT INTO images(id,size,image) VALUES('PYTHON',245,imager)

会得到同样的错误，您可以先格式化它可能会有所帮助:

import mysql.connector
import base64

conn = mysql.connector.Connect(user="root",passwd="solomon",host="localhost",db="binarymanipulation",port="3306")

cursor = conn.cursor();
with open('/home/solomon/Downloads/javapic.jpeg', 'rb') as image:
    imager = image.read()

sql = "INSERT INTO images(id,size,image) VALUES('PYTHON',245,%s)" % imager
cursor.execute(sql)
conn.commit()
conn.close()