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?

Queer

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)

Start

I have a postgresql DB; am using psycopg2.pool.ThreadedConnectionPool and pandas is having issue with that – Grandioso

Any idea why the pandas team has chosen not to support DBAPI2 connections? It seems like a weird choice, given DBAPI2's wide use (I think it's the default way for Python programs to communicate with a database server) – Chiliad

@MontanaBurr - https://mcmap.net/q/246786/-why-are-pandas-and-geopandas-able-to-read-a-database-table-using-a-dbapi-psycopg2-connection-but-have-to-rely-on-sqlalchemy-to-write-one – Start

wrong or deprecated imports. The code snippet above does not work. – Trinatrinal

@Trinatrinal - The example is for SQLAlchemy 1.4 – Start

@GordThompson I am using SQLAlchemy 1.4.36, and the import above did not work. – Trinatrinal

@Trinatrinal - from sqlalchemy.engine import URL does work with SQLA 1.4.36. Example here – Start

@GordThompson You also need the from sqlalchemy import create_engine, event otherwise you get a NameError: name 'create_engine' is not defined. – Trinatrinal

I am already using a sqlalchemy engine to connect, and I am still getting this warning with a Postgres Db and psycopg2. I am not using URL.create, I will have to experiment with that and see if it resolves the warning. – Primitive

How can I retrieve the string for the URL.create function about the DB type given the connection_string?. Is there a documentation from SQLAlchemy? I couldn't find it so far. – Phenology

How weird is that? Instead of using the default way of working with databases Pandas wants me to add an extra dependency into the program. – Trommel

Answer 2

It works for me. Caution it will ignore all current and future warnings.

import warnings

warnings.filterwarnings('ignore')

Elva

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Maas

I think the question is not answered here but rather suppressing the warnings, which doesnt solve the issue. Linking this -> https://mcmap.net/q/240578/-getting-a-warning-when-using-a-pyodbc-connection-object-with-pandas – Iluminadailwain

This is extremely funny answer :d – Lavish

Sorry, but why? Since the problem was a warning, I suppressed the message using the warnings. – Elva

@Rafael, Why? For one this hasn't just "suppressed the message", it suppresses all warnings. This includes future warnings that could really be helpful. It would be better if this were at least mentioned in your answer. – Mona

@Mona Thanks for the answer, is there any way to suppress a specific warning? I hadn't realized it. My answer was actually funny. Something like this would solve the problem of future warnings? warnings.filterwarnings("ignore", message="pandas only support SQLAlchemy connectable(engine/connection)...") – Elva

I've not used the warnings library. I just skimmed the documentation. It looks like you could use the warnings.catchwarnings() context manager. docs.python.org/3/library/… In this case you'd wrap just the read_sql_query line. Kevin Owino's answer suggests this, where he has ,"your code goes here", for the OP's code that would be (parameters not shown) "query_result = pd.read_sql_query(...)". – Mona

Answer 3

Use the accepted answer's approach using create_engine() if you can, but it doesn't work for obscure database engines. Plan B is to pass in a connection and suppress the warning. You can suppress just the relevant warning rather than all warnings.

import pandas as pd 
import pyodbc
from warnings import filterwarnings

filterwarnings("ignore", category=UserWarning, message='.*pandas only supports SQLAlchemy connectable.*')

CONN_STR = "..."
SQL = "SELECT..."

with pyodbc.connect(CONN_STR) as conn:
    df = pd.read_sql_query(SQL, conn)

Equalitarian

Answer 4

    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")

Isosceles

getting an error: ImportError: dlopen(/opt/homebrew/lib/python3.10/site-packages/pyodbc.cpython-310-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle' – Bethesde

How do you close engine connection? I would like to use syntax without with. – Standard

Answer 5

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

Riggall

Nice. Except in this case, to suppress warnings, you need "ignore" instead of "always". If you choose to fix this, may I suggest you also show a specific example with the OP's call to read_sql_query. – Mona

Answer 6

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

Retuse

Then the process will be killed, just without a warning. – Psychotic

Answer 7

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()

Trinatrinal

@gord-thompson I am using SQLAlchemy 1.4.36, so I don't know why you edited my answer to say something different. The import you used above did not work! – Trinatrinal