Well, Oracle database has another way to run Python script, not need to involve Java this time. External table support external runnable thing (such as OS command, C program, script) with key word preprocessor! actually, it is a better deal when I need those Python script to feed Oracle database.
As oracle document said. ”
If the file you want to load contains data records that are not in a format supported by the ORACLE_LOADER access driver, then use the PREPROCESSOR clause to specify a user-supplied preprocessor program that will execute for every data file. Note that the program specification must be enclosed in a shell script if it uses arguments.
The preprocessor program converts the data to a record format supported by the access driver and then writes the converted record data to standard output (stdout), which the access driver reads as input.”
This feature started from 10.2.0.5.
I found 2 good examples in following URLs
-Linux shell script https://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/xtables_preproc11g_1009.pdf
-Windows bat file
http://www.oracle-developer.net/display.php?id=513
So I got my example to feed Oracle database from Python script output.
#!/usr/local/bin/python3
import sys
from faker import Faker
fake=Faker('en_CA')
try:
x = max(1,int(sys.argv[1]))
except:
x = 1
for i in range(x):
a1=(''.join(fake.email())).replace('\n',', ')
a2=(''.join(fake.phone_number())).replace('\n',', ')
print('|'.join([a1,a2]))
And it needs a simple shell script to hide this Python script plus parameters.
/tmp/python/script/myDemoX.py 100
The key part is external table.
create directory Python_script as '/tmp/python/script';
create directory Python_conf as '/tmp/python/conf';
create directory Python_log as '/tmp/python/log';
grant read,execute on directory Python_script to system;
grant read on directory Python_conf to dbuser;
grant read,write on directory Python_log to dbuser;
DROP TABLE dbuser.myDemo_ext purge;
CREATE TABLE dbuser.myDemo_ext
(
email VARCHAR2(100),
phone VARCHAR2(20)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY Python_conf
ACCESS PARAMETERS
(
RECORDS DELIMITED by NEWLINE
BADFILE Python_log: 'myDemo.bad'
DISCARDFILE Python_log: 'myDemo.discard'
LOGFILE Python_log: 'myDemo.log'
PREPROCESSOR Python_script:'myDemoX.sh'
FIELDS OPTIONALLY ENCLOSED BY '"' LRTRIM MISSING FIELD VALUES ARE NULL (
email char(30) TERMINATED BY '|' ,
phone char(30) TERMINATED BY ''
)
)
LOCATION ('file')
)
REJECT LIMIT UNLIMITED;
# don't forget create a dummy file
$ touch /tmp/python/conf/file
SQL> create table mydemo as select * from mydemo_ext;
Table created.
SQL> select * from mydemo;
EMAIL PHONE
-------------------------------------------------- --------------------
william68@gmail.com 642-274-9739
lauren22@diaz.com 665-515-7129 x645
thomasnicolas@frank.net (568) 409-4511
zmunoz@hotmail.com (700) 240-4337
birdmichelle@yahoo.com 543 449 7777
davidrobinson@blankenship.org 749.171.8616
hillkristen@gmail.com 137-263-6943
..
It is easier than Java method I talked before.
