CODEWITHSUNDEEP
postgresqlflasksqlalchemy
kalombo
kalombo

Reputation: 869

How to use ARRAY of INET in flask-sqlalchemy?

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?

Upvotes: 1

Views: 2850

Answers (1)

Filipe Amaral
Filipe Amaral

Reputation: 1723

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'].

Upvotes: 2

Related Questions