1. 完整报错信息

1
2
3

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

tb1 = pd.read_sql(sql, db)

2. 抛出告警时的导入方式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

import pymysql
import pandas as pd
from const import _const

db = pymysql.connect(  
    host=_const.MYSQL_HOST,  
    user=_const.MYSQL_USER,  
    password=_const.MYSQl_PASSWORD,  
    port=_const.MYSQL_PORT,  
    database=_const.MYSQL_DB  
)  
  
sql = 'select * from test where flag = 0'

df = pd.read_sql(sql, db) 
print(df)

3. 推荐使用的方式

推荐使用 SQLAlchemy，需要先安装 sqlalchemy 库

比较典型的数据库URL格式 ：

dialect+driver://username:password@host:port/database

例如：

  • MySQL：

mysql+mysqlconnector://username:password@host:port/database

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

from const import _const  
import pandas as pd  
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://{user}:{password}@{host}:{port}/{database}?charset=utf8'  
                       .format(user=_const.MYSQL_USER, password=_const.MYSQL_PASSWORD, host=_const.MYSQL_HOST, port=_const.MYSQL_PORT, database=_const.MYSQL_DB))

sql = 'select * from test where flag = 0'

df = pd.read_sql(sql, engine)  
pd.set_option('display.unicode.ambiguous_as_wide', True)  # 设置列名对齐  
pd.set_option('display.unicode.east_asian_width', True)  # 设置列名对齐  
# pd.set_option('display.max_rows', None)  # 显示所有行  
pd.set_option('display.max_columns', None)  # 显示所有列  
pd.set_option('expand_frame_repr', False)  # 设置不换行  
  
print(df.info)

修改运行后无告警

img.png