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?

Gord Thompson · Accepted Answer · 2022-05-06T13:26:50.523

15

Is pyodbc becoming deprecated?

No. For at least the last couple of years pandas' documentation has clearly stated that it wants either a SQLAlchemy Connectable (i.e., an Engine or Connection object) or 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 pass engine to the pandas methods you need to use.

edited May 06 '22 at 13:26

answered Feb 11 '22 at 16:30

Gord Thompson

107,466
28
191
387

I tried and testing this and it works, thanks! – Josh Knechtel Feb 14 '22 at 14:32
1

I have a postgresql DB; am using psycopg2.pool.ThreadedConnectionPool and pandas is having issue with that – Nikhil VJ Apr 23 '22 at 13:23
1

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) – Montana Burr Apr 27 '22 at 17:15
@MontanaBurr - https://stackoverflow.com/a/60150030/2144390 – Gord Thompson Apr 27 '22 at 18:04
wrong or deprecated imports. The code snippet above does not work. – not2qubit May 04 '22 at 18:08
@not2qubit - The example is for SQLAlchemy 1.4 – Gord Thompson May 04 '22 at 19:01
@GordThompson I am using *SQLAlchemy* `1.4.36`, and the import above did not work. – not2qubit May 05 '22 at 07:31
@not2qubit - `from sqlalchemy.engine import URL` does work with SQLA 1.4.36. Example [here](https://pastebin.com/2msjyYRW) – Gord Thompson May 05 '22 at 13:52
@GordThompson You also need the `from sqlalchemy import create_engine, event` otherwise you get a `NameError: name 'create_engine' is not defined`. – not2qubit May 06 '22 at 07:43
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. – Jeff Bluemel May 11 '22 at 16:16

JonTout · Answer 2 · 2022-05-26T19:34:25.903

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

score 0 · Answer 3 · answered May 03 '22 at 22:27

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

not2qubit · Answer 4 · 2022-05-05T07:32:44.733

-1

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

edited May 05 '22 at 07:32

answered May 04 '22 at 18:14

not2qubit

11,992
7
83
112

@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! – not2qubit May 05 '22 at 07:31

Getting a warning when using a pyodbc Connection object with pandas

4 Answers4

Linked