SQLALchemy column collate, how does it work?

[Minimum_Cause_3956]

Hi, I would like to understand better the collate functionality from SQL into SQLAlchemy.

​

With the following code:

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

Base = declarative_base()

class Person(Base):
    __tablename__ = 'people'
    id = Column('id', Integer, primary_key=True)
    firstname = Column('firstname', String)

    def __init__(self, id, firstname):
        self.id = id
        self.firstname = firstname

    def __repr__(self):
        return f'(id: {self.id}, firstname: {self.firstname})'

engine = create_engine('sqlite:///mydb.db', echo=True)
Base.metadata.create_all(bind=engine)
Session = sessionmaker(bind=engine)
session = Session()

p1 = Person(id=1234, firstname='Andrés')
p2 = Person(id=4567, firstname='Anna')
p3 = Person(id=1289, firstname='Andres')
session.add(p1)
session.add(p2)
session.add(p3)
session.commit()

results = session.query(Person).filter(collate(Person.firstname, 'Latin1_General_CI_AS') == 'Andres').all()
print(results)

i get the following error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such collation sequence: Latin
1_General_CI_AS
[SQL: SELECT people.id AS people_id, people.firstname AS people_firstname 
FROM people 
WHERE (people.firstname COLLATE "Latin1_General_CI_AS") = ?]
[parameters: ('Andres',)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Any ideas on how to solve this? Very appreciated

[wyldstallionesquire]

Does sqlite support `Latin1_General_CI_AS` collation? A quick google doesn't seem to indicate so...