As discussed on IRC, for those reading the list, the difference here is between
SQLite's ability to embed native functions (and in turn Pysqlite's ability to
use Python functions in this way), and other databases that don't run as
embedded libraries. The functions need to be expressed as SQL for all
databases other than SQLite. @compiles only deals with SQL rendering, not
data processing.
On Jun 1, 2011, at 2:15 PM, Tom Kralidis wrote:
> Hi,
>
> I'm using SQLAlchemy 0.6.6 for a web application. At this point, the
> app uses SQLite3 as the underlying database; I'm currently working on
> abstracting this out to work with others (PostgreSQL, MySQL).
>
> At a high level, the application receives CGI queries which are parsed
> and sent to SQLAlchemy to query and filter. Much of the filtering is
> done against XML data (say dataset.xml) stored in a column (type text)
> in SQLite.
>
> We use SQLite's create_function approach to pass the XML filter
> queries (basically lxml.etree xpath function wrapper) via (snippet):
>
> self.session = create_session(engine)
> self.connection = engine.raw_connection()
> self.connection.create_function('query_xpath', 2,
> util.query_xpath)
>
> Sample use case: a user provides a given XPath in their query which is
> passed to query_xpath(dataset.xml, '/some/xpath') and returns either
> None or the value of the XPath as a string, which would end up being
> the following in SQL:
>
> "select * from records where query_xpath(xml, '/some/xpath') = 'foo'";
>
> This has served us very well so far.
>
> Quite simply, we're looking for a similar way to 'register' Python
> functions so that they can be used in the same manner as above, with
> any database.
>
> http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=func#sqlalchemy.sql.expression.func
> seems to suggest that custom Python functions can be called in the way
> that we are looking for (i.e. dot separated packages). Testing this
> out like:
>
> def foo()
> return 'bar'
>
> ...
>
> query = session.query(dataset.xml).filter(func.foo()=='bar')
>
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) function foo()
> does not exist
> LINE 3: WHERE foo() = E'bar'
> ^
> HINT: No function matches the given name and argument types. You
> might need to add explicit type casts.
> 'SELECT records.xml AS records_xml \nFROM records \nWHERE foo() = %
> (foo_1)s' {'foo_1': 'bar'}
>
> Does this approach only apply to db queries which are set in the
> backend db?
>
> Alternatively, I'm wondering whether
> http://www.sqlalchemy.org/docs/core/compiler.html#greatest-function
> serves as a better example to what I want to do. Testing this out
> like:
>
> class query_xpath(expression.FunctionElement):
> type = String()
> name = 'query_xpath'
>
> @compiles(query_xpath)
> def default_query_xpath(element, compiler, **kw):
> return compiler.visit_function(element)
>
> @compiles(query_xpath, 'sqlite')
> @compiles(query_xpath, 'mysql')
> @compiles(query_xpath, 'postgresql')
> def case_query_xpath(element, compiler, **kw):
> arg1, arg2 = list(element.clauses)
>
> from server import util
> return util.query_xpath(compiler.process(arg1),
> compiler.process(arg2))
>
> ...
>
> query = session.query(query_xpath(dataset.xml, xpath_string))
>
> print query
>
>
> ...which gives me an lxml.etree error saying the XML is not well-
> formed. Tracing through this, it looks like the column name is passed
> to the function verbatim, instead of being evaluated to the column
> value (I though compiler.process() would do this?)
>
> I hope I have explained this well enough for some suggestions and / or
> workarounds.
>
> Thanks
>
> ..Tom
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
