SQLAlchemy ordering by count on a many to many relationship

I haven’t used SQLAlchemy much so I figured I’d give it a shot. I didn’t try to use your models, I just wrote some new ones (similar enough though): likes = db.Table(‘likes’, db.Column(‘user_id’, db.Integer, db.ForeignKey(‘user.id’)), db.Column(‘post_id’, db.Integer, db.ForeignKey(‘post.id’)) ) class User(db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(20)) def __repr__(self): return “<User(‘%s’)>” % self.username class … Read more

How do I INSERT INTO t1 (SELECT * FROM t2) in SQLAlchemy?

SQLalchemy doesn’t build this construct for you. You can use the query from text. session.execute(‘INSERT INTO t1 (SELECT * FROM t2)’) EDIT: More than one year later, but now on sqlalchemy 0.6+ you can create it: from sqlalchemy.ext import compiler from sqlalchemy.sql.expression import Executable, ClauseElement class InsertFromSelect(Executable, ClauseElement): def __init__(self, table, select): self.table = table … Read more

How to build many-to-many relations using SQLAlchemy: a good example

From the comments I see you’ve found the answer. But the SQLAlchemy documentation is quite overwhelming for a ‘new user’ and I was struggling with the same question. So for future reference: ItemDetail = Table(‘ItemDetail’, Column(‘id’, Integer, primary_key=True), Column(‘itemId’, Integer, ForeignKey(‘Item.id’)), Column(‘detailId’, Integer, ForeignKey(‘Detail.id’)), Column(‘endDate’, Date)) class Item(Base): __tablename__ = ‘Item’ id = Column(Integer, primary_key=True) … Read more

Bulk update in SQLAlchemy Core using WHERE

Read the Updating and Deleting Rows with Core section of the tutorial. The following code should get you started: from sqlalchemy import bindparam stmt = addresses.update().\ where(addresses.c.id == bindparam(‘_id’)).\ values({ ‘user_id’: bindparam(‘user_id’), ’email_address’: bindparam(’email_address’), }) conn.execute(stmt, [ {‘user_id’: 1, ’email_address’ : ‘jack@yahoo.com’, ‘_id’:1}, {‘user_id’: 1, ’email_address’ : ‘jack@msn.com’, ‘_id’:2}, {‘user_id’: 2, ’email_address’ : ‘www@www.org’, ‘_id’:3}, … Read more

SQLAlchemy – Mapping self-referential relationship as one to many (declarative form)

You add a ForeignKey referencing the parent, and then create a relationship that specifies the direction via remote_side. This is documented under adjacency list relationships. For declarative you’d do something like this: class Tag(Base): __tablename__ = ‘tag’ id = Column(Integer, primary_key=True) label = Column(String) parent_id = Column(Integer, ForeignKey(‘tag.id’)) parent = relationship(‘Tag’, remote_side=[id]) If you want … Read more

SQLAlchemy – Mapping self-referential relationship as one to many (declarative form)

You add a ForeignKey referencing the parent, and then create a relationship that specifies the direction via remote_side. This is documented under adjacency list relationships. For declarative you’d do something like this: class Tag(Base): __tablename__ = ‘tag’ id = Column(Integer, primary_key=True) label = Column(String) parent_id = Column(Integer, ForeignKey(‘tag.id’)) parent = relationship(‘Tag’, remote_side=[id]) If you want … Read more

using sqlalchemy to load csv file into a database

In case your CSV is quite large, using INSERTS is very ineffective. You should use a bulk loading mechanisms, which differ from base to base. E.g. in PostgreSQL you should use “COPY FROM” method: with open(csv_file_path, ‘r’) as f: conn = create_engine(‘postgresql+psycopg2://…’).raw_connection() cursor = conn.cursor() cmd = ‘COPY tbl_name(col1, col2, col3) FROM STDIN WITH (FORMAT … Read more

How to execute “left outer join” in SqlAlchemy

q = session.query(Table1.field1, Table1.field2)\ .outerjoin(Table2)\ # use in case you have relationship defined # .outerjoin(Table2, Table1.id == Table2.table_id)\ # use if you do not have relationship defined .filter(Table2.tbl2_id == None) should do it, assuming that field1 and field2 are from Table1, and that you define a relationship: class Table2(Base): # … table1 = relationship(Table1, backref=”table2s”)

Getting first row from sqlalchemy

Use query.one() to get one, and exactly one result. In all other cases it will raise an exception you can handle: from sqlalchemy.orm.exc import NoResultFound from sqlalchemy.orm.exc import MultipleResultsFound try: user = session.query(User).one() except MultipleResultsFound, e: print e # Deal with it except NoResultFound, e: print e # Deal with that as well There’s also … Read more