pythonsqlitesqlalchemy

sql alchemy generates integer instead of int for sql lite

Python Sql Alchemy generates table with VARCHAR for sql lite instead of INTEGER so select for sql lite ordered by with alphabet number

Given City Table in Sql lite generated from sql alchemy:

class City(Base):
__tablename__ = "city"
id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
city_name: Mapped[str] = mapped_column(String, index=True)

It generates: enter image description here

Data:

15470   Paris
100567  Paris

Query:

select(City).where(City.city_name == city_name.upper()).order_by(City.id).limit(1)

SELECT city.id, city.city_name 
FROM city 
WHERE city.city_name = :city_name_1 ORDER BY city.id
LIMIT :param_1

returns city id with 100567 instead of 15470.

Do I need to provide example with sql insert as well?

Solution

  • In SQLite, if the id column is stored as TEXT instead of INTEGER, the ORDER BY will sort alphabetically, not numerically.

    That's why '100567' can come before '15470'.

    To fix it, you should cast id to an integer when ordering:

    SELECT id, city_name
FROM city
WHERE city_name = 'Paris'
ORDER BY CAST(id AS INTEGER)
LIMIT 1;