sqlalchemy.exc.DataError: (psycopg2.DataError) integer out of range

Question

I am trying to save data into postgresql via SQLAchemy ORM. I encountered an error below:

sqlalchemy.exc.DataError: (psycopg2.DataError) integer out of range

I pinpointed the place where it goes wrong. I have a large number which is 2468432255.0. If I change to smaller number like 468432255.0, then it works.

The thing confused me is that: I defined the column as volume = Column(Numeric). As far as I understand, Numeric should be able to handle this large number. Additionally, I tried other data type like BigInt etc... they all gave me the same error.

Any idea?

Thanks, Chengjun

Answer 1

You can define anything you want in the SQlAlchemy schema in your local code, It doesn't mean it will be honored by the DB you're inserting the data into.

The schema that is defined in SQLAlchemy is being enforced by the code itself.

While the DB has its own schema that is also enforcing it when you're trying to insert/delete etc .. ( has its own constraints etc .. ) that SQLAlchemy knows nothing about ( until you declare it ).

In my opinion you can simply generate the SQLAlchemy schema automatically - and it will take the DB column name & types from the DB schema.

from sqlalchemy import create_engine

class SomeTable(Base):
    """
    Class, that represents SomeTable
    """
    __tablename__ = "my_table_in_db"

    __table__ = Table(__tablename__, Base.metadata,
        autoload=True,
        autoload_with=create_engine(DB_URL))

And after you'll create a SomeTable object, you can access its columns simply by

SomeTable.colname

colname is a column that currently exists in the DB