python用pandas和sqlalchemy执行sql的大坑记录

背景

1. 在使用pandas.read_sql()从数据库加载数据到DataFrame时，发现如果sql中带有%就会报错，比如这样的sql

# 本意是想格式化日期
sql = ''' select DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 MONTH),'%Y-%m') '''

df = pd.read_sql(
            sql=sql, con=engine)  # read data to DataFrame 'df'

# 就会报错:
unsupported format character '�' (0xffffffe5) at index 459

2. 网上也没查到相关的文章，然后就想着把%转义，查到python的转义如果想把{}转义输出，就要写成{{}}，而不是其他语言的反斜杠 {}去转义， 所以这里也用 %% ，就成功转义了

# 改成 %% ,成功执行查询
sql = ''' select DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 MONTH),'%%Y-%%m') '''

df = pd.read_sql(
            sql=sql, con=engine)  # read data to DataFrame 'df'

3. 所以就以为是python中%是关键字， 在字符串拼接时都需要这样转义，于是后面再遇到sql都是这样写，没有出过问题。 直到用sqlalchemy执行相同的sql时

# use sqlalchemy to build link-engine
engine = create_engine(connect_info, pool_size=10, max_overflow=20)

DB_Session = sessionmaker(bind=engine, autocommit=True)
session = DB_Session()

# 改成 %% ,在这里居然出问题了， %%没有被转义，原样输出了 %% ，因此这个sql查询出来的结果是: %Y-%m 
# 拿来做查询条件的话，就没关联到数据，造成本来应该查询出数据的语句，返回空数据了
sql = ''' select DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 MONTH),'%%Y-%%m') '''

session.execute(sql)
session.close()

4. 而我那这个错误的查询去执行了 replace into ,sql是执行成功的，但是其实一条数据都没插进去， 并且这个写成了定时任务跑了两个星期，观察执行结果都是正常的，直到2个星期后才发现数据没有插进去，造成严重损失～

问题分析

1. 想当然的经验，造成了完全不一样的结果，原来是因为 % 仅仅是在pandas.read_sql()函数中是关键词，因为这是用来传参数的。
2. 正确使用方法: 参考文章 pandas之read_sql_query-params设置

l1=['张三','李四']
sql1= '''select id,name,score from table1 where name in %(tt)s'''
con = db.connect(server, port, user, password)
df1 = pd.read_sql_query(sql1,con,index_col='id',params = {'tt':l1})
#注意观察sql脚本参数与params参数的关系

3. 因为在pandas这个方法中 %是用来传参的，而我写的 ‘%Y-%m’ 并不符合传参格式，因此报错了。 而sqlalchemy中执行sql没有%传参，因此是原样输出的。

重点是 % 在python中并不是关键词啊， 我之前还以为str.format()中%都是关键词，必须要用%%转义

最佳实践

1. 经过这样分析，所以如果sql中需要用到 %, 比如date_format或者like语句， 用pandas的话，还是做成传参吧， 这样应该还能避免sql注入问题.
2. 而sqlalchemy中的传参，是使用 :varName ,参考文章.sqlalchemy - sqlalchemy中执行原生sql - 传参方式避免了sql注入 比如

def get_data_all(user_id, name, start_time, end_time, page=1, limit=10):
    """
    sqlalchemy orm 执行原生sql语句
    :return:
    """
    try:
        # 项目数据列表
        conditions = dict()
        base_sql_pre = "select * from table1 p  where p.status = 1  and p.user_id = :user_id"
 
        conditions.update({"user_id": user_id})
        if start_time:
            start_time_sql = " and p.create_time >= :start_time"
            conditions.update({"start_time": start_time})
        else:
            start_time_sql = ""
        if name:
            name_sql = " and p.name like concat('%', :name, '%')"
            conditions.update({"name": str(name).strip()})
        else:
            name_sql = ""
        
 
        # 组合sql
        select_sql = base_sql_pre + start_time_sql  + name_sql
        cursor = db.session.execute(select_sql, conditions)
        res = cursor.fetchall()
 
        
        return res
    except Exception:
        traceback.print_exc()

扩展思考

1. 用python执行replace into 等DDL语句，无法返回操作的记录数，因此无法判断任务是否执行成功，而sql本身是执行成功了的。在作为定时任务时也不能真正返回执行结果，有没有什么好的优化方式呢？ 在数据库客户端其实能看到replace into 的结果是 "x row affected, take y ms"的，这个结果sqlalchemy 有什么方法能获取到吗？