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

末蓝、 2021-12-04 00:03 667阅读 0赞

### 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][https_blog.csdn.net_hefrankeleyn_article_details_84986982]

[https_blog.csdn.net_hefrankeleyn_article_details_84986982]: https://blog.csdn.net/hefrankeleyn/article/details/84986982