How can I determine if one PGArray is included in another using SQLAlchemy sessions?

Question

I have an SqlAlchemy table like so:

table = sql.Table('treeItems', META,
    sql.Column('id', sql.Integer(), primary_key=True),
    sql.Column('type', sql.String, nullable=False),
    sql.Column('parentId', sql.Integer, sql.ForeignKey('treeItems.id')),
    sql.Column('lineage', PGArray(sql.Integer)),
    sql.Column('depth', sql.Integer),
)

Which is mapped to an object like so:

orm.mapper(TreeItem, TreeItem.table, polymorphic_on=TreeItem.table.c.type, polymorphic_identity='TreeItem')

I'd like to select any child node of a given node so what I'm looking for is SQL that looks like this (for a parent with pk=2):

SELECT *
FROM "treeItems"
WHERE ARRAY[2] <@ "treeItems".lineage AND "treeItems".id != 2
ORDER BY "treeItems".lineage

Here is the SqlAlchemy/Python code I use to try to get to the above SQL with little luck:

arrayStr = 'ARRAY[%s]' % ','.join([str(i) for i in self.lineage])
lineageFilter = expr.text('%s <@ %s' % (arrayStr, TreeItem.table.c.lineage))
query = SESSION.query(TreeItem).filter(expr.and_(lineageFilter, TreeItem.table.c.id!=self.id))

But here is the SQL I wind up with (notice the lack of quotes around the treeItems table name in the where clause):

SELECT "treeItems".id AS "treeItems_id", "treeItems".type AS "treeItems_type", "treeItems"."parentId" AS "treeItems_parentId", "treeItems".lineage AS "treeItems_lineage", "treeItems".depth AS "treeItems_depth"
FROM "treeItems"
WHERE ARRAY[2] <@ treeItems.lineage AND "treeItems".id != %(id_1)s

So, now for the questions:

Is there a better way to do this than to use the text() expression / Is there an operator or expression in SqlAlchemy that can do <@ with PGArray?

How can I get the quotes to show up around my table name if I must use the text() expression?

Thanks everyone!

Answer 1

SQLAlchemy's clause elements have an .op() method for custom operators. What isn't available is a special clause for array literals. You can specify the array literal with literal_column:

print sql.literal_column('ARRAY[2]').op('<@')(table.c.lineage)
# ARRAY[2] <@ "treeItems".lineage

If you want a better API for array literals, then you can create it with the sqlalchemy.ext.compiler module added in SQLAlchemy 0.5.4.

Answer 2

In this particular case I noticed that the quoting in the SQL was due to the fact I was using a table name that was mixed case. Converting the table name from 'treeItems' to 'tree_items' resolved the quoting issue and I was able to get my text expression to work:

expr.text('%s <@ %s' % (arrayStr, TreeItem.table.c.lineage))

It is a fix and it is nice to know that mixed case table names need to be quoted but Ants' answer remains the proper way to address the issue.