CODEWITHSUNDEEP
pythonoracle-databasesqlalchemyanacondafastapi
Taras
Taras

Reputation: 276

Accessing Oracle via SQLAlchemy's `create_engine()` requires more settings, is not it?

I am developing an API by means of the FastAPI. It is intended to get an input, retrieving data from a database (Oracle), and providing a processed output.

Here is a function that executes queries with Oracle engine:

from sqlalchemy import exc, create_engine, inspect

def db_connection_sqlalchemy(your_query):
    db_config = config['database'] # configurations from a yml-file
    
    try:
        engine = create_engine("{0}+{1}://{2}:{3}@{4}".format(db_config['dialect'] #'oracle',
                                                              db_config['sql_driver'] #'cx_oracle',
                                                              db_config['username'],
                                                              db_config['password'],
                                                              db_config['network_alias']),
                               max_identifier_length=30,
                               encoding="utf8")
        inspector = inspect(engine)
        conn = engine.connect()
        rows = conn.execute(your_query)
        rows = rows.fetchmany()
        result = [dict(row.items()) for row in rows]
        conn.close()
        return result

    except exc.SQLAlchemyError as e:
        error = str(e.__dict__['orig'])
        return error

It works okay. However, to set it up I faced several issues.

Issue 1. After installing the SQLAlchemy via pip install SQLAlchemy, I was encountering this error:

NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:oracle.cx_oracle

Even though I thought that "I do not need to import cx_Oracle anymore", based on this answer. I decided to install pip install cx_Oracle and than I got a next problem

Issue 2. Every time when I tried to execute an SQL-query, I was seeing this error:

"DPI-1047: Cannot locate a 64-bit Oracle Client library: "C:\oracle\12.2.0\x86\bin\oci.dll is not the correct architecture". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help"

So, to overcome this problem, this answer helped me.

Actually everything works till now, but is it still possible to avoid those additional steps (installing cx-Oracle library, getting dll-files) when dealing with SQLAlchemy or these are typical pitfalls when working with the Oracle DB in a virtual environment?

I have a virtual environment via Anaconda and Python 3.7.10 on Windows 10.

Upvotes: 1

Views: 2580

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10536

I always install cx_Oracle separately - adding one word "cx_Oracle" to my pip install isn't a big deal. Regarding the quoted wording: importing (in a running script) is different to installing (in a system).

You will always need to install Instant Client and set PATH to it, or call cx_Oracle.init_oracle_client(lib_dir= r"c:\path_to_libraries") in your code (see here).

When fetching a large number of rows, tune arraysize and prefetchrows, see Tuning Fetch Performance.

Upvotes: 2

Related Questions