CODEWITHSUNDEEP
pythoncx-oracle
smackenzie
smackenzie

Reputation: 3022

cx_Oracle SessionPool root of all Flask problems

I created a web service in Flask over uwsgi. I thought I would follow good practice and create a SessionPool with 20 connections to be safe. Each call to a web service endpoint, I acquire a connection from the pool, and at the end I release it.

When using Locust to swarm test the API, I was getting hundreds of failures, nearly 100% on some of the longer responses (30Mb JSON response). Smaller payloads were much better, but with intermittent failures.

The minute I switched back to bad practice and created a brand new connection and cursor within the method itself, all my problems vanished. 100% success on 1000s of stress test calls.

My errors were varied. TNS Bad Packet, incorrect number of connections from pool, request cancelled by user....you name it, it was there.

So I can't use Oracle connection pooling with flask it seems, or have a single connection at the Flask application level (this generated errors, not sure why, which is why I switched to connection pooling).

Any advice on creating scalable apps using cx_Oracle in flask.

My original code was:

pool = cx_Oracle.SessionPool("user", "password", "myserver.company.net:1521/myservice", min=10, max=10, increment=0, getmode=cx_Oracle.SPOOL_ATTRVAL_WAIT, encoding="UTF-8")







def read_products_search(search=None):
    """
    This function responds to a request for /api/products
    with the complete lists of people

    :return:        json string of list of people
    """
    conn_ariel = pool.acquire()   
    cursor_ariel = conn_ariel.cursor()


    search=search.lower()
    print("product search term is: ", search)
    # Create the list of products from our data
    sql = """
        SELECT DRUG_PRODUCT_ID, PREFERRED_TRADE_NAME, PRODUCT_LINE, PRODUCT_TYPE, FLAG_PASSIVE, PRODUCT_NUMBER
        FROM DIM_DRUG_PRODUCT 
        WHERE lower(PREFERRED_TRADE_NAME) LIKE '%' || :search1 || '%' or lower(PRODUCT_LINE) LIKE '%' || :search2 || '%'  or lower(PRODUCT_NUMBER) LIKE '%' || :search3 || '%' 
        ORDER BY PREFERRED_TRADE_NAME ASC
        """
    cursor_ariel.execute(sql, {"search1":search,"search2":search, "search3":search })
    products = []




    for row in cursor_ariel.fetchall():

        r = reg(cursor_ariel, row, False)

        product = {
            "drug_product_id"           :   r.DRUG_PRODUCT_ID,
            "preferred_trade_name"      :   r.PREFERRED_TRADE_NAME,
            "product_line"              :   r.PRODUCT_LINE,
            "product_type"              :   r.PRODUCT_TYPE,
            "flag_passive"              :   r.FLAG_PASSIVE,
            "product_number"            :   r.PRODUCT_NUMBER

        }
        # logging.info("Adding Product: %r", product)
        products.append(product)

    if len(products) == 0:
        products = None

    pool.release(conn_ariel)
    return products

Upvotes: 0

Views: 1815

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10506

When you create the pool, use threaded=True.

See How to use Python Flask with Oracle Database.

Upvotes: 1

Related Questions