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 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 -~----------~----~----~----~------~----~------~--~---
