How do I use cx_Oracle to connect to Oracle DB?

Help
1

I have cx_Oracle installed in a python venv, and when I try to use it I get:

>>> import cx_Oracle
>>> cx_Oracle.connect("blabla")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory". See https://oracle.github.io/odpi/doc/installation.html#linux for help

So I figured I should install pkgs.oracle-instantclient since it has the missing .so file. I get the same error when I rerun the code. People got it working in oracle-instantclient: 12.2.0.1.0 -> 19.3.0.0.0 by flokli · Pull Request #67206 · NixOS/nixpkgs · GitHub so I’m missing something.
How should it work, and how should I solve the problem?

2

Did you use Nix to have the python environment provided?

For me, it still works just fine in a nix-shell:

❯ nix-shell -p python3.pkgs.cx_oracle --run python
Python 3.7.5 (default, Oct 14 2019, 23:08:55) 
[GCC 8.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> cx_Oracle.connect("blabla")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
cx_Oracle.DatabaseError: ORA-12162: TNS:net service name is incorrectly specified
>>>

https://github.com/NixOS/nixpkgs/blob/8ce65087c333097ab714d23800b69fc471ec48ca/doc/languages-frameworks/python.section.md should explain how to use this outside of nix-shell, too.

3

I’ve never used it for python, but I should git it a go.

4

So I made a shell.nix as described in your link with the content of

with import <nixpkgs> { config = { allowUnfree = true; }; };
(python3.withPackages (ps: [ps.cx_oracle])).env

and could successfully connect to my oracle db.
I have one last question. How do I do it without using Nix?

5

Found it, should have a paid more attention, it was in the “How to consume python modules using pip in a virtual environment like I am used to on other Operating Systems?” section.

6

Ok, I don’t understand why I need to put pythonPackages.cx_oracle into buildInputs when its already installed in venv with pip

with import <nixpkgs> { config = { allowUnfree = true; }; };

let
  pythonPackages = python3Packages;
in pkgs.mkShell rec {
  name = "impurePythonEnv";
  venvDir = "./venv";
  buildInputs = [
    pythonPackages.python
    pythonPackages.venvShellHook
    pythonPackages.cx_oracle
  ];
}

Any tips?

7

The python cx_oracle contains c bindings. If cx_oracle was installed via pip, these bindings are probably binaries coming from a tarball, and don’t know where and how to find the underlying odpic/oracle-instantclient .so files.

I always recommend using Nix to provide the complete python environment. I’m not really familiar with impurePythonEnv - (maybe remove cx_oracle from the pip-provided folders?) but I wouldn’t want to mix these things…