Windows: Connecting to ODBC datasource supported?

[pybokeh]

Hello, just found this github project and I am also new to SqlAlchemy. So I am wondering is there a way to connect to an ODBC datasource using ipython-sql? If so, what is an example connection string? I'm assuming I need to have pyodbc installed. Thanks.

[sstorie]

Yes, you can do this using the PyODBC package and the proper connection string. I am using python 3.5 so I needed to "manually" install the pyodbc package as described here: https://github.com/mkleehammer/pyodbc/issues/77#issuecomment-152696628.

Then obviously make sure you have an ODBC connection defined, and you can use this syntax:

## Load the ipython-sql extension
#
%load_ext sql

## Establish a connection with our database using the ODBC connection (trusted authentication)
#
%sql mssql+pyodbc://YOUR_DSN

Alternatively you can enter a username/password as described here: http://docs.sqlalchemy.org/en/latest/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pyodbc

[pybokeh]

@sstorie Thanks. Is there a connection string for IBM DB2? I looked at the sqlalchemy dialects page, but don't see DB2 listed.

[DieselAnalytics]

Hello sttorie,

I followed your steps. I did the following:

• Imported the pyodbc library
• loaded the sql magic using the following code: %load_ext sql
• Connected to my database using the following code: %sql mssql+pyodbc://

It appeared that I successfully connected to the database but when I tried to run a simple top 3 * select statement against one of the tables in the database to test I got the following error:

DBAPIError: (pyodbc.Error) ('HY000', '[HY000] [Microsoft][ODBC Driver 13 for SQL Server]Connection is busy with results for another command (0) (SQLExecDirectW)') [SQL: 'commit']

I see others having this same issue but I have yet to find an answer. Any help will be appreciated. I am using the 4.1.0 version of the Jupyter Notebook server, SQL Server 2016 Developer Edition, and the 3.0.10 version of pyodbc.

Thanks in advance!

Regards,

Ryan

[DieselAnalytics]

I am giving a presentation to my local PyData group about Jupyter Notebooks. One of the features that I want to cover is the sql magic and I will like to use a SQL Server 2016 database as my data source. Hopefully you guys can help me figure this thing out. Thanks.

[meet-bhagdev]

@DieselAnalytics Can you share your sample Python script? I installed pyodbc package from pip and I am able to run SQL queries just fine.

[DieselAnalytics]

@meet-bhagdev Here is my code:

%load_ext sql %sql mssql+pyodbc://

I was able to connect to the MS SQL Server database but I was not able to run any queries without getting the "DBAPIError" error. I could not resolve the problem so I decided to use a SQLite database instead and now I am not having any problems.

Were you successful at running SQL queries against a MS SQL Server 2016 database? If you were successful please share how you were able to do so. There are many people having the same issue.

Regards,

Ryan Wade

[dsakalley]

Make sure you add a DSN (Data Source Name) in your Data Sources for Windows with windows authentication, driver = ODBC Driver 13 for SQL Server (for supporting 2016) and then reference that DSN like %sql mssql+pyodbc://@DSN_NAME

[dewaldabrie]

This is what worked for me on Ubuntu 16.04 to connect to MS SQL Server:

System dependencies: sudo apt-get install unixodbc unixodbc-dev freetds-dev freetds-bin tdsodbc

Python dependencies pip install pyodbc

Configuration Add this to /etc/freetds/freetds.conf:

    [<server_name>]
            host = <host name or IP address>
            port = 1433
            tds version = 7.0

Test with:

$ tsql -S <server_name> -U <username> -P <password>

Add this to /etc/odbcinst.ini

    [FreeTDS]
    Description = TDS driver (Sybase/MS SQL)
    # Some installations may differ in the paths
    #Driver = /usr/lib/odbc/libtdsodbc.so
    #Setup = /usr/lib/odbc/libtdsS.so
    Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
    Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
    CPTimeout =
    CPReuse =
    FileUsage = 1    

Add this to /etc/odbc.ini

    [sqlserver]
    Driver = FreeTDS
    Description = ODBC connection via FreeTDS
    Trace = No
    Server = <host name or IP address>
    Port = 1433
    Database = <db name> 

Test with:

$ isql -v sqlserver <DBuser> <DBpass>

Try connecting with pyodbc:

import pyodbc

dsn = 'sqlserver'
user = ' ... '
password =  ' ...'
database = ' ... '

con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database)
cnxn = pyodbc.connect(con_string)
cnxn

Try connecting with sql extension:

%sql mssql+pyodbc://<username>:<password>@sqlserver