I’m working on a project that uses FastAPI alongside Pydantic and SQLAlchemy. I’m also using encode/databases to manage database connections. But for some weird reason, I get asyncpg.exceptions.DataError: invalid input for query argument $1: 217027642536 (value out of int32 range)
anytime I try saving to the database. Here’s what my code looks like:
database.py
... ... currencies = Table( 'currencies', metadata, Column('id', Integer, primary_key=True), Column('name', String(50)), Column('price', Float), Column('price_date', DateTime), Column('price_timestamp', DateTime), Column('market_cap', Integer) ) database = Database(DATABASE_URL)
database_manager.py
... ... async def add_currency(payload: Currency): query = currencies.insert().values(**payload.dict()) return await database.execute(query=query)
endpoints.py
... ... @endpoints.post('/', response_model=CurrencyOutput, status_code=201) async def add_currency(): data = check_currency_price() payload = Currency( name=data['name'], price=data['price'], price_date=data['price_date'], price_timestamp=data['price_timestamp'], market_cap=data['market_cap'] ) currency_id = await database_manager.add_currency(payload) response = { 'id': currency_id, **payload.dict() } return response
models.py
... ... class Currency(BaseModel): name: str price: float price_date: datetime price_timestamp: datetime market_cap: int class CurrencyOutput(Currency): id: int
services.py
def check_currency_price(): response = httpx.get( 'https://api.nomics.com/v1/currencies/ticker?' + 'key=somerandomAPIkey&ids=BTC&interval=1d,30d&convert=USD' ) return response.json()[0]
I can’t see anything wrong with this. Someone, please tell me what the hell is going on?
Advertisement
Answer
You are basically having Integer Overflow, Int32 represents 2^31 - 1
, that means it can store the values in range -2147483648 to 2147483648 but the value you are trying to insert is bigger than 2^31
2**31 > 217027642536 Out: False
So you need to use SQLAlchemy’s BigInteger type represents Int64 it also represents 2^63 - 1
which can store the values in range negative and positive 9,223,372,036,854,775,807
from sqlalchemy import BigInteger currencies = Table( 'currencies', metadata, Column('id', Integer, primary_key=True), Column('name', String(50)), Column('price', Float), Column('price_date', DateTime), Column('price_timestamp', DateTime), Column('market_cap', BigInteger) ) ^^^^^^^^^^
Changing market’s Column Type with the BigInteger should solve the problem, but be careful bigger types uses more memory.