PyAthena VS flask-sqlalchemy

For python there is PyAthena which is a nice little wrapper around boto3 that tries to adhere to the python DB API.

You should probably use a database for this, using something like sqlite a single file "database" is probably the quickest way to get started, I recommend you use it with Flask-SQLAlchemy which makes working with the db easy as pie.

Flask-SQLAlchemy: An extension that integrates SQLAlchemy with Flask. You can install it using pip install flask-sqlalchemy.

flask-sqlalchemy: ORM for database access

I've made several runs at stripping the essential paginating functionality of flask-sqlalchemy for use with things like lists. That's where I'd start.

You can either use an ORM, which might be sqlalchemy, possibly wrapped up as a flask plugin https://flask-sqlalchemy.palletsprojects.com/ or more directly: https://www.sqlalchemy.org/

You can learn more about this approach and see an example is this Flask-SQLAlchemy PR.

Flask SQL Alchemy

I used for this project Flask and OpenAI CLIP. For the vector search I used approximate nearest neighbors provided by spotify/annoy. I used Flask-SQLAlchemy with GeoAlchemy2 to query GPS coordinates. The embedding was done using UMAP using the CLIP image vectors.

This appears to be this: https://github.com/pallets/flask-sqlalchemy/issues/910

from sqlalchemy.engine.reflection import Inspector from sqlalchemy.schema import DropConstraint, DropTable, MetaData, Table from extensions import db # from dotmap import DotMap def try_add(object): """ Try to add the column 'object' to its table in the database and return it. """ try: db.session.add(object) db.session.commit() return object except Exception: db.session.rollback() raise def drop_all_cascade(): """(On a live db) drops all foreign key constraints before dropping all tables. Workaround for SQLAlchemy not doing DROP ## CASCADE for drop_all() (https://github.com/pallets/flask-sqlalchemy/issues/722) """ con = db.engine.connect() trans = con.begin() inspector = Inspector.from_engine(db.engine) # We need to re-create a minimal metadata with only the required things to # successfully emit drop constraints and tables commands for postgres # (based on the actual schema of the running instance) meta = MetaData() tables = [] all_fkeys = [] for table_name in inspector.get_table_names(): fkeys = [] for fkey in inspector.get_foreign_keys(table_name): if not fkey["name"]: continue fkeys.append(db.ForeignKeyConstraint((), (), name=fkey["name"])) tables.append(Table(table_name, meta, *fkeys)) all_fkeys.extend(fkeys) for fkey in all_fkeys: con.execute(DropConstraint(fkey)) for table in tables: con.execute(DropTable(table)) trans.commit()