Postgrsql Array of CIDR and return type as String

[Peter Hudec]

Hi,

I have found, that sqlalcheny should convert poctgresql ARRAY fri LIST.
This works fine with the TEXT.  See example in some other post

http://stackoverflow.com/questions/20699196/python-list-to-postgresql-array

(<type 'list'>, "['new', 'updated', 'values']")


If I change the column type to CIDR ARRAY I get


(<type 'str'>, "'{127.0.0.1/32,::1/128}'")

postgresl version: 9.4 /debian jessie/
python: 2.7.9
bottle (0.12.9)
bottle-pgsql (0.2)
bottle-sqlalchemy (0.4.3)
psycopg2 (2.6.1)
SQLAlchemy (1.0.11)


Please could anybody help me why the ARRAY of CIDR is not converted to the LIST?


  best regards
    Peter Hudec

[Mike Bayer]

typically the psycopg2 driver is responsible for recognizing the string
form of datatypes coming back from the server and converting them.
While SQLAlchemy's type objects are capable of doing the same thing, in
the case of Postgresql we tend to not get into it, because psycopg2
almost always has their own converter, or worse, they add one in at some
version which then conflicts with ours.

So in this case I'd gather psycopg2 isn't handling the convert, because
CIDR ARRAY is a little unusual.

you should be able to adapt the recipe at
http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#using-enum-with-array,
which is a similar workaround for ARRAY of ENUM. Here it is, works great:

import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import ARRAY, CIDR
import re

class ArrayOfCIDR(ARRAY):

def bind_expression(self, bindvalue):
return sa.cast(bindvalue, self)

def result_processor(self, dialect, coltype):
super_rp = super(ArrayOfCIDR, self).result_processor(
dialect, coltype)

def handle_raw_string(value):
inner = re.match(r"^{(.*)}$", value).group(1)
return inner.split(",") if inner else []

def process(value):
if value is None:
return None
return super_rp(handle_raw_string(value))
return process

from sqlalchemy import Table, Column, Integer, create_engine, MetaData,
select

metadata = MetaData()
t = Table(
'mydata', metadata,
Column('id', Integer, primary_key=True),
Column('data', ArrayOfCIDR(CIDR))

)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
metadata.drop_all(e)
metadata.create_all(e)

e.execute(t.insert(), data=['127.0.0.1/32', '::1/128'])

value = e.scalar(select([t.c.data]))
print value[0]
print value[1]






>
>
> best regards
> Peter Hudec
> |
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.