I’ve been searching the web for a solution but couldn’t find any so here is what we came up with (there might be better ways but at least this is a start if someone else runs into this). Specify the dtype parameter in to_sql. We went from:df.to_sql(table_name, analytics_db) to df.to_sql(table_name, analytics_db, dtype={‘name_of_json_column_in_source_table’: sqlalchemy.types.JSON}) and it … Read more
This is an old post, but I ran into a problem with the actual database column names not matching the mapped attribute names on the instance. We ended up going with this: from sqlalchemy import inspect inst = inspect(model) attr_names = [c_attr.key for c_attr in inst.mapper.column_attrs] Hope that helps somebody with the same problem!
In the intervening couple of years, Alex Gaynor created https://github.com/alex/alchimia which may be a better central repository for doing integration with SQLAlchemy and Twisted.
You can apply a filter in Artist.albums.any(), which will generate a subquery: Artist.query.filter(Artist.albums.any(genre_id=genre.id)).all() Or you can use a join() on albums: Artist.query.join(Artist.albums).filter_by(genre_id=genre.id).all()
In a many to many relationship, the primaryjoin expression describes the join between the left table and the junction table, and the secondaryjoin describes the join between the junction table and the right table. In other words, the primaryjoin expression is saying, “find all rows in the followers table where follower_id is X”, the secondaryjoin … Read more
You can extend your models with some simple crud methods to achieve something similar to Django ORM / ActiveRecord: # SQLAlchemy db_session setup omitted … Model = declarative_base(name=”Model”) Model.query = db_session.query_property() class CRUD(): def save(self): if self.id == None: db_session.add(self) return db_session.commit() def destroy(self): db_session.delete(self) return db_session.commit() class User(Model, CRUD): __tablename__ = ‘users’ id = … Read more
One of the notable things about SQLAlchemy is that it makes tables first class objects. Thus the core API is really written around table objects, and the API therefore is essentially relational in nature. Thus at this level even if the API is OO, it is essentially reflecting RDBMS objects or functions such as Tables, … Read more
For a simple example of SQLAlchemy’s coalesce function, this may help: Handling null values in a SQLAlchemy query – equivalent of isnull, nullif or coalesce. Here are a couple of key lines of code from that post: from sqlalchemy.sql.functions import coalesce my_config = session.query(Config).order_by(coalesce(Config.last_processed_at, datetime.date.min)).first()
Just add another .options(…) with a new relationship path: q = ( session .query(A) .options(joinedload(A.b).joinedload(B.c)) .options(joinedload(A.b).joinedload(B.d)) )
Check out the documentation about the case statement here: http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.case