Answered my own question:
Define a custom column type, as per
http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types
a combination of the documentation and some trial & error have given me
this:
class MyDuckType(sqlalchemy.types.TypeDecorator):
"""
SQLALchemy custom column type, designed to let sqlite handle the typing
using 'numeric affinity' which intelligently handles both numbers
and strings
"""
impl = sqlite.NUMERIC
def bind_processor(self, dialect):
#function for type coercion during db write
return None #ie pass value as-is, let sqlite do the typing
def result_processor(self, dialect, coltype):
#function for type coercion during db read
return None #ie pass value as sqlite has stored it, should be
ducktyped already
def process_bind_param(self, value, dialect):
#any changes to an individual value before store in DN
return value
def process_result_value(self, value, dialect):
#any changes to an individual value after retrieve from DB
return value
def copy(self):
#not quite sure what this is for
return MyDuckType()
The current sqlalchemy dialect type returns to_float in bind_processor,
which is why I was getting the errors before. i.m.v.v.h.o., this is a bug.
for my bonus points: manually setting column type to MyDuckType in my
metadata.reflect() code:
def get_database_tables(engine):
meta = MetaData()
meta.reflect(bind=engine)
tables = meta.raw_tables
for tbl in tables.values():
for col in tbl.c:
col.type = MyDuckType()
return tables
seems to work for me. Any suggestions / improvements? I've tentatively
filled out a ticket on the sqlalchemy bug tracker, not sure if that was a
little arrogant?
On Tue, Jun 15, 2010 at 4:22 PM, Michael Bayer <[email protected]>wrote:
>
> On Jun 15, 2010, at 7:14 AM, Harry Percival wrote:
>
> Not sure what the etiquette is re cross-posting to this list from
> stackoverflow? here's my question:
>
>
> http://stackoverflow.com/questions/3044518/how-do-i-take-advantage-of-sqlite-manifest-typing-type-affinity-using-sqlalchem
>
>
> AFAIK this would be a function of Pysqlite. You can make your own
> SQLAlchemy types sure but thats SQLalchemy doing it, not SQLite's feature.
> I've read that sqlite documentation page many times and I've never seen
> what it says actually occur, particularly regarding numerics. Its either
> inaccurate or Pysqlite squashes the behavior, or I just don't really
> understand it. There's no way to get numerics of arbitrary precision in
> and out of SQLite, for example - if you google around for "decimal" support
> you'll see that its not possible - floating point conversions always kick
> in.
>
>
>
>
>
>
>
> I like the idea of sqlite's manifest typing / type affinity:
>
> http://www.sqlite.org/datatype3.html
>
> Essentially, if I set a column's affinity as 'numeric', it will duck type
> integers or floats to store them as such, but still allow me to store
> strings if I want to. Seems to me this is the best 'default' type for a
> column when i'm not sure ahead of time of what data i want to store in it.
>
> so off i go:
>
> metadata = MetaData()
> new_table = Table(table_name, metadata )
>
>
> for col_name in column_headings:
> new_table.append_column(Column(col_name,
>
>
>
> sqlite.NUMERIC, #this should duck-type
> numbers but can handle strings as well
>
>
> primary_key=col_name in
> primary_key_columns))
>
>
> new_table.create(self.engine, checkfirst=False)
>
> but when i try and store some string values, eg "abc" in the table,
> sqlalchemy falls over:
>
> File "[...]\sqlalchemy\processors.py", line 79, in to_float
>
>
>
> return float(value)
> ValueError: invalid literal for float(): abc
>
>
> Boo, hiss. So, is there any way I can convince sqlalchemy to let sqlite do
> the typing? perhaps i can use a type from sqlalchemy.types instead of
> sqlachemy.dialects.sqlite?
>
> since the so post, i've done a little more digging. Am I going to have to
> write my own custom type as per
> http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types?
>
> hp
>
>
> --
> ------------------------------
> Harry J.W. Percival
> ------------------------------
> Italy Mobile: +39 389 095 8959
> UK Mobile: +44 (0) 78877 02511 (may be turned off)
> Skype: harry.percival
> Email: [email protected]
>
> --
> 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.
>
>
> --
> 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]<sqlalchemy%[email protected]>
> .
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
------------------------------
Harry J.W. Percival
------------------------------
Italy Mobile: +39 389 095 8959
UK Mobile: +44 (0) 78877 02511 (may be turned off)
Skype: harry.percival
Email: [email protected]
--
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.
