1

I'm currently using PyMySQL to make queries to my MySQL DB. I wanted the results to be a dictionary, where the columns are the keys and with their associated values, and I've got that. But I've noticed that values for types like dates and decimals are returned as objects, which is not what I've encountered with other languages and libraries. An example is:

{'date': datetime.date(2023, 12, 26), 'store_number': 7, 'total': Decimal('11336.43')}

Ideally, I just want something like:

{'date': '2023-12-26', 'store_number': 7, 'total': 10036.43}

What would be the best way to do this? I don't necessarily need to use PyMySQL, but it seems like a popular choice. I've read and even asked ChatGPT, but I figured I'd ask real people about what they have done and know. The options I've found are:

  1. Some suggest to iterate or use list comprehensions to convert the values, but that seems a little strange, maybe even silly, to me that I need to do that or write a custom function to do this every time.
  2. Using converters. This one seemed more reasonable.
  3. Casting values in the query. This also seems odd to me, as I've never had to resort to doing something like this before.

Any help or insight would be appreciated. Thanks!

Improve this question

1 Answer 1

Reset to default
1

That is likely possible changing some inner configuration for PyMysql - it just does not make much sense.

The objects retrieved are at once semantically more correct, easier to operate with, and (almost) trivial to convert back to strings, using either f-strings or the .format string method.

The exception for a "trivial conversion" to string being a datetime object, but once you know the following, it is again more practical: datetime Python objects accept the same field indicators usable in the datetime.strftime method as formatting indicators in an f-string (or str.format call):

In [8]: x = datetime.now()

In [9]: f"{x}"
Out[9]: '2024-06-04 19:56:53.551617'

In [10]: f"{x:%Y-%m-%d}"
Out[10]: '2024-06-04'

How to get values as strings

So, it ends up a connection object in pymysqldb has a "converters" attribute, which maps numeric column types in MySQL to Python callables that will be called with the fetched value.

By replacing this attribute in active connection it is possible to override the transform. Each callable takes a single input (which, in my tests where strings, but could possibly be bytes or bytearray) , and returns the converted object.

Without caring for the column type, it is possible to force a set of "converters" that will return the value itself, regardless of the column type:

from collections import defaultdict

con = pymysql.connect(...)

new_converters = defaultdict(lambda: pymysql.converters.through)
# optionally, save the original converters elsewhere if you
# want to restore the original behavior/
con.converters = new_converters

Results using this connection should now bring out strings (even for int or float - one has to selectively change the values on con.converters if the intent is to preserve those)

Given the OP comment that they want strings for JSON generating purposes, I will reiterate the most correct approach of getting the proper Python objects, and controlling their serialization into JSON in the proper (output) layer, rather than taking values "as they are" in the database. This allows your app to have control against faulty values or mismatches between the DB string representation of a value and whatever the frontend code is expecting.

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

2 Comments

Thanks for the input! Yeah, I can see scenarios where someone would want the returned values like this. In my case, I'm working in web dev, so I just wanted to get these results and return them as JSON. But I'd have to convert the values first, which is how I ended up with this question.
there it is. all disclaimers already spelled out! :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.