sqlalchemy Integer column size

Question

I saw somewhere that you can define a column size for Integer columns (e.g. Integer(20), Integer(10), etc.) but for some reason, it seems that sqlalchemy ignore those sizes in the create table query it produces through create_all():

class Job(Base):
    __tablename__ = "t_job"

    id = Column(Integer(20), Sequence('%s_id_seq' % __tablename__), primary_key=True, nullable=False)
    name = Column(String(30))
    company_id = Column(Integer(20), ForeignKey("t_company.id", ondelete="CASCADE"), nullable=False)

Produces the following query:

CREATE TABLE t_job (
        id INTEGER NOT NULL AUTO_INCREMENT,
        name VARCHAR(30),
        company_id INTEGER NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY(company_id) REFERENCES t_company (id) ON DELETE CASCADE
)

If that's not a proper way to do this, what is?

Also the size on integers in MySQL is *mostly* pointless. I'd just use Integer. See http://alexander.kirk.at/2007/08/24/what-does-size-in-intsize-of-mysql-mean/ — Prof. Falken, May 16 '13 at 07:48

score 8 · Answer 1 · edited Aug 15 '21 at 14:13

8

This functionality was deprecated in version 0.7.

If you are using MySQL, you can use the mysql.INTEGER datatype:

from sqlalchemy.dialects import mysql 

class Job(Base):
    __tablename__ = "t_job"

    id = Column(mysql.INTEGER(20), Sequence('%s_id_seq' % __tablename__), primary_key=True, nullable=False)
    name = Column(String(30))
    company_id = Column(Integer(20), ForeignKey("t_company.id", ondelete="CASCADE"), nullable=False)

edited Aug 15 '21 at 14:13

snakecharmerb

47,570
11
100
153

answered Apr 04 '13 at 07:21

Nathan Villaescusa

17,331
4
53
56

6

That does misses the point of using an ORM, doesn't it? (I need to support Oracle also) – Ofir Apr 04 '13 at 07:38
From what I have seen, when one uses the mysql.INTEGER type and connects to a non-mysql database the type reverts to the built in Integer type, so Oracle should still work. I don't think Oracle's INTEGER allows the size to be specified. – Nathan Villaescusa Apr 04 '13 at 08:02

Konstantin Smolyanin · Answer 2 · 2022-09-21T11:06:04.137

1

There are no arbitrary size integers but SQLAlchemy supports besides Integer also BigInteger and SmallInteger.

https://docs.sqlalchemy.org/en/14/core/type_basics.html#generic-types

edited Sep 21 '22 at 11:06

answered Aug 15 '21 at 13:51

Konstantin Smolyanin

17,579
12
56
56

sqlalchemy Integer column size

2 Answers2

Linked