Issue
I have Select access to a MS SQL database that I would like to extract data into a Pandas dataframe running inside a Jupyter notebook. For reasons out of my control, I have access to the database from a different user. How can I query the database from Jupyter while connected to my current user account?
Solution
This is how you can do this:
- Python needs to be installed for all users in computer.
- Install Visual C++ Build tools Microsoft C++ Build Tools - Visual Studio
- Create a directory for the virtualenv accesible by all users in the computer, for example:
mkdir C:\virtualenv
- Make a copy of the Command Prompt and paste in the virtualenv directory.
From: C:\Users\YOURUSERNAME\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\System Tools\Command Prompt
To: C:\virtualenv\Command Prompt
- Run Command Prompt as different user (press shift + right click) and login with YOURDOMAIN.
username: YOURUSERNAME@YOURDOMAIN or YOURDOMAIN\YOURUSERNAME password: your password
cd C:\virtualenv
- Create the virtualenv:
python -m venv .
- Connect to the virtualenv.
scripts\activate
- Install dependencies
pip install --upgrade jupyter pyodbc sqlalchemy pandas pip
- Create a Jupyter configuration file for the user.
jupyter notebook --generate-config
- Close the Command Prompt.
- Right click on the Command Prompt shortcut, then select Properties.
- Clear and leave empty the values from Start in and replace the Target values with:
Target: %windir%\system32\runas.exe /user:YOURUSERNAME@YOURDOMAIN “CMD /k "cd C:\virtualenv && Scripts\activate && jupyter notebook”"
**Optional: You may add /savecred (after/user) and it will remember the password. For security reasons you should not do this. - Double click the command prompt to run Jupyter.
Create a notebook to connect to your SQL database and download a table into a Pandas dataframe.
import os
import pyodbc
import sqlalchemy as db
import pandas as pd
windomain = os.environ["userdomain"]
if windomain == "YOURDOMAIN":
server = "YOURSERVER"
database = "YOUDATABASE"
driver = "{SQL Server}"
connect = "DRIVER="+driver+";SERVER="+
server+";DATABASE="+database+
";Trusted_Connection=yes"
engine = db.create_engine(
"mssql+pyodbc:///?odbc_connect={}".format(connect))
else:
print("Open as different user.")
df = pd.read_sql(
"""
SELECT TOP(100) *
FROM [YOURDATABASE]
""",
con=engine,
)
Answered By - iDevFS
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.