CODEWITHSUNDEEP
pythonmysql
ToniW
ToniW

Reputation: 21

Python MySQL connector returns bytearray instead of string for prepared statements

Given MySQL table like such:

create table demo (id int, name varchar(100)) collate utf8mb4_general_ci;
insert into demo values (1,'abcdef');

And Python script:

import mysql.connector
db = mysql.connector.connect(host='xx', user='xx', password='xx', database='xx')
cursor = db.cursor()
cursor.execute('select * from demo')
for row in cursor:
    print(row)

This produces the expected result:

(1, 'abcdef')

If I however change the cursor to a prepared cursor:

cursor = db.cursor(prepared=True)

the result is unexpected:

(1, bytearray(b'abcdef'))

I'm using Python 3.8.0 and mysql.connecter version 2.2.9 In the release notes of MySQL connector 2.1.8 (https://dev.mysql.com/doc/relnotes/connector-python/en/news-2-1-8.html) I read

When using prepared statements, string columns were returned as bytearrays instead of strings. The returned value is now a string decoded using the connection's charset (defaults to 'utf8'), or as a bytearray if this conversion fails. (Bug #27364914)

so I did not expect the behavior in the version I'm using.

What am I missing?

Upvotes: 2

Views: 1858

Answers (1)

Booboo
Booboo

Reputation: 44128

The text should probably read:

The returned value is now a string encoded using the connection's charset (defaults to 'utf8'), or as a bytearray if this conversion fails. (Bug #27364914).

>>> 'abcdef'.encode('utf8') == b'abcdef'
True
>>>

So, when using cursor = db.cursor(prepared=True), the driver is doing what the documentation says it will do (if the encoding fails, only then will it return a bytearray, but otherwise expect a byte string -- this is the change being described). But I see no reason to specify prepared=True for you can use prepared statements without that and get the results you have come to expect if you are only using the prepared statement as a mechanism to avoid SQL Injection attacks and are not using it for repetitive execution.

Update

I did a small benchmark with and without using prepared=True retrieving 10,882 rows:

import mysql.connector

def foo(db):
    cursor = db.cursor(prepared=True)
    for i in range(10883):
        cursor.execute('select Company from company where PK_Company = %s', (i,))
        rows = cursor.fetchall()
    print(rows[0][0]) # print last fetched row

db = mysql.connector.connect(user='xx', password='xx', database='xx')
foo(db)

Results:

With `prepared=True`: 2.0 seconds for function `foo`
Without `prepeared=True`: 1.6 seconds for function `foo`
Using pymysql: 1.5 seconds for function `foo`

It would seem that prepared=True runs more slowly. ????

Upvotes: 1

Related Questions