Hello Guys, Thanks to your both for your suggestion, they're of a great help. Michael, I see your point about this, seems there isn't going to be any way to create a truely portable version of the query, I'll just opt for a custom one for each build for now I think :-)
Conor, your suggestion solutions seem to work nicely, I've placed those into a query an upon my initial tests it appears to be returning the data that I would expect. Cheers guys, Rob On 27 Oct, 15:35, "Michael Bayer" <[email protected]> wrote: > Sir Rawlins wrote: > > > Hello Guys, > > > I've got a query which I'm currently running as literal SQL against a > > MySQL database. I'm looking to create a ported version of the query > > for SQLite but am totally new to that platform so am looking for a > > little help with my date/time functions, I'm hoping someone here will > > have a little more experience with SQLite and be able to help out. > > > In addition to making these changes to the literal SQL I'm wondering > > if the query can be rewritten in a more SQLAlchemy style using > > functions rather than literal SQL so that it is more portable in > > future? Or is that going to be tricky? > > > The query, or at least the WHERE clause can be found in this pastebin > > snippet. > > >http://pastebin.com/m24c39a4f > > > I appreciate any help you guys can offer to get me started. I > > understand I can get the current date within SQLite by running date > > ('now') however some of the more tricky modifiers for getting > > DayOfWeek and DayOfMonth are beyond my understanding at the moment. > > sqlite date functions are dramatically different and are documented here: > > http://sqlite.org/lang_datefunc.html > > To make a query that is "generic" across MySQL and SQLite3, using date > functions which are pretty much specific to each of those databases, I'd > construct each date function you need using a custom ClauseElement > subclass. Then I'd use the sqlalchemy.ext.compiler to generate output for > MySQL or SQLite as needed. > > Here's a timestamp() function I use that is agnostic across PG and SQLite: > > from sqlalchemy.sql.expression import ColumnElement, select > from sqlalchemy.types import TIMESTAMP > from sqlalchemy.ext import compiler > from sqlalchemy import create_engine > > class timestamp(ColumnElement): > type = TIMESTAMP() > > @compiler.compiles(timestamp, 'postgres') > def gen_timestamp(element, compiler, **kw): > return "timezone('utc', CURRENT_TIMESTAMP)" > > @compiler.compiles(timestamp) > def gen_timestamp(element, compiler, **kw): > return "datetime('now', 'localtime')" > > stmt = select([timestamp()]) > > print stmt.compile(bind=create_engine('sqlite://')) > print stmt.compile(bind=create_engine('postgres://')) > > > > > > > Thanks, > > > Rob --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
