Python - MYSQL - Select leading zeros

Question

Hi

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

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 3

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 4

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!