MySQL - IP Address

[spaceman]

Hi,

I am trying to store an IP address in MySQL database using the following mapping:

class IP(Base):

__tablename__ = 'ip'

ip = Column(VARBINARY(16), nullable=False, index=True)

VARBINARY is the correct type for storing IP addresses according to:

https://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-aton

My question is how do I run the MySQL function inet6_aton on insertion
and inet6_ntoa on read using SQLAlchemy ORM?

I know I could do it with python, but I am assuming that it is faster
with MySQL.

I am running SQLAlchemy 1.2.18 and MariaDB 10.0.38.

Regards,
spaceman

[Simon King]

You would normally do this by creating a custom column type:

https://docs.sqlalchemy.org/en/latest/core/custom_types.html#types-sql-value-processing

Here's an untested example:

class IPAddress(TypeDecorator):
impl = VARBINARY(16)
def bind_expression(self, bindvalue):
return func.inet6_aton(bindvalue, type_=self)

def column_expression(self, col):
return func.inet6_ntoa(col, type_=self)

class IP(Base):
__tablename__ = 'ip'

ip = column(IPAddress(), nullable=False, index=True)



Depending on what else you want to do with this column, you may also
want to define a custom Comparator.

Hope that helps,

Simon

[spaceman]

Hi,

Simon King wrote:
> class IPAddress(TypeDecorator):
> impl = VARBINARY(16)
> def bind_expression(self, bindvalue):
> return func.inet6_aton(bindvalue, type_=self)
>
> def column_expression(self, col):
> return func.inet6_ntoa(col, type_=self)
>
> class IP(Base):
> __tablename__ = 'ip'
> ip = column(IPAddress(), nullable=False, index=True)

Thanks, that works.

Surprised I didn't find that in the docs.

Regards,
spaceman