Which column type has support for lists in sqlalchemy?











up vote
1
down vote

favorite












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.










share|improve this question




















  • 1




    Sounds like you'd be better off with a separate table where you can map any number of region numbers to a given region. Have a look at this section of the docs: docs.sqlalchemy.org/en/latest/orm/…
    – SuperShoot
    Nov 13 at 11:13










  • Not really in this case (I think) because these numbers corresponds to a same data series which comprise an observation window. Therefore the properties I'm deriving and storing in other table will correspond to the regions given by both numbers.
    – Chicrala
    Nov 13 at 11:19






  • 1




    Generally speaking, I agree with @SuperShoot that you should probably use a separate table. However, there are occasionally legitimate reasons to break the rules. If you are using a PostgreSQL backend, there is an ARRAY type you can use.
    – benvc
    Nov 13 at 14:09








  • 1




    @Chicrala In some specific cases you can use array. Example: db.Column(postgresql.ARRAY(db.Integer()), )
    – Danila Ganchar
    Nov 14 at 14:28








  • 1




    @DanilaGanchar I'm using sqlite so I guess I would need to change for postgre. Anyway that answers the question I guess because it states which case arrays can be used. I think you should post it as an answer so I can give you the credits for it
    – Chicrala
    Nov 14 at 17:09















up vote
1
down vote

favorite












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.










share|improve this question




















  • 1




    Sounds like you'd be better off with a separate table where you can map any number of region numbers to a given region. Have a look at this section of the docs: docs.sqlalchemy.org/en/latest/orm/…
    – SuperShoot
    Nov 13 at 11:13










  • Not really in this case (I think) because these numbers corresponds to a same data series which comprise an observation window. Therefore the properties I'm deriving and storing in other table will correspond to the regions given by both numbers.
    – Chicrala
    Nov 13 at 11:19






  • 1




    Generally speaking, I agree with @SuperShoot that you should probably use a separate table. However, there are occasionally legitimate reasons to break the rules. If you are using a PostgreSQL backend, there is an ARRAY type you can use.
    – benvc
    Nov 13 at 14:09








  • 1




    @Chicrala In some specific cases you can use array. Example: db.Column(postgresql.ARRAY(db.Integer()), )
    – Danila Ganchar
    Nov 14 at 14:28








  • 1




    @DanilaGanchar I'm using sqlite so I guess I would need to change for postgre. Anyway that answers the question I guess because it states which case arrays can be used. I think you should post it as an answer so I can give you the credits for it
    – Chicrala
    Nov 14 at 17:09













up vote
1
down vote

favorite









up vote
1
down vote

favorite











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.










share|improve this 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.







python python-3.x list sqlalchemy






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 13 at 14:11









benvc

3,2921319




3,2921319










asked Nov 13 at 11:02









Chicrala

18510




18510








  • 1




    Sounds like you'd be better off with a separate table where you can map any number of region numbers to a given region. Have a look at this section of the docs: docs.sqlalchemy.org/en/latest/orm/…
    – SuperShoot
    Nov 13 at 11:13










  • Not really in this case (I think) because these numbers corresponds to a same data series which comprise an observation window. Therefore the properties I'm deriving and storing in other table will correspond to the regions given by both numbers.
    – Chicrala
    Nov 13 at 11:19






  • 1




    Generally speaking, I agree with @SuperShoot that you should probably use a separate table. However, there are occasionally legitimate reasons to break the rules. If you are using a PostgreSQL backend, there is an ARRAY type you can use.
    – benvc
    Nov 13 at 14:09








  • 1




    @Chicrala In some specific cases you can use array. Example: db.Column(postgresql.ARRAY(db.Integer()), )
    – Danila Ganchar
    Nov 14 at 14:28








  • 1




    @DanilaGanchar I'm using sqlite so I guess I would need to change for postgre. Anyway that answers the question I guess because it states which case arrays can be used. I think you should post it as an answer so I can give you the credits for it
    – Chicrala
    Nov 14 at 17:09














  • 1




    Sounds like you'd be better off with a separate table where you can map any number of region numbers to a given region. Have a look at this section of the docs: docs.sqlalchemy.org/en/latest/orm/…
    – SuperShoot
    Nov 13 at 11:13










  • Not really in this case (I think) because these numbers corresponds to a same data series which comprise an observation window. Therefore the properties I'm deriving and storing in other table will correspond to the regions given by both numbers.
    – Chicrala
    Nov 13 at 11:19






  • 1




    Generally speaking, I agree with @SuperShoot that you should probably use a separate table. However, there are occasionally legitimate reasons to break the rules. If you are using a PostgreSQL backend, there is an ARRAY type you can use.
    – benvc
    Nov 13 at 14:09








  • 1




    @Chicrala In some specific cases you can use array. Example: db.Column(postgresql.ARRAY(db.Integer()), )
    – Danila Ganchar
    Nov 14 at 14:28








  • 1




    @DanilaGanchar I'm using sqlite so I guess I would need to change for postgre. Anyway that answers the question I guess because it states which case arrays can be used. I think you should post it as an answer so I can give you the credits for it
    – Chicrala
    Nov 14 at 17:09








1




1




Sounds like you'd be better off with a separate table where you can map any number of region numbers to a given region. Have a look at this section of the docs: docs.sqlalchemy.org/en/latest/orm/…
– SuperShoot
Nov 13 at 11:13




Sounds like you'd be better off with a separate table where you can map any number of region numbers to a given region. Have a look at this section of the docs: docs.sqlalchemy.org/en/latest/orm/…
– SuperShoot
Nov 13 at 11:13












Not really in this case (I think) because these numbers corresponds to a same data series which comprise an observation window. Therefore the properties I'm deriving and storing in other table will correspond to the regions given by both numbers.
– Chicrala
Nov 13 at 11:19




Not really in this case (I think) because these numbers corresponds to a same data series which comprise an observation window. Therefore the properties I'm deriving and storing in other table will correspond to the regions given by both numbers.
– Chicrala
Nov 13 at 11:19




1




1




Generally speaking, I agree with @SuperShoot that you should probably use a separate table. However, there are occasionally legitimate reasons to break the rules. If you are using a PostgreSQL backend, there is an ARRAY type you can use.
– benvc
Nov 13 at 14:09






Generally speaking, I agree with @SuperShoot that you should probably use a separate table. However, there are occasionally legitimate reasons to break the rules. If you are using a PostgreSQL backend, there is an ARRAY type you can use.
– benvc
Nov 13 at 14:09






1




1




@Chicrala In some specific cases you can use array. Example: db.Column(postgresql.ARRAY(db.Integer()), )
– Danila Ganchar
Nov 14 at 14:28






@Chicrala In some specific cases you can use array. Example: db.Column(postgresql.ARRAY(db.Integer()), )
– Danila Ganchar
Nov 14 at 14:28






1




1




@DanilaGanchar I'm using sqlite so I guess I would need to change for postgre. Anyway that answers the question I guess because it states which case arrays can be used. I think you should post it as an answer so I can give you the credits for it
– Chicrala
Nov 14 at 17:09




@DanilaGanchar I'm using sqlite so I guess I would need to change for postgre. Anyway that answers the question I guess because it states which case arrays can be used. I think you should post it as an answer so I can give you the credits for it
– Chicrala
Nov 14 at 17:09












1 Answer
1






active

oldest

votes

















up vote
1
down vote



accepted










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.






share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














     

    draft saved


    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53279590%2fwhich-column-type-has-support-for-lists-in-sqlalchemy%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    1
    down vote



    accepted










    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.






    share|improve this answer

























      up vote
      1
      down vote



      accepted










      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.






      share|improve this answer























        up vote
        1
        down vote



        accepted







        up vote
        1
        down vote



        accepted






        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.






        share|improve this answer












        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.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 15 at 7:59









        Danila Ganchar

        3,67882145




        3,67882145






























             

            draft saved


            draft discarded



















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53279590%2fwhich-column-type-has-support-for-lists-in-sqlalchemy%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            mysqli_query(): Empty query in /home/lucindabrummitt/public_html/blog/wp-includes/wp-db.php on line 1924

            How to change which sound is reproduced for terminal bell?

            Can I use Tabulator js library in my java Spring + Thymeleaf project?