Hiding Password While Using sqalchemy.create_engine in python/sql server-CodePudding

I am importing a table created in python to my companies sql server database and would like to hide my sql server credentials.

The code I currently have is this:

engine = sqlalchemy.create_engine(
    "mssql pyodbc://[email protected]:mypassword!@dsn"
    "?authentication=ActiveDirectoryPassword"
)

df.to_sql('df', con = engine, schema= 'dbo', if_exists='replace', index=False)

This currently works perfectly but I would like to hide my password as this code will live on our companies virtual machine so it can run automatically everyday. Any tips would be appreciated

I tried using keyring as such:


import keyring

creds = keyring.get_credential(service_name= "sqlupload", username = None)
username_var = creds.username
password_var = creds.password

and then replacing 'mypassword' with password_var


engine = sqlalchemy.create_engine(
    "mssql pyodbc://[email protected]:password_var@dsn"
    "?authentication=ActiveDirectoryPassword"
)

df.to_sql('df', con = engine, schema= 'dbo', if_exists='replace', index=False)

I get the following error:

Error validating credentials due to invalid username or password

I believe this is because "mssql pyodbc://[email protected]:password_var@dsn" is in quotes and password_var is not being read as a variable.

CodePudding user response：

You can do a string formatter in python.

password_var = "mypassword!"
print(f'mssql pyodbc://[email protected]:{password_var}@dsn')

Result: mssql pyodbc://[email protected]:mypassword!@dsn

Reference: https://docs.python.org/3/tutorial/inputoutput.html