Extract files from an oracle database with Python

Question

i'm using cx_Oracle to access mon Oracle database i've been looking for a while a way to extract files from my database but i can't find the solution to it. And by extracting i mean download. Here's what i got so far :

from types import prepare_class
from typing import overload
import cx_Oracle

dsn_tns = cx_Oracle.makedsn('****', '****', service_name='****')
conn = cx_Oracle.connect(user=r'****', password='****', dsn=****)

c = conn.cursor()
c.execute('my request')

for row in c:
    print (row[0], '-', row[1])
conn.close()

i don't know if i need to use an external library or directly with a request.

Do you mean that you're trying to query all of the rows in a database table and save them to a file? What kind of file - CSV? There's several options in cx_oracle for fetching 1 row, multiple rows, or all the rows. You can also use a library like pandas — kfinity
– kfinity, Commented Nov 18, 2021 at 14:29
... what do you mean, specifically ? You just need to write the content into a file. — Maurice Meyer
– Maurice Meyer, Commented Nov 18, 2021 at 14:31
there's blob files that i want to extract/download but yeah i could just open them one by one and write them in files but i don't know if it's quicker and i didn't knew cx_oracle could do it i'll look at how it works thanks — Tsirsuna
– Tsirsuna, Commented Nov 18, 2021 at 14:40
If the binary files stored as blob are text files, you can use a function to convert the blob to a clob, then download the clob as text in a normal extraction. The problem comes if the blob files are something different, like pictures or audio — Roberto Hernandez
– Roberto Hernandez, Commented Nov 18, 2021 at 15:43
Review the cx_Oracle doc cx-oracle.readthedocs.io/en/latest/user_guide/tuning.html and cx-oracle.readthedocs.io/en/latest/user_guide/lob_data.html — Christopher Jones
– Christopher Jones, Commented Nov 18, 2021 at 22:15

Ptit Xav · Accepted Answer · 2021-11-18 14:48:08Z

1

If you have a column with the name and one with file contents you do something like :

for fileName,fileContents in c.execute('select fileName,file contents from the_table'):
    with open(fileName, w) as f:
        F.write(fileContents)

answered Nov 18, 2021 at 14:48

Ptit Xav

3,2272 gold badges10 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tsirsuna Over a year ago

i see but when i open the file within the table F.write will go through the entire file and write it in another ?

Ptit Xav Over a year ago

I assume in your table you have the full file contents that what to be written in a file. So only one f.write

Collectives™ on Stack Overflow

Extract files from an oracle database with Python

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related