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 mike...@zzzcomputing.com 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 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
-~--~~~~--~~--~--~---
