Connecting to a SQL server in Beaker Notebook?

Question

In iPython I am using the following code to connect to a SQL server with SQLAlchemy:

import sqlalchemy as sa
import urllib
import pandas as pd

connection_string = "DRIVER={SQL Server};SERVER=myserver;DATABASE=mydatabase;TRUSTEDCONNECTION=Yes"
connection_string = urllib.quote_plus(connection_string) 
connection_string = "mssql+pyodbc:///?odbc_connect=%s" % connection_string

engine = sa.create_engine(connection_string).connect()

I am switching over to Beaker notebook because I enjoy its features, but am unsure how to get the SQL cells to connect to the SQL database, as it does above. I know you are supposed to put driver information in the "Default Data Source" box in Language Manager, but if I try to put the connection_string in there it spits an error.

Suggestions?

Answer 1

1. Download the SQL Server jdbc driver
2. Open a new notebook, open the Language Manager, under the "Notebook" menu.
3. Select SQL in the language list
4. Enter the relevant jdbc strings in the appropriate boxes and the path to the jar file of the SQL driver.

If you are using docker, you need to mount a volume that is accessible from within the docker machine and you have to be sure that you are able to reach your DB server from within the docker machine.

See the SQL example file in the tutorial notebook for further reference and the image below for a sample configuration