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.
