CODEWITHSUNDEEP
pythonmysqlsqlalchemy
Fi3n1k
Fi3n1k

Reputation: 891

Print different value that is stored in MySQL DB

In MySQL table with myISAM I have a integer value ex.011. When I query in Python it prints me value 11 removing 0 before number. It should print the exact value that is stored in DB ex. 011 instead of 11. Any help ?

Upvotes: 0

Views: 171

Answers (2)

Silas Ray
Silas Ray

Reputation: 26150

Your column is an int, so MySQLdb gives you an integer value back in the query result. However, I think you should be able to write a mySQLdb result set wrapper (or maybe find one someone else already wrote) that inspects the flags set on the columns of the result set and casts to a string appropriately.

Look at cursor.description and cursor.description_flags as well as PEP-249. I think (ie I have not actually tried it) something along the lines of the following should get you started:

def get_result_set_with_db_specified_formatting(cursor):
    integer_field_types = (MySQLdb.constants.FIELD_TYPE.TINY,
                           MySQLdb.constants.FIELD_TYPE.SHORT,
                           MySQLdb.constants.FIELD_TYPE.LONG,
                           MySQLdb.constants.FIELD_TYPE.LONGLONG,
                           MySQLdb.constants.FIELD_TYPE.INT24)
    rows = cursor.fetchall()
    for row in rows:
        for index, value in enumerate(row):
            value = str(value)
            if (cursor.description[index][1] in integer_field_types
                and cursor.description_flags[index] & MySQLdb.constants.FLAG.ZEROFILL):
                if len(value) < cursor.description[index][2]:
                    value = ('0' * (cursor.description[index][2] - len(value))) + value
            row[index] = value
    return rows

Upvotes: 1

Marboni
Marboni

Reputation: 2459

May be, simple zero filling is OK in this case?

>>> print str(11).zfill(3)
011

As I understood, it's additional part of number. If its length is not constant, you need to change data type in DB to VARCHAR.

Upvotes: 0

Related Questions