0

I was previously using python to pass multiple queries to an Oracle Database but the script took too long to execute. So I decided to opt for PyPy and use the JIT Compiler rather than the CPython interpreter. I installed PyPy via apt and then compiled the script with the command:

pypy script_file.py

It returned back the following error:

import cx_Oracle
ImportError: No module named cx_Oracle

Is cx_Oracle not supported by PyPy or am I doing something incorrectly? If it is, is this due to an issue with the environment values and how can I set it right?

Thank you for the help! Sorry for the bad English.

Kushagra Chaturvedy
  • 3
  • 2
  • You can check [here](https://stackoverflow.com/a/9254649/6247206) for the solution. – Alihan ÖZ Jun 25 '20 at 11:21
  • 1
    Does this answer your question? [How to install/use cx\_Oracle in PyPy](https://stackoverflow.com/questions/9230206/how-to-install-use-cx-oracle-in-pypy) – rizerphe Jun 25 '20 at 11:27
  • what happens when you `pypy3 -mpip install cx_oracle`? Does it succeed? [This issue](https://github.com/oracle/python-cx_Oracle/issues/53) was closed so it should work. You may want to open a new issue there asking that they provide binary wheels for PyPy3. – mattip Jun 26 '20 at 05:11
  • @БогданОпир I saw this solution earlier but not only is it outdated (from 2012) it isn't the solution I'm looking for as one comment to the answer mentions that it installs an older version of cx_Oracle that was outdated even for 2012 so I'm guessing it won't support python 3 which is what I'm using. Nevertheless, I'll try it. – Kushagra Chaturvedy Jun 26 '20 at 07:14
  • @mattip Thank You! I got it working by installing pypy3 and using its pip to install cx_oracle and then executing the script using pypy3 instead of python. You can post your comment as an answer. – Kushagra Chaturvedy Jun 26 '20 at 07:20

1 Answers1

0

Regardless of PyPy support, each connection to Oracle Database will still only ever be able to execute one statement at a time. You could stick with CPython and look at other architectures such as opening multiple connections. Also tune your queries and tune how cx_Oracle fetches data, eg. with arraysize and the (new in cx_Oracle 8) prefetchrows. See the cx_Oracle manual section Tuning cx_Oracle.

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48
  • Ok I figure I should mention what I am working on. Basically, I am tasked with converting PL SQL procedures into python. What I am doing is connecting to the database, and passing the queries as it is through the .execute() method and implementing the logic of if else loops and any intermediate variables in python. However, I have a feeling that the performance issues that I mentioned are due to the fact that I am passing very large (almost 100-200 lines) queries which are taking a long time to execute. The equivalent PL SQL code takes 2-3 seconds to execute while my python implementation... – Kushagra Chaturvedy Jun 30 '20 at 11:41
  • ...takes about 30 seconds. So this is what I wanted to ask: Is cx_Oracle not feasible for extremely long and deeply nested queries? I have used some of the optimization that were mentioned in the tuning section of cx_Oracle like bind variables but in the end, I don't think that the optimization would be enough to shave off 25-28 seconds from my python runtime to make my python based solution feasible. – Kushagra Chaturvedy Jun 30 '20 at 11:46
  • Doesn't sound like the logical conclusion to me. Please keep your original post updated with the information. And share actual details about what you are doing (e..g by query do you mean SELECT or are you being lax and meaning another kind of statement. The main premise about converting PL/SQL to Python also sounds 'wrong' - why bother? Why move load and data off the DB server? – Christopher Jones Jun 30 '20 at 21:57
  • By query, I'm referring to all kinds of SELECT, INSERT INTO, DELETE queries. I'm an intern and my supervisor told me that the database back end would be faster if I could replace PL/SQL functions with python. So, in a PL/SQL function whenever I come across a SELECT, INSERT INTO, DELETE query, I pass it as it is in the execute method and when I would come across a if else or loop I would implement that logic in python. – Kushagra Chaturvedy Jul 01 '20 at 04:31
  • While there are some cases where processing data outside the DB is better, there are many cases where the cost of moving data to & from the DB is significant. If you split up PL/SQL blocksinto individual statements you'll end up with more round-trips (for each statement) on top of all data transfers. You'll have to make the assessment with your own environment (data sizes, network costs etc). On the pure cx_Oracle side, review the cx_Oracle manual [Tuning cx_Oracle](https://cx-oracle.readthedocs.io/en/latest/user_guide/tuning.html). use optimal features like `executemany()` and `arraysize`. – Christopher Jones Jul 01 '20 at 04:47