Getting a warning when using a pyodbc Connection object with pandas

Question

I am trying to make sense of the following error that I started getting when I setup my python code to run on a VM server, which has 3.9.5 installed instead of 3.8.5 on my desktop. Not sure that matters, but it could be part of the reason.

The error

C:\ProgramData\Miniconda3\lib\site-packages\pandas\io\sql.py:758: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) or
database string URI or sqlite3 DBAPI2 connection
other DBAPI2 objects are not tested, please consider using SQLAlchemy
  warnings.warn(

This is within a fairly simple .py file that imports pyodbc & sqlalchemy fwiw. A fairly generic/simple version of sql calls that yields the warning is:

myserver_string = "xxxxxxxxx,nnnn"
db_string = "xxxxxx"

cnxn = "Driver={ODBC Driver 17 for SQL Server};Server=tcp:"+myserver_string+";Database="+db_string +";TrustServerCertificate=no;Connection Timeout=600;Authentication=ActiveDirectoryIntegrated;"

def readAnyTable(tablename, date):
    conn = pyodbc.connect(cnxn)
    
    query_result = pd.read_sql_query(
            ''' 
                 SELECT *
                 FROM [{0}].[dbo].[{1}]
                where Asof >= '{2}'
            '''.format(db_string,tablename,date,), conn)
            
    conn.close()
    
    return query_result

All the examples I have seen using pyodbc in python look fairly similar. Is pyodbc becoming deprecated? Is there a better way to achieve similar results without warning?

Answer 1

Is pyodbc becoming deprecated?

No. For at least the last couple of years pandas' documentation has clearly stated that it wants either

1. a SQLAlchemy Connectable (i.e., an Engine or Connection object),
2. a string containing a SQLAlchemy connection URL, or
3. a SQLite DBAPI connection.

(The switch-over to SQLAlchemy was almost universal, but they continued supporting SQLite connections for backwards compatibility.) People have been passing other DBAPI connections (like pyodbc Connection objects) for read operations and pandas hasn't complained … until now.

Is there a better way to achieve similar results without warning?

Yes. You can take your existing ODBC connection string and use it to create a SQLAlchemy Engine object as described in the SQLAlchemy 1.4 documentation:

from sqlalchemy.engine import URL
connection_string = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=dagger;DATABASE=test;UID=user;PWD=password"
connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})

from sqlalchemy import create_engine
engine = create_engine(connection_url)

Then use the SQLAlchemy engine to work with the pandas methods you require. For example, with SQLAlchemy 2.0 and pandas 1.5.3:

import pandas as pd
import sqlalchemy as sa

# …

with engine.begin() as conn:
    df = pd.read_sql_query(sa.text("SELECT 'thing' as txt"), conn)

Answer 2

It works for me.

import warnings

warnings.filterwarnings('ignore')

Answer 3

    import pandas as pd
    import pyodbc
    import sqlalchemy as sa
    import urllib
    from sqlalchemy import create_engine, event
    from sqlalchemy.engine.url import URL
    
    server = 'IP ADDRESS or Server Name' 
    database = 'AdventureWorks2014' 
    username = 'xxx' 
    password = 'xxx' 
    
    params = urllib.parse.quote_plus("DRIVER={SQL Server};"
                                     "SERVER="+server+";"
                                     "DATABASE="+database+";"
                                     "UID="+username+";"
                                     "PWD="+password+";")
    
    engine = sa.create_engine("mssql+pyodbc:///?odbc_connect={}".format(params))
    
    qry = "SELECT t.[group] as [Region],t.name as [Territory],C.[AccountNumber]"
    qry = qry + "FROM [Sales].[Customer] C INNER JOIN [Sales].SalesTerritory t on t.TerritoryID = c.TerritoryID "
    qry = qry + "where StoreID is not null and PersonID is not null"

with engine.connect() as con:
    rs = con.execute(qry)

    for row in rs:
        print (row)

You can use the SQL Server name or the IP address, but this requires a basic DNS listing. Most corporate servers should already have this listing though. You can check the server name or IP address using the nslookup command in the command prompt followed by the server name or IP address.

I'm using SQL 2017 on Ubuntu server running on VMWare. I'm connecting with IP Address here as part of a wider "running MSSQL on Ubuntu" project.

If you are connecting with your Windows credentials, you can replace the params with the trusted_connection parameter.

params = urllib.parse.quote_plus("DRIVER={SQL Server};"
                                 "SERVER="+server+";"
                                 "DATABASE="+database+";"
                                 "trusted_connection=yes")

Answer 4

since its a warning, I suppressed the message using the warnings python library. Hope this helps

import warnings
with warnings.catch_warnings(record=True):
    warnings.simplefilter("always")
    #your code goes here

Answer 5

My company doesn't use SQLAlchemy, preferring to use postgres connections based on pscycopg2 and incorporating other features. If you can run your script directly from a command line, then turning warnings off will solve the problem: start it with python3 -W ignore

Answer 6

The correct way to import for SQLAlchemy 1.4.36 is using:

import pandas as pd
from sqlalchemy import create_engine, event
from sqlalchemy.engine.url import URL
#...

conn_str = set_db_info()    # see above
conn_url = URL.create("mssql+pyodbc", query={"odbc_connect": conn_str})
engine = create_engine(conn_url)

df = pd.read_sql(SQL, engine)
df.head()