Using sqlalchemy to generate: SELECT * ... INTO OUTFILE "file";

Question

I have recently started using SQLALCHEMY to query a my-sql database. I want to generate a select statement that uses the "INTO OUTFILE <file>" syntax to export query results to a test file. For example:

SELECT *
FROM table
INTO OUTFILE '/tmp/export.txt';

Is there a way to generate the "INTO OUTFILE..." clause using SQLALCHEMY?

If not, can I subclass one of the SQLALCHEMY classes so I can build that clause myself?

Thanks.

Answer 1

I did some thinking and poking around the examples on the SQLAlchemy site and figured it out. (Also posted to sql-alchemy user reciptes)

---

from sqlalchemy import *
from sqlalchemy.sql.expression import Executable, ClauseElement
from sqlalchemy.ext import compiler

class SelectIntoOutfile(Executable, ClauseElement):
    def __init__(self, select, file):
        self.select = select
        self.file = file


@compiler.compiles(SelectIntoOutfile)
def compile(element, compiler, **kw):
    return "%s INTO OUTFILE '%s'" % (
        compiler.process(element.select), element.file
    )


e = SelectIntoOutfile(select([s.dim_date_table]).where(s.dim_date_table.c.Year==2009), '/tmp/test.txt')
print e
eng.execute(e)