MySQL / python conncector conversion arguments not working

Question

I am trying to use the following additional arguments in the mysql.connector.connect(), however when I run the following code it doesn't seem to have any effect.

import mysql.connector
cnx = mysql.connector.connect(
    ....
    raw='true',
    use_unicode='false'
)
cursor = cnx.cursor()
query = ("...")
cursor.execute(query)
result = cursor.fetchall()
print result

[(datetime.date(2015, 4, 5), 1243), ...

where the results has a column of MySQL datetime format values but even with these arguments the output is still in python datetime.date(YYYY,M,D) format

same issue also when calling col = cursor.column_names, returns unicode instead of strings even though "raw" is set to true. print cursor.colun_names = (u'string_1', u'string_2')

is there some other configuration needed? i dont want to have to write code to convert these every time its used. thanks!

Are you sure you're supposed to be passing in string literals of 'true' and 'false' and not boolean values? — Falmarri
– Falmarri, Commented Apr 10, 2015 at 18:46
No errors in the code. I removed the single quotes and replaced with True / False boolean and am still getting the same result with the unicode column names. The datetime is now a bytearray(b'2015-04-05') which is a step in the wrong direction for me. — Jonathan
– Jonathan, Commented Apr 10, 2015 at 18:51
How did you get the data into the database in the first place? — Matthew Turner
– Matthew Turner, Commented Apr 10, 2015 at 18:53
it is an existing database I am working with, but can confirm that the field is a mysql "date" field. To clarify my goal, i would like the date output to be a string in the "2015-04-05" format so that it can be read easily — Jonathan
– Jonathan, Commented Apr 10, 2015 at 18:58
Looks like Alexander is right, check the documentation: dev.mysql.com/doc/connector-python/en/…. Search for "date" and it says use raw=True. — Matthew Turner
– Matthew Turner, Commented Apr 10, 2015 at 19:05

Alexander R. · Accepted Answer · 2015-04-10 20:34:21Z

1

I think when Python evaluates something like:

if use_unicode:
   // do something

Variable use_unicode returns True, because string 'false' evaluated to True. Try to use Boolean values.

import mysql.connector
    cnx = mysql.connector.connect(
        ....
        raw=True,
        use_unicode=False
    )
    cursor = cnx.cursor()
    query = ("...")
    cursor.execute(query)
    result = cursor.fetchall()
    print result

raw=True means:

A MySQLCursorRaw cursor skips the conversion from MySQL data types to Python types when fetching rows. A raw cursor is usually used to get better performance or when you want to do the conversion yourself.

So you get ByteArray as expected. I don't know what exactly you are trying to do, but when I work with MySQL in Python, I prefer to work with dictionary data structure.

cursor = cnx.cursor(dictionary=True)

And then you will get result as dictionary. If you want to view it as string, just do print str(dict)

import mysql.connector
    cnx = mysql.connector.connect(user=self.username, password=self.password, host=self.host, database=self.database);
    cursor = cnx.cursor(dictionary=True)
    query = ("...")
    cursor.execute(query)
    result = cursor.fetchall()
    print str(result)

edited Apr 10, 2015 at 20:34

answered Apr 10, 2015 at 18:59

Alexander R.

1,75612 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonathan Over a year ago

Thanks, the "raw" argument seems to be working, although I don't understand why it returns a bytearray instead of a string (as if you copy-paste from sql client). The "use_unicode" must not apply to cursor.column_names as it still returns unicode

Collectives™ on Stack Overflow

MySQL / python conncector conversion arguments not working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related