How to add my password to my pyodbc connection in python-CodePudding

I have the following code to connect to my SQL Server database. I am wondering where/how I can add my password so it automatically connects instead of asking for my password.

server = 'myserver'
database = 'mydatabase'
username ='[email protected]'
Authentication='ADI'
driver= '{ODBC Driver 17 for SQL Server}'
conn = pyodbc.connect('DRIVER=' driver 
                    ';SERVER=' server 
                    ';PORT=1433;DATABASE=' database 
                    ';UID=' username 
                    ';AUTHENTICATION=' Authentication
                    )

I tried this but it did not work.

server = 'myserver'
database = 'mydatabase'
username ='[email protected]'
Authentication='ADI'
driver= '{ODBC Driver 17 for SQL Server}'
conn = pyodbc.connect('DRIVER=' driver 
                    ';SERVER=' server 
                    ';PORT=1433;DATABASE=' database 
                    ';UID=' username 
                    ';AUTHENTICATION=' Authentication
                    ';PWD= 'MyPassword'
                    )

Secondarily, is there another way to have it read my password without putting it in the code itself? If so, I would love any information on that.

CodePudding user response：

I'm not 100% on pyodbc but this works for mysqldb & psycopg2.

If you are able to use the Environment Variables, you can store information in there and call it using os in your script. Once you have it stored, you can just change your code to something like this below:

import os

server = 'myserver'
database = 'mydatabase'
username ='[email protected]'
Authentication='ADI'
driver= '{ODBC Driver 17 for SQL Server}'
conn = pyodbc.connect(DRIVER=driver,
                    SERVER=server,
                    PORT=1433,
                    DATABASE=database,
                    UID=username,
                    AUTHENTICATION=Authentication,
                    PWD= os.environ['MYPASSWORD']
                    )

CodePudding user response：

This is what ended up working for me:

#def get_connection():
conn = pyodbc.connect(DRIVER= '{ODBC Driver 17 for SQL Server}',
                    SERVER='ServerName',
                    DATABASE = 'DatabaseName',
                    PORT=PortNumber,
                    Trusted_Connection = 'Yes',
                    UID = 'MyUserName',
                    PWD = 'MyPass',
                    Authentication = 'ActiveDirectoryPassword'
                    )