Bulk inserts into Oracle database via Python's jaydebeapi

Question

I want to bulk-copy data from SQLite3 files to an Oracle DB, and do so programmatically from within a Python script using the jaydebeapi module. (I have no control over the choice of Python + jaydebeapi to do this; it is imposed by the project I am collaborating in.)

One way to do it would be to dump the SQLite3 tables to temporary CSV files, and use Oracle's LOAD DATA INFILE command to read the CSV files.

I am looking for a way to achieve the same end result that avoids creating the intermediate temporary files.

More specifically, since I can bulk-read the SQLite3 tables into memory (with simple SELECT statements), what I need is the bulk-write counterpart to dump the tables from memory into the Oracle database.

EDIT: This is a recurrent task. The largest table to be copied has typically ~100K rows.

@OldProgrammer: I've edited my post to address your questions. — kjo, Jun 19 '19 at 03:21

score 1 · Accepted Answer · answered Jun 19 '19 at 22:31

Since these are two different "databases" you'll likely need a connection to SQLLite to do the query, and another connection to Oracle DB to do the insert.

When it comes to inserting into Oracle DB from within a Python app, you should use the cx_Oracle module's executemany() method:

data = [
    (60, "Parent 60"),
    (70, "Parent 70"),
    (80, "Parent 80"),
    (90, "Parent 90"),
    (100, "Parent 100")
]

cursor.executemany("""
        insert into ParentTable (ParentId, Description)
        values (:1, :2)""", data)

See https://blogs.oracle.com/opal/efficient-and-scalable-batch-statement-execution-in-python-cx_oracle

score 0 · Answer 2 · answered Jun 19 '19 at 03:38

Do the SQLite3 and Oracle DB tables have the same schema? If they do, then you could try something as simple as the equivalent of this pseudocode

for table in sqlite3tables:
    (SELECT * FROM table)  ->  temptable
    for row in temptable
        INSERT row INTO oracletable

If the different databases have different schemas then you would need to write your SELECT statement in a more tailored fashion.

In either case, reading the Python Database API docs at https://www.python.org/dev/peps/pep-0249/ would be very useful to help write the solution.

Bulk inserts into Oracle database via Python's jaydebeapi

2 Answers2