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)sWithin 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.ordersThe 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:", ordersAbove, 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:
query.filter(Customer.id==Order.customer_id)