Search code examples
pythonmysqlflasksumdecimal

flask mysql SUM() Decimal in Dictionary from cursor.fetchone()


I'am connecting my flask-app to a MySQL-Database like this:


from flask_mysql_connector import MySQL
from flask_mysqldb import MySQL


# MySQL Connection
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = ''
app.config['MYSQL_DB'] = 'Prices'
app.config['MYSQL_PORT'] = 3306
app.config['MYSQL_DATABASE_SOCKET'] = ''
app.config['MYSQL_UNIX_SOCKET'] = ''
app.config['MYSQL_CONNECT_TIMEOUT'] = ''
app.config['MYSQL_READ_DEFAULT_FILE'] = ''
app.config['MYSQL_USE_UNICODE'] = ''
app.config['MYSQL_CHARSET'] = ''
app.config['MYSQL_SQL_MODE'] = ''
app.config['MYSQL_CURSORCLASS'] = 'DictCursor'


mysql = MySQL()
mysql.init_app(app)

Performing a query like this:

cursor.execute("SELECT SUM(total) AS sumy FROM tbl")
total = cursor.fetchone()
total = total["sumy"]

returns this:

({'sumy': Decimal('123.4')})

My Questions now are:

Does every connector, the

MySQL Connector/Python
PyMySQL
MySQLDB
mysqlclient
and the OurSQL

return a Decimal('123.4') Value from a SUM(column) query? How would I get rid of this Decimal-feature the easy way? I know there are CAST() and float() suggestions out there. I like to keep it simple so is there maybe a fetchone() parameter I am missing? Or a connection parameter like ['raw'] as the MySQL Connector/Python has(I'd have to rewrite my code, if I had to change to that...)

thank you.


Solution

  • Well, funny as it may seem, even the SUM of floats is returned as Decimal(123.4). I so went on doing all calculations in MySQL and using flask only to show the content.