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

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

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

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

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

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

解决方案 使用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()```

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63