SoFunction
Updated on 2024-11-17

Pandas alert UserWarning:pandas only supports SQLAlchemy connectable handling

I. Error message

Importing data from a database into pandas using the old way of writing, will result in a warning message:

UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.

II. The old way of writing

The old way of writing it was.

import pymysql
import pandas as pd

db_host = 'localhost'
user = 'root'
passwd = '123456'
db = 'mytestdb'

conn = (host=db_host,
                       user=user,
                       passwd=passwd,
                       db=db,
                       charset='utf8')

sql = 'SELECT * FROM students'

pd.set_option('.ambiguous_as_wide', True)
pd.set_option('.east_asian_width', True)
df = pd.read_sql(sql, conn)
print(df)

()

III. New ways of writing

Following the prompts, SQLAlchemy is recommended and you need to install the SQLAlchemy library first:

pip install sqlalchemy

The new version of the pandas library uses the create_engine object created by the sqlalchemy library for the con parameter . Create the create_engine object (in a format similar to a URL address)

from sqlalchemy import create_engine
import pandas as pd

MYSQL_HOST = 'localhost'
MYSQL_PORT = '3306'
MYSQL_USER = 'root'
MYSQL_PASSWORD = '123456'
MYSQL_DB = 'mytestdb'

engine = create_engine('mysql+pymysql://%s:%s@%s:%s/%s?charset=utf8'
                           % (MYSQL_USER, MYSQL_PASSWORD, MYSQL_HOST, MYSQL_PORT, MYSQL_DB))

sql = 'SELECT * FROM students'

df = pd.read_sql(sql, engine)

pd.set_option('.ambiguous_as_wide', True)
pd.set_option('.east_asian_width', True)
print(df)

P.S. What other userwarning does pandas have?

When using Pandas, some UserWarning may be encountered.The following are some of the UserWarning that may occur:

  1. Data Validation extension is not supported and will be removed: This warning comes from openpyxl when reading Excel files with pandas. This warning is about some canonical extension that does not affect the reading of data 1.
  2. pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.: This warning appears when importing data from a database to pandas using the old way of writing. This warning is about using SQLAlchemy to connect to the database in Recommendation 2.
  3. Pandas doesn't allow columns to be created via a new attribute name: When inheriting a Pandas DataFrame, this warning 3 appears if columns are created via a new attribute name.

The following are code examples for each case: Data Validation extension is not supported and will be removed: This warning is usually related to the reading of Excel files, you can use the warnings module of the openpyxl library to ignore this warning, the example is as follows:

import openpyxl
import warnings
from  import DataValidationError

# Ignore DataValidationError warnings
("ignore", category=DataValidationError)# Reading Excel files using Pandas
df = pd.read_excel('your_excel_file.xlsx')

pandas only supports SQLAlchemy connectable (engine/connection): This warning recommends using SQLAlchemy to connect to the database. Sample code is shown below:

from sqlalchemy import create_engine

# Create a SQLAlchemy database connection
engine = create_engine('database_connection_string')# Import data from database to Pandas DataFrame using SQLAlchemy connection
df = pd.read_sql_query('SELECT * FROM your_table', con=engine)

Pandas doesn't allow columns to be created via a new attribute name: This warning usually appears when inheriting a Pandas DataFrame, and you should avoid creating columns via a new attribute name and instead use the standard Pandas way of creating columns. The sample code is as follows:

import pandas as pd

# Create an empty DataFrame
df = ()

# Create columns using the standard way
df['column_name'] = [1, 2, 3, 4, 5]

# Avoid creating columns with new attribute names
# df.new_column = [6, 7, 8, 9, 10]  # This triggers a warning.

This sample code can help you deal with these Pandas UserWarning and take appropriate steps to avoid potential problems.

summarize

To this point this article on Pandas alarm UserWarning: pandas only supports SQLAlchemy connectable way of dealing with the article is introduced to this, more related Pandas alarm UserWarning content please search for my previous posts or continue to browse the following related articles I hope that everyone! I hope you will support me more in the future!