python mysql语句中有单引号执行的报错处理方式

最新推荐文章于 2024-09-21 21:56:57 发布

qq_35640866

最新推荐文章于 2024-09-21 21:56:57 发布

阅读量634

点赞数 7

分类专栏： python 文章标签： python mysql

本文链接：https://blog.youkuaiyun.com/qq_35640866/article/details/136659895

版权

python 专栏收录该内容

14 篇文章

订阅专栏

文章讲述了使用Python的format方法拼装SQL语句时遇到的问题，当SQL包含单引号时会引发错误。作者提供了解决方案，即使用参数化查询(cur.execute(insert_sql,sql_info))来避免字符串格式化带来的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

用python format 拼装sql语句，发现带单引号的sql 语句，

 ... in ('a','b','c')

转换为类似下面的语句执行的时候报错。

insert into into rds_all_slow_sql （sqltext） values （'... in ('a','b','c')'）

format 格式化sql 代码

sql_command = """
               insert into rds_all_slow_sql(
                                inst_id,
                                inst_name,
                                dbname,
                                execution_start_time,
                                host_address,
                                lock_times,
                                parse_row_counts,
                                query_time_ms,
                                query_times,
                                return_row_counts,
                                ali_sqlhash,
                                sql_finger_md5,
                                sql_finger,
                                sqltext_md5,
                                sqltext)
                    values ('{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}',{})
               """.format(
                                '',
                                '',
                                sqlslow_record.dbname,
                                self.utc_to_local(sqlslow_record.execution_start_time),
                                sqlslow_record.host_address,
                                sqlslow_record.lock_times,
                                sqlslow_record.parse_row_counts,
                                sqlslow_record.query_time_ms,
                                sqlslow_record.query_times,
                                sqlslow_record.return_row_counts,
                                sqlslow_record.sqlhash,
                                sql_finger_md5,
                                sql_finger,
                                sqltext_md5,
                                sqltext)

解决方案使用cur.execute(insert_sql, sql_info) 的方式格式化sql语句


insert_sql = """
               insert into rds_all_slow_sql(
                                inst_id,
                                inst_name,
                                dbname,
                                execution_start_time,
                                host_address,
                                lock_times,
                                parse_row_counts,
                                query_time_ms,
                                query_times,
                                return_row_counts,
                                ali_sqlhash,
                                sql_finger_md5,
                                sql_finger,
                                sqltext_md5,
                                sqltext) 
                    values (
                                %(inst_id)s,
                                %(inst_name)s,
                                %(dbname)s,
                                %(execution_start_time)s,
                                %(host_address)s,
                                %(lock_times)s,
                                %(parse_row_counts)s,
                                %(query_time_ms)s,
                                %(query_times)s,
                                %(return_row_counts)s,
                                %(ali_sqlhash)s,
                                %(sql_finger_md5)s,
                                %(sql_finger)s,
                                %(sqltext_md5)s,
                                %(sqltext)s
                            )
               """


sql_info = {
    "inst_id": '',
    "inst_name": '',
    "dbname": sqlslow_record.dbname,
    "execution_start_time": self.utc_to_local(sqlslow_record.execution_start_time),
    "host_address": sqlslow_record.host_address,
    "lock_times": sqlslow_record.lock_times,
    "parse_row_counts": sqlslow_record.parse_row_counts,
    "query_time_ms": sqlslow_record.query_time_ms,
    "query_times": sqlslow_record.query_times,
    "return_row_counts": sqlslow_record.return_row_counts,
    "ali_sqlhash": sqlslow_record.sqlhash,
    "sql_finger_md5": sql_finger_md5,
    "sql_finger": sql_finger,
    "sqltext_md5": sqltext_md5,
    "sqltext": sqltext

}


try:
    # cur.execute(sql_command)
    cur.execute(insert_sql, sql_info)
    self.conn.commit()```