sqlalchemy snippets

SQLAlchemy example

Tagged sqlalchemy, python  Languages python

SQLAlchemy example:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine("mysql://username:[email protected]/database_name")
engine.echo = False

Base = declarative_base()

class Entry(Base):
    __tablename__ = "entries"
    id = Column(Integer, primary_key=True)
    title = Column(String(255))
    url = Column(String)

    def __repr__(self):
        return "<Entry('%s', '%s')>" % (self.title, self.url)

# Set up handles
entry_table = Entry.__table__
metadata = Base.metadata
metadata.create_all(engine)

# Start a session
Session = sessionmaker(bind=engine)
session = Session()
 
# Query entries
entries = session.query(Entry) \
    .filter(Entry.title != 'Zermatt')

# Print all entries
for entry in entries.all():
    print entry.update_named_entities()

# Print first entry
entry = entries.first()

# Update entry
entry.title = 'Zermatt, Verbier'

# Commit changes
session.commit()

How to use PostgreSQL advisory locks with SQLAlchemy and Python

Tagged python, advisory lock, pg_try_advisory_lock, sqlalchemy  Languages python

advisory_lock.py

from models import Session
from sqlalchemy import func, select


def execute(session, lock_fn, lock_id, scope):
    """
    Executes the lock function
    """
    return session.execute(select([lock_fn(lock_id, scope)])).scalar()


def obtain_lock(session, lock_id, scope):
    """
    Obtains the advisory lock
    """
    lock_fn = func.pg_try_advisory_lock
    return execute(session, lock_fn, lock_id, scope)


def release_lock(session, lock_id, scope):
    """
    Releases the advisory lock
    """
    lock_fn = func.pg_advisory_unlock
    return execute(session, lock_fn, lock_id, scope)


def with_lock(my_func, lock_id, scope=1):
    """
    Executes my_func if the lock can be obtained.
    """
    session = Session()
    obtained_lock = False
    try:
        obtained_lock = obtain_lock(session, lock_id, scope)
        if obtained_lock:
            my_func()
    finally:
        if obtained_lock:
            release_lock(session, lock_id, scope)

Usage:

from advisory_lock import with_lock

def run():
    print("It runs")

if __name__ == '__main__':
    with_lock(run, 300000)

SQLAlchemy's yield_for with raw SQL and models

Tagged raw sql, sqlalchemy, yield_for  Languages python
from sqlalchemy import text

session = ...
q = session.query(Report).from_statement(text("""
SELECT
    id
FROM
    reports
WHERE
    id = '51812'
ORDER BY
    id DESC;
""")).yield_per(100)