[sqlalchemy] Re: Adapt custom types to DBMS type? - Michael Bayer - com.googlegroups.sqlalchemy

2 messages in com.googlegroups.sqlalchemy[sqlalchemy] Re: Adapt custom types t...

From	Sent On	Attachments
Martin Bernreuther	27 Feb 2008 14:30
Michael Bayer	27 Feb 2008 16:28

Subject:	[sqlalchemy] Re: Adapt custom types to DBMS type?
From:	Michael Bayer (mike...@zzzcomputing.com)
Date:	02/27/2008 04:28:57 PM
List:	com.googlegroups.sqlalchemy

On Feb 27, 2008, at 5:31 PM, Martin Bernreuther wrote:

Another approach could be to use a custom type
(http://www.sqlalchemy.org/docs/04/types.html#types_custom ), like

class IPIntegerType(sqlalchemy.types.TypeEngine): "SQL Datatype to use for IP numbers (4 byte, unsigned)"

def __init__(self, driver=""): if driver=="mysql": self.col_spec="INTEGER UNSIGNED" # Sqlite does not support non- standard "UNSIGNED", like MySQL else: self.col_spec="INTEGER" # Works for Sqlite (using >4 Byte); with MySQL 4Bytes including sign bit is not enough

def get_col_spec(self): return self.col_spec

Again, there's a conditional statement and I'm not happy with the fact, that the information about which driver/DBMS is used has to be passed to the object through the constructor function parameter, like IPIntegerType("mysql"). Is the TypeEngine class capable to determine this information itself?

I didn't see any information about the support of "UNSIGNED" within the Connection dialect property. (http://www.sqlalchemy.org/docs/04/sqlalchemy_engine.html#docstrings_sqlalchemy.engine_Dialect ) Otherwise instead of the "driver", this information would be sufficient.

What are the experiences running a sqlalchemy application with various DBMS using specific data types? (Which is the common approach?)

this is exactly where you want to look into TypeDecorator. The examples for TypeDecorator have the "Decorated" type listed as a class- level variable - *but*, you can override this behavior to be per- dialect by overriding the load_dialect_impl() method. the sqlalchemy.types.Interval type is an example of this usage, although its load_dialect_impl() method is a little overly complex and it uses the older style of overriding binds/result sets....so it would look more like:

class IPInteger(TypeDecorator): impl = TypeEngine # placeholder

def load_dialect_impl(self, dialect): if isinstance(dialect, mysql.MySQLDialect): return mysql.MSInteger else: return dialect.type_descriptor(Integer)

def process_bind_param(self, value, dialect): return <convert IP to binary>

def process_result_value(self, value, dialect): return <convert binary to IP>