python链接数据库

旧版链接方式

conn = pymysql.connect( host='111.11.111.11', port=00000, user='xxxx', password='xxxxx', db='xxxx',charset='utf8', autocommit=True, cursorclass=pymysql.cursors.DictCursor)

这种链接方式可以使用但是会报Warning。

C:\Users\lxzl\AppData\Local\Temp\ipykernel_9700\1728336626.py:1: 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.

需要更改新版的链接方式，如下

MYSQL_HOST = '111.11.111.11'MYSQL_PORT = '0000'MYSQL_USER = 'xxxx'MYSQL_PASSWORD = 'xxxxxx'MYSQL_DB = 'xxxx'conn = create_engine('mysql+pymysql://%s:%s@%s:%s/%s?charset=utf8' % (MYSQL_USER, MYSQL_PASSWORD, MYSQL_HOST, MYSQL_PORT, MYSQL_DB))

但是新版的链接方式会对sql语句中的%比较敏感，如果sql中有%，会报【ValueError: unsupported format character ‘’’ (0x27) at index 807】。我搜寻了两种方式。

# 方法一：将SQL转为textfrom sqlalchemy import create_engine, textdf3012 = pd.read_sql_query(text("XXX.sql")),conn)# 方法二：将sql语句的%替换成%%，读取的sql少可直接该文本，多的话可以写个函数读的时候批量改，以免重复修改sql文件。def readSqlFile(file):with open(file,encoding="utf8") as f:sql = f.read()sql = sql.replace('%','%%')return sqldf3012 = pd.read_sql_query("XXX.sql"),conn)