Python中： unsupported format character ''' (0x27)

Python中： unsupported format character ''' (0x27)

1)MySQL DB使用%运算符将参数放入查询中，因此查询字符串中的任何单个%字符都被解释为参数说明符的开始。 
2)%在python中三个特殊的符号，如%s，%d分别代表了字符串占位符和数字占位符。

例子一、今天写python程序，用pymysql从数据库查询数据，使用like模糊匹配报错：

query_string = "SELECT DATE_FORMAT(movie.publish_time,'%Y-%m-%d'),movie.movie_name FROM movie WHERE movie.publish_time > now() and movie.media_type = 'movie' and movie.movie_type like '%%%%%s%%%%';"
query_param = ["喜剧"]

执行

count = cur.execute(query_string, query_param)

报错

Python: ValueError: unsupported format character ''' (0x27) at index 1

解决方案： 
1）解决方案1

query_param = ['%%%s%%' % '喜剧']
'''
>>> print ['%%%s%%' % '喜剧']
['%\xe5\x96\x9c\xe5\x89\xa7%']
>>> print ['%%%s%%' % '喜剧'][0]
%喜剧%
'''
query_string = "SELECT left(movie.publish_time,10),movie.movie_name FROM movie WHERE movie.publish_time > now() and movie.media_type = 'movie' and movie.movie_type like %s;"
count = cur.execute(query_string, query_param)

2)解决方案2

query_param = ['喜剧']
query_string = "SELECT left(movie.publish_time,10),movie.movie_name FROM movie WHERE movie.publish_time > now() and movie.media_type = 'movie' and movie.movie_type like '%%%s%%';"
count = cur.execute(query_string % tuple(query_param))

解释： 
1)MySQL DB使用%运算符将参数放入查询中，因此查询字符串中的任何单个%字符都被解释为参数说明符的开始。 
2)%在python中三个特殊的符号，如%s，%d分别代表了字符串占位符和数字占位符。

参考：https://blog.csdn.net/qq_35790269/article/details/81912953 

例子二、同理使用python涉及hivesql或者sparksql时也存在同样的问题，如下：

文件1、 produce_user_actions_detail.py

sql_insert_to_hive_dwd_user_actions_visit='''
use hive_dw;
insert overwrite directory '/home/hdp-jinke-data/yzhe/project/datawarehouse/dwd/hive_dwd_user_actions_daily/daily/%s/visit' row format delimited fields terminated by '\t'
select user_id,deviceid,action_time from hive_stat_record where pday>='%s' and pday<='%s' and deviceid like like '%-%-%-%-%'  and app_type='ios'  '''

文件2、execute_daily.py

#!/usr/bin/python2.7
# -*- coding:utf-8 -*-
import os
import sys
from pyspark import SparkContext, SparkConf
from pyspark.sql import HiveContext
reload(sys)
sys.setdefaultencoding("utf-8")
os.environ['PYSPARK_PYTHON'] = 'python27/bin/python2.7'
import produce_user_actions_detail
conf = SparkConf().setAppName(jobName).setMaster("yarn-client")
sc = SparkContext(conf=conf)
hiveContext = HiveContext(sparkContext=sc)
date_list=['20190710','20190713']
loan_visit_sql = produce_user_actions_detail.sql_insert_to_hive_dwd_user_actions_visit % tuple(date_list)
for sql in loan_visit_sql.split(";"):
    hiveContext.sql(sqlQuery=sql.strip())

文件3、execute_main.sh

thedate=$(/bin/date -d-1day "+%Y%m%d")
jobname='execute_spark_sql_from_yz'
spark-submit --num-executors 100 \
         --executor-memory 2G \
         --executor-cores 4 \
         --conf spark.default.parallelism=500 \
         --conf spark.storage.memoryFraction=0.3 \
         --conf spark.shuffle.memoryFraction=0.6 \
         --conf spark.sql.result.partitions=20 \
             $cwd/execute_daily.py $thedate $jobname 

执行bash ./execute_main.sh, 如下错误，解决办法同上：
    63 ValueError: unsupported format character ''' (0x27) at index 986

'''
select deviceid from hive_stat_record where pday='20190716' and deviceid like like '%-%-%-%-%' limit 5;
447440868-F6DA-4E13-BD97-EF5233F06A03
E62eeB7326-9A6A-402C-964F-96D52513FEA1
F439553DDD-1E7E-4E25-A776-2A73A594C8F2
8F93AggB54-3AE3-410D-A4D9-D48B76CEC16E
F4393ggDDD-1E7E-4E25-A776-2A73A594C8F2
'''

例子三、写python程序用pymysql从数据库查询数据，使用like模糊匹配报错：

1）、python中，拼接字符串，使用'''符号拼接成下面的内容：

select mobile user_device from user_info

where 1=1

and product_name not like '%大王咔%'

and pay_type='2'

and date_time like '201811%';

2）、最初使用下面的语法，报 unsupported format character ''' (0x27)错误：

big_card = '''select mobile user_device from user_info

where 1=1

and product_name not like '%%s%'

and pay_type='2'

and date_time like '%s%';

''' % (product_name,pay_type,date_time)

经过查询发现，当“%s”前后有“%”的时候，想要将其当做“%”的字符使用，需要用“%%”表示，因此改动方案如下：

big_card = '''select mobile user_device from user_info

where 1=1

and product_name not like '%%%s%%'

and pay_type='2'

and date_time like '%s%%';

''' % (product_name,pay_type,date_time)

3）、这样之后，就不会报错了，顺利拼接成下面的字符串：

select mobile user_device from user_info

where 1=1

and product_name not like '%大王咔%'

and pay_type='2'

and date_time like '201811%';

参考：https://blog.csdn.net/hefrankeleyn/article/details/84986982