CODEWITHSUNDEEP
pythonoracle-databasesqlalchemycx-oracle
Ayman
Ayman

Reputation: 1

How do I run cx_Oracle queries within my session limit?

I am querying an Oracle database through cx_Oracle Python and getting the following error after 12 queries. How can I run multiple queries within the same session? Or do I need to quit the session after each query? If so, how do I do that?

DatabaseError: (cx_Oracle.DatabaseError) ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
(Background on this error at: https://sqlalche.me/e/14/4xp6)

My code looks something like:

import pandas as pd
import cx_Oracle
from sqlalchemy import create_engine

def get_query(item):
    ...
    return valid_query

cx_Oracle.init_oracle_client(lib_dir=r"\instantclient_21_3")
user = ...
password = ...
tz = pytz.timezone('US/Eastern')
dsn_tns = cx_Oracle.makedsn(...,
                            ...,
                            service_name=...)
cstr = f'oracle://{user}:{password}@{dsn_tns}'
engine = create_engine(cstr,
                       convert_unicode=False,
                       pool_recycle=10,
                       pool_size=50,
                       echo=False)
df_dicts = {}
for item in items:
    df = pd.read_sql_query(get_query(item), con=cstr)
    df_dicts[item.attribute] = df

Thank you!

Upvotes: 0

Views: 582

Answers (1)

AlecZ
AlecZ

Reputation: 590

You can use the cx_Oracle connection object directly in Pandas - for Oracle connection I always found that worked better than sqlalchemy for simple use as a Pandas connection.

Something like:

conn = cx_Oracle.connect(f'{user}/{password}@{dsn_tns}')
df_dicts = {}
for item in items:
    df = pd.read_sql(sql=get_query(item), con=conn, parse_dates=True)
    df_dicts[item.attribute] = df

(Not sure if you had dates, I just remember that being a necessary element for parsing).

Upvotes: 0

Related Questions