插入"&"特殊字符的几种思考

一位铁杆朋友,今天问了个问题,写了一个Python程序,从文件读取数据,其中可能包含“&”这种特殊字符,为了让其能插入Oracle,需要做什么处理?

首先,我想问的问题,"&"字符在插入数据库的时候有什么特别之处?

在sqlplus下执行如下SQL,向表A插入记录“a&a”,他会提示我们“Enter value for a:”,输入变量a的值,此处输入空,则实际存储的,就是"a",

SQL> create table a (id number, a varchar2(25));
Table created.


SQL> insert into a values(1, 'a&a');
Enter value for a:
old   1: insert into a values(1, 'a&a')
new   1: insert into a values(1, 'a')


1 row created.


SQL> select * from a;
       ID   A
---------- -------------------------         
        1   a

执 行show all,可以看到这行define "&" (hex 26),其实这个就是Oracle中用来识别自定义变量的设置,当SQL中包含"&"时,Oracle就会认为你要输入变量了,就像上面显示的 “Enter value for a:”,

SQL> show all
...
define "&" (hex 26)

在sqlplus下,可以设置set define off,此时就关闭了自定义变量,他会将"&"看作是普通字符,直接使用"a&a",不会提示“Enter value for a:”,

SQL> set define off


SQL> insert into a values(1, 'a&a');
1 row created.

但是在python程序中,使用cx_Oracle连接Oracle数据库,并不支持执行set define off,原因就是cx_Oracle只支持标准SQL语句,这个set define off只是在sqlplus下才能执行的语句,

The command "set define off" is not a SQL statement, but a SQLPlus statement. As such, it can only be executed in SQLPlus. Since cx_Oracle only processes SQL statements, this command is unnecessary in any case! If you are reading from a file that contains SQL statements that are normally run by SQL*Plus you'll need to filter these statements out.

当然,你可以使用shell脚本,执行sqlplus就可以用set define off了,但是受限于实际,不能将Python程序转为shell。

除此之外,还可以怎么做?

我们能得到"&"的ASCII码是38,

SQL> select ascii('&') from dual;
ASCII('&')
----------
    38

我们使用chr(38),代替"&",通过拼接字符串,就可以插入了,

SQL> insert into a values(1, 'a'||chr(38)||'a');
1 row created.


SQL> select * from a;
        ID A
---------- -------------------------
         1 a&a

但是问题来了,Python程序可以读取行数据的时候,将"&"替换为'||chr(38)||',但是这只是一般的情况,当出现"&&",或者多个"&"的时候,可能要特殊的判断逻辑,才可以替换正确,有些复杂。

有其他的方法么?


我们换种思维,"&"字符直接入库,Oracle会将其认为是有特殊含义的,如果插入的不是"&",就可以解决了?

步骤如下,

1.Python读取行数据时,将字符串中"&"替换为其他的字符,例如"#",或者不可见字符(避免待替换字符就存在于字符串中)。

2.插入Oracle时,将"#",replace替换为"&",如下所示,

SQL> insert into a values(1, replace('a#c', '#', chr(38)));
1 row created.


SQL> select * from a;
        ID A
---------- -------------------------
         1 a&c

这个方案就比较耐看了,一方面,是无需关注原始字符串中,到底存在几个连续的"&",要特殊的判断,另一方面,插入的时候,replace函数能通用,无需针对不同的输入选择特殊的逻辑,一个词概括,就是“通用”。

参考:

https://stackoverflow.com/questions/57470276/unable-to-set-define-off-in-cx-oracle

http://blog.itpub.net/20750200/viewspace-706760/

https://blog.csdn.net/dreary001/article/details/84355504

近期热文:

公众号600篇文章分类和索引

Oracle ACE,一段不可思议的旅程

Oracle 19c之RPM安装

应用执行慢的问题排查路径

ACOUG年会感想

千万级表数据更新的需求

探寻大表删除字段慢的原因

一次Oracle bug的故障排查过程思考

新增字段的一点一滴技巧

对recursive calls的深刻理解

《Oracle Concept》第三章 - 12

一次惊心动魄的问题排查

英超梦幻之行

藤子不二雄博物馆之行

传控Tiki-Taka战术解惑

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值