Usage

Initialisation

Initialisation is very easy: the only thing you need to do is to create an SQLAlchemy engine, normally by providing a database URL to sqlalchemy.create_engine(), and tell pyramid_sqlalchemy about that using the pyramid_sqlalchemy.bind_connection() function.

from sqlalchemy import create_engine
from pyramid_sqlalchemy import bind_connection

engine = create_engine('sqlite://')
bind_connection(engine)

ORM classes

SQLAlchemy provides an incredible ORM to interact with databases. To use this with pyramid_sqlalchemy the only thing you need to remember is to use the pyramid_sqlalchemy.orm_base function to get the base classes for mapped classes.

from pyramid_sqlalchemy import orm_base
from sqlalchemy import Column Integer, String

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

Metadata

If you do want want to use the ORM you can also use SQLAlchemy core constructs using a provided metadata instance.

from pyramid_sqlalchemy import get_metadata
from sqlalchemy import Column, Table, Integer, String

user = Table('user', get_metadata(),
    Column('user_id', Integer, primary_key=True),
    Column('user_name', String(16), nullable=False),
    Column('email_address', String(60)),
    Column('password', String(20), nullable=False)
)

Performing a query

In order do a query you need to request the current session first, using the pyramid_sqlalchemy.get_sql_session() function:

from pyramid_sqlalchemy import get_sql_session

users = get_sql_session().query(User)
for user in users:
    print('Hello %s' % user.user_name)

As a slight convenience you can also get a query instance directly with the pyramid_sqlalchemy.get_sql_query() function:

from pyramid_sqlalchemy import get_sql_query

users = get_sql_query(User)
for user in users:
    print('Hello %s' % user.user_name)

get_sql_query will become more useful if you start using multiple connections, but that is an advanced topic.