How to use ARRAY of INET in flask-sqlalchemy?

Question

I have a user model with array of ips field in my flask application. I want to use postgresql array of inet type:

from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy.dialects.postgresql import ARRAY, INET, Integer, Unicode, Column
db = SQLAlchemy()

class User(db.Model):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    login = Column(Unicode(15))
    password = Column(Unicode(34))
    name = Column(Unicode(100))
    permitted_ips = Column(ARRAY(INET))

But when i make query i get bad answer:

user = User.query.get(84)
print user.permitted_ips
#['{', '1', '7', '2', '.', '2', '0', '.', '2', '5', '.', '2', ',', '1', '7', '2', '.', '2', '0', '.', '2', '5', '.', '3', '}']

instead of ['172.20.25.2', '172.20.25.3']. Current version of sqlalchemy is 0.9.10. I tried the latest one but result was the same. Is it possible to fix that?

Filipe Amaral · Accepted Answer · 2016-05-14 15:16:13Z

2

I found that Arrays are not parsed automatically so you need to create a generic type caster with psycopg2 library.

# needed imports
from psycopg2 import STRING
from psycopg2.extensions import register_type, new_array_type

Registering the array type, it will be done one time.

# to see the oid of inet. As pointed out by @univerio the oid should never             
# change, so you don't need to fetch it every time your app starts.
tp = db.engine.execute("select typarray from pg_type where typname = 'inet'")
print(tp.fetchone())
# (1041,)

# registering the array type
register_type(new_array_type((1041,), 'INET[]', STRING))

Now you can fetch the array and it will be parsed properly.

# fetch data
ips = db.engine.execute("select permitted_ips from users where id = 1")
print(ips.fetchone()[0])
# ['172.20.25.2', '172.20.25.3'].

edited May 14, 2016 at 15:16

answered May 13, 2016 at 18:07

Filipe Amaral

1,7531 gold badge14 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

univerio Over a year ago

It is definitely cleaner to register the array type as opposed to ad hoc parsing the "wrong" string.

Filipe Amaral Over a year ago

You mean safer? I'm not an expert, can you explain please?

univerio Over a year ago

No, it's cleaner. If you have to do this ad hoc conversion everywhere you need to access user.permitted_ips, wouldn't it be nice to have it done automatically before you even set it on the object? Same reason why it doesn't just give you integers as strings and ask you to do int(user.id) every time.

Filipe Amaral Over a year ago

Thanks, now I got it. When I said that's cleaner its because I proposed to use SQL instead of ORM itself and I think that was messy... Anyway, maybe there's a way to do that with sqlalchemy, I will do my research, thaks again!

univerio Over a year ago

I see. The oid of the builtin types should not ever change, so you don't have to fetch that from PostgreSQL every time your app starts up.

|

Collectives™ on Stack Overflow

How to use ARRAY of INET in flask-sqlalchemy?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related