Generating string primary keys with sequence in SQLAlchemy











up vote
3
down vote

favorite












I need improvement on solution to the following problem:




Generating random string keys in SQA is relatively simple, something
like:



request_id = Column(String, default=lambda: uuid.uuid4().hex, primary_key=True)


However, I need to to get request_id have format like
DIVISION_ABC_REQUEST_223 (this is because PK is also supposed to be
good for human consumption -- that key will be sent around in emails,
copied/pasted, etc, but it also should be usable for regular SQA/SQL
queries as a typical PK), with integer suffixes ideally following a
normal (ordinal) sequence.



(the backend DB is Postgres)




I found a solution, if a bit wasteful:



class WorkPackage(Base):
__tablename__ = 'work_package'
int_id = Column(Integer, primary_key=True)
wp_prefix = Column(Unicode, default=u'DIVISION_ABC_REQUEST_', primary_key=True)
data = Column(Unicode)

@hybrid_property
def wp_id(self):
return self.wp_prefix + str(self.int_id)

@wp_id.expression
def wp_id(cls):
return cls.wp_prefix.concat(cls.int_id)


wp_id.expression uses concat (a ColumnOperator that produces SQL concatenation operator ||).



It works in the sense of automatic PK creation in desired order and for querying by the wp_id attribute.



However, the silly aspect is that obviously there's a single column filled with the same prefix over and over.



The obvious problem here is that wp_prefix is a column for a single reason: so that I could use its concat method.



I would like to change it in such way that the wp_prefix column is unnecessary (e.g. it could be SQL concatenation of a string and int_id column).



I do not know how to construct SQLAlchemy Core expression that would achieve this without using column method.










share|improve this question


























    up vote
    3
    down vote

    favorite












    I need improvement on solution to the following problem:




    Generating random string keys in SQA is relatively simple, something
    like:



    request_id = Column(String, default=lambda: uuid.uuid4().hex, primary_key=True)


    However, I need to to get request_id have format like
    DIVISION_ABC_REQUEST_223 (this is because PK is also supposed to be
    good for human consumption -- that key will be sent around in emails,
    copied/pasted, etc, but it also should be usable for regular SQA/SQL
    queries as a typical PK), with integer suffixes ideally following a
    normal (ordinal) sequence.



    (the backend DB is Postgres)




    I found a solution, if a bit wasteful:



    class WorkPackage(Base):
    __tablename__ = 'work_package'
    int_id = Column(Integer, primary_key=True)
    wp_prefix = Column(Unicode, default=u'DIVISION_ABC_REQUEST_', primary_key=True)
    data = Column(Unicode)

    @hybrid_property
    def wp_id(self):
    return self.wp_prefix + str(self.int_id)

    @wp_id.expression
    def wp_id(cls):
    return cls.wp_prefix.concat(cls.int_id)


    wp_id.expression uses concat (a ColumnOperator that produces SQL concatenation operator ||).



    It works in the sense of automatic PK creation in desired order and for querying by the wp_id attribute.



    However, the silly aspect is that obviously there's a single column filled with the same prefix over and over.



    The obvious problem here is that wp_prefix is a column for a single reason: so that I could use its concat method.



    I would like to change it in such way that the wp_prefix column is unnecessary (e.g. it could be SQL concatenation of a string and int_id column).



    I do not know how to construct SQLAlchemy Core expression that would achieve this without using column method.










    share|improve this question
























      up vote
      3
      down vote

      favorite









      up vote
      3
      down vote

      favorite











      I need improvement on solution to the following problem:




      Generating random string keys in SQA is relatively simple, something
      like:



      request_id = Column(String, default=lambda: uuid.uuid4().hex, primary_key=True)


      However, I need to to get request_id have format like
      DIVISION_ABC_REQUEST_223 (this is because PK is also supposed to be
      good for human consumption -- that key will be sent around in emails,
      copied/pasted, etc, but it also should be usable for regular SQA/SQL
      queries as a typical PK), with integer suffixes ideally following a
      normal (ordinal) sequence.



      (the backend DB is Postgres)




      I found a solution, if a bit wasteful:



      class WorkPackage(Base):
      __tablename__ = 'work_package'
      int_id = Column(Integer, primary_key=True)
      wp_prefix = Column(Unicode, default=u'DIVISION_ABC_REQUEST_', primary_key=True)
      data = Column(Unicode)

      @hybrid_property
      def wp_id(self):
      return self.wp_prefix + str(self.int_id)

      @wp_id.expression
      def wp_id(cls):
      return cls.wp_prefix.concat(cls.int_id)


      wp_id.expression uses concat (a ColumnOperator that produces SQL concatenation operator ||).



      It works in the sense of automatic PK creation in desired order and for querying by the wp_id attribute.



      However, the silly aspect is that obviously there's a single column filled with the same prefix over and over.



      The obvious problem here is that wp_prefix is a column for a single reason: so that I could use its concat method.



      I would like to change it in such way that the wp_prefix column is unnecessary (e.g. it could be SQL concatenation of a string and int_id column).



      I do not know how to construct SQLAlchemy Core expression that would achieve this without using column method.










      share|improve this question













      I need improvement on solution to the following problem:




      Generating random string keys in SQA is relatively simple, something
      like:



      request_id = Column(String, default=lambda: uuid.uuid4().hex, primary_key=True)


      However, I need to to get request_id have format like
      DIVISION_ABC_REQUEST_223 (this is because PK is also supposed to be
      good for human consumption -- that key will be sent around in emails,
      copied/pasted, etc, but it also should be usable for regular SQA/SQL
      queries as a typical PK), with integer suffixes ideally following a
      normal (ordinal) sequence.



      (the backend DB is Postgres)




      I found a solution, if a bit wasteful:



      class WorkPackage(Base):
      __tablename__ = 'work_package'
      int_id = Column(Integer, primary_key=True)
      wp_prefix = Column(Unicode, default=u'DIVISION_ABC_REQUEST_', primary_key=True)
      data = Column(Unicode)

      @hybrid_property
      def wp_id(self):
      return self.wp_prefix + str(self.int_id)

      @wp_id.expression
      def wp_id(cls):
      return cls.wp_prefix.concat(cls.int_id)


      wp_id.expression uses concat (a ColumnOperator that produces SQL concatenation operator ||).



      It works in the sense of automatic PK creation in desired order and for querying by the wp_id attribute.



      However, the silly aspect is that obviously there's a single column filled with the same prefix over and over.



      The obvious problem here is that wp_prefix is a column for a single reason: so that I could use its concat method.



      I would like to change it in such way that the wp_prefix column is unnecessary (e.g. it could be SQL concatenation of a string and int_id column).



      I do not know how to construct SQLAlchemy Core expression that would achieve this without using column method.







      python sqlalchemy






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Jan 15 '16 at 16:52









      LetMeSOThat4U

      3771210




      3771210






















          1 Answer
          1






          active

          oldest

          votes

















          up vote
          0
          down vote













          literal_column("'DIVISION_ABC_REQUEST'").concat... (note the double quotes, since you want an SQL string expression).



          literal_column() essentially means "I hand you this string which is a valid SQL expression and can be used in any column expression-ish context".






          share|improve this answer





















            Your Answer





            StackExchange.ifUsing("editor", function () {
            return StackExchange.using("mathjaxEditing", function () {
            StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
            StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
            });
            });
            }, "mathjax-editing");

            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: "196"
            };
            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: false,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            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%2fcodereview.stackexchange.com%2fquestions%2f116905%2fgenerating-string-primary-keys-with-sequence-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
            0
            down vote













            literal_column("'DIVISION_ABC_REQUEST'").concat... (note the double quotes, since you want an SQL string expression).



            literal_column() essentially means "I hand you this string which is a valid SQL expression and can be used in any column expression-ish context".






            share|improve this answer

























              up vote
              0
              down vote













              literal_column("'DIVISION_ABC_REQUEST'").concat... (note the double quotes, since you want an SQL string expression).



              literal_column() essentially means "I hand you this string which is a valid SQL expression and can be used in any column expression-ish context".






              share|improve this answer























                up vote
                0
                down vote










                up vote
                0
                down vote









                literal_column("'DIVISION_ABC_REQUEST'").concat... (note the double quotes, since you want an SQL string expression).



                literal_column() essentially means "I hand you this string which is a valid SQL expression and can be used in any column expression-ish context".






                share|improve this answer












                literal_column("'DIVISION_ABC_REQUEST'").concat... (note the double quotes, since you want an SQL string expression).



                literal_column() essentially means "I hand you this string which is a valid SQL expression and can be used in any column expression-ish context".







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Jun 22 at 16:07









                dom0

                101




                101






























                     

                    draft saved


                    draft discarded



















































                     


                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f116905%2fgenerating-string-primary-keys-with-sequence-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

                    Morgemoulin

                    Scott Moir

                    Souastre