Integer(mysql) != Integer(postgres)

[Leonardo Rossi]

I found that a Integer column in mysql means long in python and for PostgreSQL means int in python. It means that to have a long in both I need to use BigInteger?

e.g.

from sqlalchemy import Column, Integer, Text, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()


class FuuTable(Base):

    __tablename__ = 'fuutable'
    id = Column(Integer(), primary_key=True, autoincrement=True)
    name = Column(Text(), nullable=False)

mysql_e = create_engine("mysql://root:@localhost/invenio", echo=True)
postgres_e = create_engine(
    "postgres://postgres:postgres@localhost/testdb5",
    echo=True)

# mysql
Base.metadata.drop_all(mysql_e)
Base.metadata.create_all(mysql_e)
Session = sessionmaker(bind=mysql_e)
mysql_session = Session()

mysql_fuu = FuuTable(name="bar")

mysql_session.add(mysql_fuu)
mysql_session.commit()

mysql_test = mysql_session.query(FuuTable).filter(
    FuuTable.id == mysql_fuu.id).first()

# postgres
Base.metadata.drop_all(postgres_e)
Base.metadata.create_all(postgres_e)
Session = sessionmaker(bind=postgres_e)
postgres_session = Session()

postgres_fuu = FuuTable(name="bar")

postgres_session.add(postgres_fuu)
postgres_session.commit()

postgres_test = postgres_session.query(FuuTable).filter(
    FuuTable.id == postgres_fuu.id).first()

# test if Integer(mysql) == Integer(postgres)
print "Integer(mysql) = ", type(postgres_test.id)
print "Integer(postgres) = ", type(mysql_test.id)
print type(postgres_test.id) == type(mysql_test.id)

[Mike Bayer]

this is a usage question and I'd appreciate it if you could keep these on the mailing list. The Integer type on SQLAlchemy renders INT or INTEGER on the database backend. That is the part that's important, and if you want to know the range of the type, the database documentation is what you should consult, and per http://www.postgresql.org/docs/9.1/static/datatype-numeric.html and https://dev.mysql.com/doc/refman/5.1/en/integer-types.html we can see the range of INTEGER on both of these backends is exactly the same and analogous to Python int, and there is also a BIGINT on both that is more analogous to Python long.

so the "long" you get back for MySQL here is only an artifact of the database driver, possibly to accommodate for a future Python 3 version where Python 3 has only 'int' that is basically 'long'.

if your your program is relying upon "type(x) is int" I'd advise replacing with isinstance(x, numbers.Integral), or use a TypeDecorator to convert all values to int. We don't do conversions ilke that on the SQLA side because they are an unnecessary performance burden for the vast majority of use cases.