Search code examples
pythonsnowflake-cloud-data-platformodbcpyodbc

Snowflake connectivity by pyodbc

I am trying to connect to snowflake in linux by pyodbc in python2.7. I have installed snowflake-odbc-2.25.8.x86_64.rpm on my RHEL7 box. I have made changes in odbc.ini file like this:

[testodbc2]  
Driver      = /usr/lib64/snowflake/odbc/lib/libSnowflake.so
Description =  
server      = <MY_ACCOUNT>.snowflakecomputing.com  
#PRIV_KEY_FILE_PWD = <MY_PASSWORD>  
AUTHENTICATOR = SNOWFLAKE_JWT  
SSL=on

Now if uncomment PRIV_KEY_FILE_PWD = <MY_PASSWORD> in my odbc.ini and execute following lines

conn_str_1 = "DSN={};UID={};PWD={};server={};schema={};role={};warehouse={};database={};PRIV_KEY_FILE={}".format(dsn_name, 
  user, password_str, "{}.snowflakecomputing.com".format(account), schema, role, warehouse, database, private_key_location)

 conn = pyodbc.connect(conn_str_1)

It works. But as soon as I comment PRIV_KEY_FILE_PWD = <MY_PASSWORD> in my odbc.ini and try to pass password from my code, like this:

conn_str_1 = "DSN={};UID={};PWD={};server={};schema={};role={};warehouse={};database={};PRIV_KEY_FILE={};PRIV_KEY_FILE_PWD ={}".format(dsn_name, 
  user, password_str, "{}.snowflakecomputing.com".format(account), schema, role, warehouse, database, private_key_location, MY_PASSWORD)

 conn = pyodbc.connect(conn_str_1)

It starts failing with Error:

"Error: ('HY000', '[HY000] [Snowflake][Snowflake] (44) \n Error finalize setting: Marshaling private key failed.\n (44) (SQLDriverConnect)')"

MY_PASSWORD has ";" inside it. So I tried to escape it by
MY_PASSWORD = MY_PASSWORD .replace(';', '\\;') before passing in DSN string, still same error.

But what is surprising me that in odbc.ini - I m using password as it is and it is working but if I pass by code in dsn string - it is Not working.

Do you think - I am missing something here.

Solution

  • The reference says the following on the connection string argument.

    This information is formatted as key/value pairs separated by semi-colons (the values can be enclosed with curly braces if necessary)

    For details, see the ODBC reference.

    So, you should add curly braces like this.

    ...
conn = pyodbc.connect("...;PRIV_KEY_FILE_PWD={{{}}}".format(..., MY_PASSWORD))
// or 
conn = pyodbc.connect(f"...;PRIV_KEY_FILE_PWD={{{MY_PASSWORD}}}")