Which column type has support for lists in sqlalchemy?

Question

I'm currently setting up a database for solar active regions and one of the columns is supposed to get the region number which, for now, I have declared in the following way:

noaa_number = sql.Column(sql.Integer, nullable=True)

However, since a new number may be assigned as the region evolves, which column type would better support a list to keep all the numbers that a given region is given? So instead of having an entry like:

noaa_number = 12443

I could have my result stored as:

#a simple entry
noaa_number = [12443]
#or multiple results
noaa_number = [12444,12445]

Where these elements in the list would be integers.

I was checking the documentation and the best idea I had was to place this column as a string and parse all the numbers out of it. While that would work just fine I was wondering if there is a better and more appropriate way of doing so.

Answer 1

In some cases you can use array column. This is really not bad way to store very specific data. Example:

class Example(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    my_array = db.Column(db.ARRAY(db.Integer())

# You can easily find records:
# Example.my_array.contains([1, 2, 3]).all()
# You can use text items of array
# db.Column(db.ARRAY(db.Text())

Also you can use CompositeArray (sqlalchemy_utils) to use custom database types as array items. Example:

# let's imagine that we have some meta history
history = db.Column(
        CompositeArray(
            CompositeType(
                'history',
                [
                    db.Column('message', db.Text),
                ]
             )
        )
# example of history type:
CREATE TYPE history AS (
   message text
);

Note! Not sure about sqlite but with postgres should works fine.

Hope this helps.