102

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?

Improve this question

7 Answers 7

Reset to default
103

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

I have a postgresql DB; am using psycopg2.pool.ThreadedConnectionPool and pandas is having issue with that
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)
@MontanaBurr - stackoverflow.com/a/60150030/2144390
@not2qubit - from sqlalchemy.engine import URL does work with SQLA 1.4.36. Example here
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.
|
19

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

import warnings

warnings.filterwarnings('ignore')
Improve this answer

7 Comments

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.
I think the question is not answered here but rather suppressing the warnings, which doesnt solve the issue. Linking this -> stackoverflow.com/a/71083448/3440795
This is extremely funny answer :d
Sorry, but why? Since the problem was a warning, I suppressed the message using the warnings.
@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.
|
17

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)
Improve this answer

Comments

7 
    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")
Improve this answer

2 Comments

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'
How do you close engine connection? I would like to use syntax without with.
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
Improve this answer

1 Comment

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.
3

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

Improve this answer

1 Comment

Then the process will be killed, just without a warning.
-4

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()
Improve this answer

1 Comment

@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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.