CODEWITHSUNDEEP
pythonregexpostgresqlsqlalchemy
Bastien Ho
Bastien Ho

Reputation: 839

Filter REGEX in SQLalchemy

I have 2 tables users and users_address and I'm trying to get all users with an incomplete name or address.

Everything works well except the filter related to postal routing, where I search a pattern of :

(some digits)+(a space)+(some letters or spaces)

Here is my code:

    ua = aliased(UserAddress)

    # Main query, incomplete users or addresses
    query1 = DBSession.query(
        User.id,
        User.member_id,
        User.lastname,
        User.firstname,
        ua.supplemental_address_1,
        ua.supplemental_address_2,
        ua.supplemental_address_3,
        ua.street_address,
        ua.postal_routing,
        ua.country,
    ).distinct()

    query1 = query1.join(ua, User.addresses)

    query1 = query1.filter(
        sa.or_(
            User.lastname == u'',
            User.firstname == u'',
            sa.and_(
                ua.primary == True,
                sa.or_(
                    ua.country == u'',
                    sa.and_(
                        ua.supplemental_address_1 == u'',
                        ua.supplemental_address_2 == u'',
                        ua.supplemental_address_3 == u'',
                        ua.street_address == u'',
                    ),
                    ~ua.postal_routing.op('regexp')('\d*\ ?(\w\s)*'),
                )
            )
        )
    )

But I got this error :

ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "regexp" LINE 4: ...ress = '' OR NOT (user_addresses_1.postal_routing regexp '[[...

I think a made a mistake in the way to call the regex, and I'm pretty sure my regex itself is mis formated :(

Could anyone help me ?

Upvotes: 1

Views: 3948

Answers (1)

Bastien Ho
Bastien Ho

Reputation: 839

Ok, self answer.

as I see in Using regex in WHERE in Postgres, the correct REGEX call in Postgresql is ~ so, the correct syntax is:

- ~ua.postal_routing.op('regexp')('\d*\ ?(\w\s)*'),

+ ~ua.postal_routing.op('~')('^\d+ \w+$'),

By the way, I changed the pattern.

Maybe is there a better syntax, but this one is working for me.

Upvotes: 1

Related Questions