pythonsnowflake-cloud-data-platformsnowflake-tasksnowsql

Regular Expression not working as expected in Snoflake python connector

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.

Solution

  • 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}$');