mysql int being passed to python as long
Question: I have a mysql database in which I have defined a table as:

    
    
    CREATE TABLE IF NOT EXISTS tblModel (
    model_id int NOT NULL AUTO_INCREMENT,
    model_file varchar(50) NOT NULL,
    model_name varchar(50) NOT NULL,
    model_descrip varchar(200) NOT NULL,
    target_index char(6) NOT NULL,
    training_days int NOT NULL,
    trading_days int NOT NULL,      
    PRIMARY KEY(model_id));
    

Oddly enough, I noticed that though the mysql documentation says ints are
really int(4), I see that the int fields actually got created as int(11). This
is a bit disturbing, as it seems my instructions were not followed, per the
documentation.

When I query this table into python with a mysqldb.database_connection.cursor,
I see the ints are coming through as longs - not surprisingly.

My question is thus threefold (in order of increasing importance to me):

1) Any idea why int got created as int(11)?

2) The int fields in this table will hold a sequence #, and day counts - the
space allocated to a long is nowhere near necessary. Should I care about the
wasted space?

3) Assuming I'm stuck with the int(11)s (other tables have foreign keys into
this and it's a bit of a pain to break and recreate all that), is there any
better solution than doing an explicit cast to int of the cur.fetchall() or
cur.fetchone() returns?


Answer:   1. That is the default display width since an int can be in the range of -2147483648 to 2147483647 so max display width is 10 integers and a sign. The storage size of an int is 4 bytes regardless of its value. Don't confuse "display width" with storage size since display width is only if the number has a 'zerofill' option so int(4) zerofill will display 0004 while int(6) zerofill will display 000004; and this is a minimum display width so int(4) zerofill using 123456 will still yield 123456 and not 1234. Check out <http://dev.mysql.com/doc/refman/5.5/en/numeric-type-attributes.html>

  2. The space may or may not be wasted and depends on the architecture and OS. In C, ints must be at least 16 bits and longs at least 32 bits, but may be larger. On a 64-bit system a long might actually be a long long and be 64 bits. Unless you are operating on a severely limited system, it's probably not worth the worry. This is only in memory since MySQL will store those ints as 4 bytes regardless of what Python does with them.

  3. As stated previously, the number in the () is just a display width and doesn't affect the underlying range of the number in that column although if you do something like int(4) zerofill on one table with a foreign key and int(6) zerofill on another, joins may act weird but I haven't tested it. Adding "zerofill" to a MySQL number makes that column automatically unsigned. You don't need to cast anything when reading those out of MySQL; ints fit in a long and Python stores both as C longs regardless.

