Regular Expression not working as expected in Snoflake python connector

Question

I have written a query which will filter out all the phone_no that is not of pattern (nnn)-nnn-nnnnn.

select * 
from employee
where  not RLIKE(phone_no,'^\\(\\d{3}\\)-\\d{3}-\\d{4}$');

It is giving the result as expected when I am query directly in Snowflake Console. But when i am using snowflake-connector-python to run the same query using pythonit is not working as expected.

        query="""
                INSERT  into   DEMO_DB.PUBLIC.EMPLOYEE_INTER_ATIF
                select * , 'Phone_No::Invalid Number'
                from DEMO_DB.PUBLIC.employee
                where  NOT  RLIKE(phone_no,'^\\(\\d{3}\\)-\\d{3}-\\d{4}$');
        """
        cs.execute(query).

CS is the name of the cursor that I have made.

What is the issue here.

Try making the double slashes into single slashes, I am not sure you need to escape slashes in this context? — Lorna Mitchell, Aug 26 '20 at 12:12

score 3 · Accepted Answer · answered Aug 26 '20 at 18:34

The issue here is that Python is interpreting special characters (like backslashes) within your string literal (the text inside your triple-quotes) before the string is being presented to Snowflake. To instruct Python NOT to do this, prefix the opening triple-quote with r, as follows:

query=r"""
INSERT  into   DEMO_DB.PUBLIC.EMPLOYEE_INTER_ATIF
select * , 'Phone_No::Invalid Number'
from DEMO_DB.PUBLIC.employee
where  NOT  RLIKE(phone_no,'^\\(\\d{3}\\)-\\d{3}-\\d{4}$');
"""

You can easily see the difference if you write a trivial Python script like this and execute it:

query="""
INSERT  into   DEMO_DB.PUBLIC.EMPLOYEE_INTER_ATIF
select * , 'Phone_No::Invalid Number'
from DEMO_DB.PUBLIC.employee
where  NOT  RLIKE(phone_no,'^\\(\\d{3}\\)-\\d{3}-\\d{4}$');
"""
print(query)
query=r"""
INSERT  into   DEMO_DB.PUBLIC.EMPLOYEE_INTER_ATIF
select * , 'Phone_No::Invalid Number'
from DEMO_DB.PUBLIC.employee
where  NOT  RLIKE(phone_no,'^\\(\\d{3}\\)-\\d{3}-\\d{4}$');
"""
print(query)

The output is:

INSERT  into   DEMO_DB.PUBLIC.EMPLOYEE_INTER_ATIF
select * , 'Phone_No::Invalid Number'
from DEMO_DB.PUBLIC.employee
where  NOT  RLIKE(phone_no,'^\(\d{3}\)-\d{3}-\d{4}$');


INSERT  into   DEMO_DB.PUBLIC.EMPLOYEE_INTER_ATIF
select * , 'Phone_No::Invalid Number'
from DEMO_DB.PUBLIC.employee
where  NOT  RLIKE(phone_no,'^\\(\\d{3}\\)-\\d{3}-\\d{4}$');

Yeah ,I managed to fix the issue by printing ,I was able to see that it was eating up a slash , so i added one more slash and it started working — Atif, Aug 27 '20 at 08:01

Regular Expression not working as expected in Snoflake python connector

1 Answers1