6

I am trying to do the equivalent of cast(regexp_replace(entry.page_title,'.*::: ','') AS INT) using SQLAlchemy.

I've seen that you can use hybrid properties to perform functions on ORM-mapped classes. I've tried the following but I can't really see how you can use the hybrid properties to do string replace. 

class Entry(object):

    def __init__(self, page_title):
        self.page_title = page_title

    @hybrid_property
    def original_brand_id(self):
        return self.page_title.partition(' ::: ')[-1]
        ###OR ALSO TRIED DOING:
        return re.sub(r'[.*::: ]','',self.page_title)

I know that the issue is that I'm wanting to treat Entry's page_title as a string when it's actually an InstrumentedAttribute. But I'm not clear on how to get the string value, to get this to do what I want.

Is it even possible?

hello-klol
  • 735
  • 10
  • 20

1 Answers1

7

Turns out there exists a func for this so I can do

@hybrid_property
def original_brand_id(self):
    return cast(func.regexp_replace(self.page_title, '.*::: ',''), Numeric)

I would just delete this question but it took me such a long time of Google searching to find that out I'm just going to leave this here in case anyone else needs it.

hello-klol
  • 735
  • 10
  • 20
  • What database engine did you use? `func.regexp_replace` doesn't seem to be available with SQLite. – Solomon Ucko Oct 28 '18 at 12:25
  • I was using SQLAlchemy with Postgres :) It's possible use of the library has changed as this was a few years ago now. Have a quick check of the documentation. – hello-klol Nov 13 '18 at 05:24
  • 1
    It's probably just a Postgres specific thing. – Solomon Ucko Nov 13 '18 at 11:24
  • 2
    SQLAlchemy will generate EVERY function that you call via func.arbitrary_function(). Whether it really exists in your SQL flavor is another thing. From the docs: "Note that any name not known to func generates the function name as is - there is no restriction on what SQL functions can be called, known or unknown to SQLAlchemy, built-in or user defined." – chakalakka Feb 06 '21 at 10:56