String field in Mysql returns as bytearray type using Python

Asked

Viewed 75 times

1

When performing a query in Mysql using Python, I get a return of type bytearray, being that the column is of type String.

import mysql.connector as myc

# conexão MySQL
conMySQL = myc.connect (host = 'localhost',user = 'root',password = 'mysql',database = 'teste')

cursorMySQL = conMySQL.cursor()
sqlMySQL = """SELECT
  f.fid,
  f.uri,
  n.nid AS FK_REGISTRO_VIRTUAL
FROM field_data_field_anexos a
JOIN node n ON a.entity_id = n.nid
JOIN file_managed f ON a.field_anexos_fid = f.fid
WHERE n.`type` = 'registro_virtual' and f.fid = 76881;"""
cursorMySQL.execute(sqlMySQL)

result = cursorMySQL.fetchall()
for fid, uri, nid in result:
    print(fid ,'-', uri.decode('utf8'), '-', nid)

cursorMySQL.close()
conMySQL.close()

Follow the return:

76881 - bytearray(b'public://private/registry-virtual/2018/11/Audi xc3 xaancia-Cust xc3 xb3dia-29-08-0007877-15.2017.815.2002.pdf') - 65542

And by replacing the print(fid ,'-', uri, '-', nid) for print(fid ,'-', uri.decode('utf8'), '-', nid), I get the following return:

Unicodeencodeerror: 'ascii' codec can’t Encode Character ' xea' in position 46: ordinal not in range(128)

I checked that Decode('utf8') only works when the string to be processed does not come directly from the database.

How can I solve?

  • And the encoding of the column is utf8 really? Have you tried using latin1 or utf16? Because the error is that the byte sequence is not a valid UTF8 string, so maybe it’s just not UTF8...

  • I have tried using latin1 and utf16 and also did not work. When charset the column and table still do not know what is.

  • That reply from Soen has the queries for you to recover the Charsets of tables, columns and databases.

  • I figured out the column type, it’s utf8_bin, only by changing to print(fid ,'-', Uri.Decode('utf8_bin'), '-', Nid) returns Lookuperror: Unknown encoding: utf8_bin

  • utf8_bin is the collation, it means that the column is utf8 and the comparisons of this column are done in binary form. What I’m wondering is why python is using the 'ascii' codec. The Encode works normally, maybe the problem is in another part of the code.

  • I also checked the terminal and it works, but when reading from the database it does not, including other columns String when reading from the database returns the same error. I tried to read a column with the collation utf8_general_ci and the same error occurs. Must have some treatment to read database string that I’m not aware of.

  • Then the most advisable and search the documentation of Connector that you’re using.

Show 2 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.