SQLAlchemy-Akiban provides a SQLAlchemy dialect for Akiban, as well as a Core/ORM extension library allowing direct control of Akiban nested SELECT statements and result sets.
SQLAlchemy-Akiban depends on:
- Akiban for Python - this is an extension for the psycopg2 DBAPI, in order to provide nested result support.
- SQLAlchemy 0.8 - The dialect has been developed against SQLAlchemy 0.8, which has one small API change to support nested result sets. Less critically it also supports generation of a WHERE clause using an ORM relationship attribute (see the example in ORM->Explicit Nesting).
Connect format is similar to that of a regular Postgresql database:
from sqlalchemy import create_engine engine = create_engine("akiban+psycopg2://@localhost:15432/")
The Engine above will produce connections when the Engine.connect method is called.
The dialect introduces a new type called NestedResult, the value of which is a new SQLAlchemy ResultProxy representing a nested result:
with engine.begin() as conn: result = conn.execute( "SELECT customer.id, " "(SELECT order.id, order.data " "FROM order " "WHERE customer_id=customer.id) AS order " "FROM customer") for row in result: print "customer id:", row['id'] for order_row in row['order']: print "order id:", order_row['id'] print "order data:", order_row['data']
Currently, Akiban requires the GROUPING keyword on all foreign keys. The dialect adds this keyword when emitting DDL for foreign keys:
from sqlalchemy import MetaData, Table, Column, String, Integer, ForeignKey metadata = MetaData() customer = Table('customer', metadata, Column('id', Integer, primary_key=True), Column('name', String(20)), ) order = Table('order', metadata, Column('id', Integer, primary_key=True), Column('customer_id', Integer, ForeignKey('customer.id')), Column('order_info', String(20)), ) metadata.create_all(conn)
Will emit DDL like:
CREATE TABLE customer ( id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, name VARCHAR(20) NULL, PRIMARY KEY (id) ) CREATE TABLE "order" ( id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, customer_id INTEGER NULL, order_info VARCHAR(20) NULL, PRIMARY KEY (id), GROUPING FOREIGN KEY(customer_id) REFERENCES customer (id) )
Moving up a level, the dialect introduces a new Core construct nested which is an extension of SQLAlchemy's "scalar select" construct. This construct is a drop-in replacement for a standard select() subquery, and is a marker intercepted by the Akiban dialect indicating that column and typing information about a "nested result" should be carried over from statement to result set:
from sqlalchemy import select from sqlalchemy_akiban import nested sub_stmt = nested([order]).where(order.c.customer_id == customer.c.id).label('o') stmt = select([sub_stmt]).where(customer.c.id == 1) result = conn.execute(stmt)
The above will produce SQL like the following:
SELECT (SELECT "order".id, "order".customer_id, "order".order_info FROM "order" WHERE "order".customer_id = customer.id) AS o FROM customer WHERE customer.id = %(id_1)s
Within the result set, nested columns will be targetable not just by column name but also by column object, and any SQLAlchemy-side type converters in place will take effect for these columns:
for row in result: print "customer id:", row[customer.c.id] for order_row in row['order']: print "order id:", order_row[order.c.id] print "order data:", order_row[order.c.data]
SQLAlchemy-Akiban includes ORM extensions, importable from the sqlalchemy_akiban.orm package.
The orm.nestedload() and orm.nestedload_all() provide relationship eager loading making usage of an embedded nested result. These are used just like SQLAlchemy's own orm.joinedload() and orm.subqueryload() functions:
from sqlalchemy.orm import relationship, Session from sqlalchemy.ext.declarative import declarative_base from sqlalchemy_akiban import orm Base = declarative_base() class Customer(Base): __table__ = customer orders = relationship("Order") class Order(Base): __table__ = order sess = Session(engine) for customer in sess.query(Customer).options(orm.nestedload(Customer.orders)): print "customer:", customer.name print "orders:", customer.orders
The orm.orm_nested() function acts just like the core nested() construct, except that it is ORM-aware and accepts a Query object; it will invoke Query style loading, nested into the tuples returned by Query:
sess = Session() n = orm.orm_nested(sess.query(Order.id, Order).filter(Customer.orders)) q = sess.query(Customer, n).filter(Customer.id == 1) for customer, orders in q: print "customer:", customer.name print "orders:", orders
Above, we're taking advantage of a new convenience feature in SQLAlchemy 0.8, which is that we can pass the Customer.orders class-level attribute directly to Query.filter() in order to generate a correlated WHERE clause. Alternatively, we could just spell this out: