urllib.parse.quote与decodeURIComponent的完美配合

如何优雅的把副文本编辑器的内容（HTML格式）入库，并在之后加载回前端编辑器？

我们都知道编辑器的内容经常包含很多单引号、双引号等，因此，在存储时要对Html代码进行转义与在加载时需要反转义（也可以叫做编码和解码）

• 首先想到的是js的escape和unescape 函数。哈哈，似乎问题很容易解决。

b=escape(a)
"%3Cp%3E%22select%20count%281%29%20%22%u603B%u6570%22%2C%3C/p%3E%3Cp%3E%26nbsp%3Bsum%28case%20when%20date%28begin_time%29%26lt%3B%26gt%3Bdate%28insert_time%29%20then%201%20else%200%20end%29%20%22%u4E0D%u76F8%u7B49%u6570%u91CF%22%2C%3C/p%3E%3Cp%3E%26nbsp%3Bround%28sum%28case%20when%20date%28begin_time%29%26lt%3B%26gt%3Bdate%28insert_time%29%20then%201%20else%200%20end%29/cast%28count%281%29%7C%7C%27.000%27%20as%20numeric%29%2C3%29%20%22%u4E0D%u76F8%u7B49%u5360%u6BD4%22%20%2C%3C/p%3E%3Cp%3E%26nbsp%3Bsum%28case%20when%20date%28begin_time%29%26lt%3B%20date%28insert_time%29%20then%201%20else%200%20end%29%20%22%u5C0F%u4E8Eopdt%u6570%u91CF%22%2C%3C/p%3E%3Cp%3E%26nbsp%3Bround%28sum%28case%20when%20date%28begin_time%29%26lt%3B%20date%28insert_time%29%20then%201%20else%200%20end%29/cast%28count%281%29%7C%7C%27.000%27%20as%20numeric%29%2C3%29%20%22%u5C0F%u4E8Eopdt%u5360%u6BD4%22%3C/p%3E%3Cp%3Efrom%20public.test%3C/p%3E%3Cp%3Ewhere%20insert_time%20%26gt%3B%3D%20date%28%272017-11-01%27%29%3C/p%3E%3Cp%3E%26nbsp%3B%20and%20insert_time%20%26lt%3B%3D%20date%28%272017-11-02%27%29%3B%3C/p%3E%3Cp%3E%22%3C/p%3E"

unescape(b)
"<p>"select count(1) "总数",</p><p>&nbsp;sum(case when date(begin_time)&lt;&gt;date(insert_time) then 1 else 0 end) "不相等数量",</p><p>&nbsp;round(sum(case when date(begin_time)&lt;&gt;date(insert_time) then 1 else 0 end)/cast(count(1)||'.000' as numeric),3) "不相等占比" ,</p><p>&nbsp;sum(case when date(begin_time)&lt; date(insert_time) then 1 else 0 end) "小于opdt数量",</p><p>&nbsp;round(sum(case when date(begin_time)&lt; date(insert_time) then 1 else 0 end)/cast(count(1)||'.000' as numeric),3) "小于opdt占比"</p><p>from public.test</p><p>where insert_time &gt;= date('2017-11-01')</p><p>&nbsp; and insert_time &lt;= date('2017-11-02');</p><p>"</p>"

• 但这样就不够的，因为我们还要对内容进行处理，辨别是否有敏感字啊或者对图片进行特殊存储处理之类的。一旦escape后就很难再处理Html了。

• 所以我们不能使用js的escape功能，得先让后端处理完内容，再escape。但我们也不可能让把后端处理完的内容返回到前端escape。所以我们需要通过后端来escape，我用的是Python，所以想到了urllib模块

3.6.3 (v3.6.3:2c5fed8, Oct  3 2017, 18:11:49) [MSC v.1900 64 bit (AMD64)]
from urllib import parse
parse.quote('''select count(1) "总数",
 sum(case when date(begin_time)<>date(insert_time) then 1 else 0 end) "不相等数量",
 round(sum(case when date(begin_time)<>date(insert_time) then 1 else 0 end)/cast(count(1)||'.000' as numeric),3) "不相等占比" ,
 sum(case when date(begin_time)< date(insert_time) then 1 else 0 end) "小于opdt数量",
 round(sum(case when date(begin_time)< date(insert_time) then 1 else 0 end)/cast(count(1)||'.000' as numeric),3) "小于opdt占比"
from public.test
where insert_time >= date('2017-11-01')
  and insert_time <= date('2017-11-02');
''')
'select%20count%281%29%20%22%E6%80%BB%E6%95%B0%22%2C%0A%20sum%28case%20when%20date%28begin_time%29%3C%3Edate%28insert_time%29%20then%201%20else%200%20end%29%20%22%E4%B8%8D%E7%9B%B8%E7%AD%89%E6%95%B0%E9%87%8F%22%2C%0A%20round%28sum%28case%20when%20date%28begin_time%29%3C%3Edate%28insert_time%29%20then%201%20else%200%20end%29/cast%28count%281%29%7C%7C%27.000%27%20as%20numeric%29%2C3%29%20%22%E4%B8%8D%E7%9B%B8%E7%AD%89%E5%8D%A0%E6%AF%94%22%20%2C%0A%20sum%28case%20when%20date%28begin_time%29%3C%20date%28insert_time%29%20then%201%20else%200%20end%29%20%22%E5%B0%8F%E4%BA%8Eopdt%E6%95%B0%E9%87%8F%22%2C%0A%20round%28sum%28case%20when%20date%28begin_time%29%3C%20date%28insert_time%29%20then%201%20else%200%20end%29/cast%28count%281%29%7C%7C%27.000%27%20as%20numeric%29%2C3%29%20%22%E5%B0%8F%E4%BA%8Eopdt%E5%8D%A0%E6%AF%94%22%0Afrom%20public.test%0Awhere%20insert_time%20%3E%3D%20date%28%272017-11-01%27%29%0A%20%20and%20insert_time%20%3C%3D%20date%28%272017-11-02%27%29%3B%0A'

• 经过几次试验发现一个问题，Python escape的内容在前端unescape无法识别。请教谷哥才知道：

注释：ECMAScript v3 反对使用该方法，应用使用 decodeURI() 和 decodeURIComponent() 替代它。

a
"select%20count%281%29%20%22%E6%80%BB%E6%95%B0%22%2C%0A%20sum%28case%20when%20date%28begin_time%29%3C%3Edate%28insert_time%29%20then%201%20else%200%20end%29%20%22%E4%B8%8D%E7%9B%B8%E7%AD%89%E6%95%B0%E9%87%8F%22%2C%0A%20round%28sum%28case%20when%20date%28begin_time%29%3C%3Edate%28insert_time%29%20then%201%20else%200%20end%29/cast%28count%281%29%7C%7C%27.000%27%20as%20numeric%29%2C3%29%20%22%E4%B8%8D%E7%9B%B8%E7%AD%89%E5%8D%A0%E6%AF%94%22%20%2C%0A%20sum%28case%20when%20date%28begin_time%29%3C%20date%28insert_time%29%20then%201%20else%200%20end%29%20%22%E5%B0%8F%E4%BA%8Eopdt%E6%95%B0%E9%87%8F%22%2C%0A%20round%28sum%28case%20when%20date%28begin_time%29%3C%20date%28insert_time%29%20then%201%20else%200%20end%29/cast%28count%281%29%7C%7C%27.000%27%20as%20numeric%29%2C3%29%20%22%E5%B0%8F%E4%BA%8Eopdt%E5%8D%A0%E6%AF%94%22%0Afrom%20public.test%0Awhere%20insert_time%20%3E%3D%20date%28%272017-11-01%27%29%0A%20%20and%20insert_time%20%3C%3D%20date%28%272017-11-02%27%29%3B%0A"

decodeURIComponent(a)
"select count(1) "总数",
 sum(case when date(begin_time)<>date(insert_time) then 1 else 0 end) "不相等数量",
 round(sum(case when date(begin_time)<>date(insert_time) then 1 else 0 end)/cast(count(1)||'.000' as numeric),3) "不相等占比" ,
 sum(case when date(begin_time)< date(insert_time) then 1 else 0 end) "小于opdt数量",
 round(sum(case when date(begin_time)< date(insert_time) then 1 else 0 end)/cast(count(1)||'.000' as numeric),3) "小于opdt占比"
from public.test
where insert_time >= date('2017-11-01')
  and insert_time <= date('2017-11-02');
"

所以我们在前端使用decodeURIComponent()来unescape，至此，问题圆满解决。

看到网上这块资源很少，故分享给大家。