How to insert b'\x1bB\x85' in MySQL's BLOB column from Python?-CodePudding

I am trying to insert the bytes in BLOB Type column in Python using pymysql.

The requirement is the byte should not be encoded when inserting.

My SQL looks like as follows when executing:

insert into product values(b'<\xf0Q,\x18\x99y\x86\xda\x81\xc8\x869Q\xf8\xc9\xb2\xc6NqG\xcfV\xbf{\x86\xf8\nJ\x0b\xfa)');

Getting the following error while trying to insert.


    cursor.execute(sql)
  File "home/.pyenv/versions/3.7.7/lib/python3.7/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "home/.pyenv/versions/3.7.7/lib/python3.7/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "home/.pyenv/versions/3.7.7/lib/python3.7/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "home/.pyenv/versions/3.7.7/lib/python3.7/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "home/.pyenv/versions/3.7.7/lib/python3.7/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "home/.pyenv/versions/3.7.7/lib/python3.7/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "home/.pyenv/versions/3.7.7/lib/python3.7/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "home/.pyenv/versions/3.7.7/lib/python3.7/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b'<\\xf0Q,\\x18\\x99y\\x86\\xda\\x81\\xc8\\x869Q\\xf8\\xc9\\xb2\\xc6NqG\\xcfV\\xbf{\\x86\\xf8\\nJ' at line 1")

CodePudding user response：

If you use parameter substitution pymysql will handle the bytes correctly.

>>> import pymysql
>>> conn = pymysql.connect(database='test')
>>> cur = conn.cursor()
>>> cur.execute("""CREATE TABLE t70182867 (id int not null auto_increment, data blob, primary key(id))""")
0
>>> bs = b'<\xf0Q,\x18\x99y\x86\xda\x81\xc8\x869Q\xf8\xc9\xb2\xc6NqG\xcfV\xbf{\x86\xf8\nJ\x0b\xfa)'
>>> sql = """INSERT INTO t70182867 (data) VALUES (%s)"""
>>> cur.execute(sql, (bs,))  # <= let pymysql handle the values
1
>>> conn.commit()
>>> cur.execute("""SELECT data FROM t70182867""")
1
>>> cur.fetchone()
(b'<\xf0Q,\x18\x99y\x86\xda\x81\xc8\x869Q\xf8\xc9\xb2\xc6NqG\xcfV\xbf{\x86\xf8\nJ\x0b\xfa)',)
>>>