SQLAlchemy: how to perform regexp_replace on column values

Question

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 · Accepted Answer · 2014-03-19 15:47:49Z

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.

edited Mar 19, 2014 at 15:47

answered Mar 19, 2014 at 15:40

hello-klol

74510 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Solomon Ucko Over a year ago

What database engine did you use? func.regexp_replace doesn't seem to be available with SQLite.

hello-klol Over a year ago

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.

Solomon Ucko Over a year ago

It's probably just a Postgres specific thing.

chakalakka Over a year ago

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."

Collectives™ on Stack Overflow

SQLAlchemy: how to perform regexp_replace on column values

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related