Python - MYSQL - Select leading zeros

Question

When using Python and doing a Select statement to MYSQL to select 09 from a column the zero gets dropped and only the 9 gets printed.

Is there any way to pull all of the number i.e. including the leading zero?

Answer 1

There's almost certainly something in either your query, your table definition, or an ORM you're using that thinks the column is numeric and is converting the results to integers. You'll have to define the column as a string (everywhere!) if you want to preserve leading zeroes.

Edit: ZEROFILL on the server isn't going to cut it. Python treats integer columns as Python integers, and those don't have leading zeroes, period. You'll either have to change the column type to VARCHAR, use something like "%02d" % val in Python, or put a CAST(my_column AS VARCHAR) in the query.

Answer 2

MySQL supports this in the column definition:

CREATE TABLE MyTable (
  i TINYINT(2) ZEROFILL
);

INSERT INTO MyTable (i) VALUES (9);

SELECT * FROM MyTable;

+------+
| i    |
+------+
|   09 | 
+------+

Answer 3

Best option will be using CAST as suggested by Eevee but you have to use CAST ( ur_field AS CHAR(2) ) as casting to varchar has this problem as given in this post

How to convert from varbinary to char/varchar in mysql

Answer 4

What data type is the column? If you want to preserve leading zeros, you should probably use a non-numeric column type such as VARCHAR.

To format numbers with leading zeros in Python, use a format specifier:

>>> print "%02d" % (1,)
01

Answer 5

You could run each query on that column through a function such as:

if len(str(number)) == 1: number = "0" + str(number)

This would cast the number to a string but as far as I know, Python doesn't allow leading zeroes in its number datatypes. Someone correct me if I'm wrong.

Edit: John Millikin's answer does the same thing as this but is more efficient. Thanks for teaching me about format specifiers!