Write table from python to SNOWFLAKE database with specific ROLE

October 10, 2022

How can I change "ROLE". I need use pytohn code (write to table) as a ACCOUNTADMIN. But Im default as SYSADMIN. When I use role ACCOUNTADIM in "conn_string", its non function, Im still as SYSADMIN and doesnt see my target database. I mean I need modify "conn_string". I’m already a little desperate. Thank you for help.
My code:

account_identifier = 'my_snowflake_server'
user = 'user_name'
password = 'pass'
database_name = 'database_1'
schema_name = 'schema_1'
role = 'ACCOUNTADMIN'

conn_string = f"snowflake://{user}:{password}:{role}:@{account_identifier}/{database_name}/{schema_name}"
engine = create_engine(conn_string)

table_name = 'TABLE_1'

if_exists = 'append'

with engine.connect() as con:
        df.to_sql(name=table_name.lower(), con=engine, if_exists=if_exists, method=pd_writer, index=False)

Output:

Long error message
...
Failed to connect to DB: xxx.snowflakecomputing.com:443. Incorrect username or password was specified.

Edit 1:
Username and Pass are 100% correct.

>Solution :

There are 2 ways of creating the engine and specifying the ROLE as well:

Using directly a connection string:

from sqlalchemy import create_engine
engine = create_engine(
    'snowflake://testuser1:0123456@myorganization-myaccount/testdb/public?warehouse=testwh&role=myrole'
)

Using the URL:

engine = create_engine(URL(
    account = 'myorganization-myaccount',
    user = 'testuser1',
    password = '0123456',
    database = 'testdb',
    schema = 'public',
    warehouse = 'testwh',
    role='myrole',
))

You can read more about it here.