Is there a way to globally set DateTime string formatting?
Jason Baker
it into a table. For simplicity's sake, let's say I have a table like
this:
CREATE TABLE some_table(
id INT PRIMARY KEY,
insert_date DATETIME
);
When I get the input data, it would look something like this:
{id: 1,
insert_date: "2008 10 29"}
The code to insert this would look something like this (obviously
simplified - data is the above JSON structure encoded into a python
dictionary):
metadata.reflect()
table = metadata.tables[table_name]
stmt = table.insert(data)
session.execute(stmt)
In this case, insert_date would seem to be going into the database
like this: "0000-00-00 00:00:00". Is there a way I can tell the
compiler how to compile this? Ideally, I'd like to be able to do
something like this at the start of my program:
DATETIME.format_str = "yyyy mm dd"
...or better yet, have a way to use python-dateutil[1] to parse the
datetime string. Any advice?
[1] http://labix.org/python-dateutil
Michael Bayer
Most DBAPIs accept Python datetime objects directly, basically other than SQLite. SQLAlchemy doesn't do any rendering or parsing of any kind for these. For SQLite, it still accepts only Python datetime objects, it just renders them into a string and then parses back to DateTime on the result side.
If you have a stringified date format that you'd like to pass to SQL statements as values, such that the string is parsed into a datetime then passed to the DBAPI, just use TypeDecorator around DateTime: http://www.sqlalchemy.org/docs/core/types.html?highlight=typedecorator#sqlalchemy.types.TypeDecorator
Jason Baker
> If you have a stringified date format that you'd like to pass to SQL statements as values, such that the string is parsed into a datetime then passed to the DBAPI, just use TypeDecorator around DateTime:http://www.sqlalchemy.org/docs/core/types.html?highlight=typedecorato...
Yeah, but I can't make reflection use the TypeDecorator subclass
instead of DATETIME can I? I can think of two options:
1. Go through each reflected table and replace each DATETIME type
with a TypeDecorator subclass.
2. Find all of the DATETIME columns, and implement a preprocessing
step for all of them. So whenever I encounter a DATETIME value, I'd
convert it from a string into a Python datetime before passing it into
insert/update.
I don't necessarily like either of these options as I was hoping to
not have my code have to deal with these kinds of typing issues, but I
might be out of luck.
One other idea though: is there any way to use the compiler extension
to do this? For instance, could I add a @compiles function that
basically says "if this is a datetime column then do this else do
whatever is done by default for columns"? The type compilation
capability almost does what I want, except it's for DDL rather than
DML.
Michael Bayer
On Sep 27, 2010, at 2:22 PM, Jason Baker wrote:
> On Sep 24, 7:09 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> If you have a stringified date format that you'd like to pass to SQL statements as values, such that the string is parsed into a datetime then passed to the DBAPI, just use TypeDecorator around DateTime:http://www.sqlalchemy.org/docs/core/types.html?highlight=typedecorato...
>
> Yeah, but I can't make reflection use the TypeDecorator subclass
> instead of DATETIME can I? I can think of two options:
>
> 1. Go through each reflected table and replace each DATETIME type
> with a TypeDecorator subclass.
> 2. Find all of the DATETIME columns, and implement a preprocessing
> step for all of them. So whenever I encounter a DATETIME value, I'd
> convert it from a string into a Python datetime before passing it into
> insert/update.
>
> I don't necessarily like either of these options as I was hoping to
> not have my code have to deal with these kinds of typing issues, but I
> might be out of luck.
we'd like to add a feature at some point so that reflection is passed a map of types so that this can be customized. However its not public API we've formulated yet and its not something I want to tack on casually. So at the moment you'd need to populate engine.dialect.ischema_names, which is where the mapping of reflection name-> type is present.
>
> One other idea though: is there any way to use the compiler extension
> to do this? For instance, could I add a @compiles function that
> basically says "if this is a datetime column then do this else do
> whatever is done by default for columns"? The type compilation
> capability almost does what I want, except it's for DDL rather than
> DML.
@compiles is just going to change what rendered SQL looks like. I don't see a path at the moment for how this would help, at the very least you'd need to write a parsing function on the SQL side and then somehow get that to be rendered with your string argument, probably not what you're looking for.