import os
import cx_Oracle
# get all files inside a specific folder
dir_path = r'C:/Users/Administrator/PycharmProjects/pythonProject9/InputFiles/'
for path in os.scandir(dir_path):
if path.is_file():
print(path.name)
with open(dir_path + path.name, 'rb') as file:
binaryData = file.read()
try:
con = cx_Oracle.connect('scott/tiger@192.168.50.128:1521/orcl')
except cx_Oracle.DatabaseError as er:
print('There is an error in the Oracle database:', er)
else:
try:
cur = con.cursor()
cur.execute("""
insert into bindata (name, data)
values (:name, :data)""",
name=path.name, data=binaryData)
con.commit()
print("Image and file inserted successfully as a BLOB into python_employee table")
except cx_Oracle.DatabaseError as er:
print('There is an error in the Oracle database:', er)
except Exception as er:
print('Error:' + str(er))
finally:
if cur:
cur.close()
finally:
if con:
con.close()
import cx_Oracle
dir_path = r'C:/Users/Administrator/PycharmProjects/pythonProject9/DownLoadFiles/'
try:
con = cx_Oracle.connect('scott/tiger@192.168.50.128:1521/orcl')
except cx_Oracle.DatabaseError as er:
print('There is an error in the Oracle database:', er)
else:
try:
cur = con.cursor()
# fetchall() is used to fetch all records from result set
cur.execute('select * from bindata')
for rows in cur:
print(rows)
print(rows[1])
with open(dir_path + rows[1], "wb") as f:
data = rows[2].read()
f.write(data)
except cx_Oracle.DatabaseError as er:
print('There is an error in the Oracle database:', er)
except Exception as er:
print('Error:' + str(er))
finally:
if cur:
cur.close()
finally:
if con:
con.close()
CREATE TABLE "SCOTT"."BINDATA" (
"ID" NUMBER(10, 0),
"NAME" VARCHAR2(512 BYTE),
"DATA" BLOB
)
ALTER TABLE bindata ADD (
CONSTRAINT bindata_pk PRIMARY KEY ( id )
);
CREATE SEQUENCE bindata_sequence;
CREATE OR REPLACE TRIGGER bindata_on_insert BEFORE
INSERT ON bindata
FOR EACH ROW
BEGIN
SELECT
bindata_sequence.NEXTVAL
INTO :new.id
FROM
dual;
END;