NoSuchColumnError: "Could not locate column in row for column" when using out parameters in postgresql

[sqlalchemy-bot]

Migrated issue, originally created by Praveen Arimbrathodiyil (@pravi)

File "/usr/lib/python2.7/dist-packages/gnukhataserver/rpc_reports.py", line 134, in xmlrpc_getLedger
grandTotal =float(balanceRow["total_CrBal"]) + float(balanceRow["curbal"])
File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/result.py", line 332, in _key_fallback
expression._string_or_unprintable(key))
sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'total_CrBal'"
org.apache.xmlrpc.XmlRpcException: error

I'm packaging gnukhata (gnukhata.org) for debian and hit this error. Krishnakant Mane (core developer of gnukhata) said everything is working fine with sqlalchemy 0.7.x. Debian sid has sqlalchemy 0.9.6. He also mentioned you have promised this would be fixed in the next release. Hopefully we can get a patch sooner so it debian and ubuntu packages can be updated. Currently we are forced to create a different repository and ship 0.7.x version of sqlalchemy.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'total_CrBal'" org.apache.xmlrpc.XmlRpcException: error

this is a common error for which there can be many causes; I can't diagnose it without a test case.

He also mentioned you have promised this would be fixed in the next release

I have no idea what this is referring to. Existing bugs targeted for the next possible release you can see in https://bitbucket.org/zzzeek/sqlalchemy/issues?status=new&status=open&milestone=0.9.7.

Unfortunately this is not really a bug report as no actionable detail is provided. If you can get me a test case or some reference to what prior bug has been reported, that would help. Reopen when you can get this to me, thanks.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Krishnakant Mane wrote:

I had discussed this before on irc. Any ways, the thing is that I have a lot of stored procedures in my software. The rdbms we are using is postgresql <www.postgresql.org> When I make calls to stored procedures, I have a function called execProc, thanks to a few sqlalchemy guys who helped me write it 2 years ago.

This is the function which does the calling to stored procedures and then returns the result

def execproc(procname, engine, queryParams=[]):
""" Purpose:
executes a named stored procedure and returns the result.
Function takes 3 parameters, procname is a string containing the name of the stored procedure.
engine is the sqlalchemy engine instance through which the query will be executed.
queryParams contains the input parameters in a list form (if any).
The function returns a resultset with columns pertaining to the output parameters of the called stored procedure and number of rows equal to those returned by the return query.
description.
This function takes in the name of the stored procedure to be executed and constructs a query using the bound parameter syntax.
The parameters are provided in a list which is attached to the main query.
We have used the func object from sqlalchemy for executing a stored procedure through this process.
"""
function = getattr(func, procname)
function_with_params = function(queryParams)
return engine.execute(select([literal_column('')]).select_from(function_with_params).execution_options(autocommit=True))

This worked very well with version upto 0.7.8. Now with latest sqlalchemy versions I.E 0.9.x I get the error Praveen had mentioned. Basically the column name is not getting recognised it seems.
The stored procedures as you might be aware have out parameters which can be used by name as in row["colname"] if the out parameter list has one called colname.
But this does not seem to work any more.

[sqlalchemy-bot]

Praveen Arimbrathodiyil (@pravi) wrote:

Krishnakant has provided more details and sample code for reproducing the issue.

You can see the source code for gnukhata at http://www.gnukhata.org/core_engine/

[sqlalchemy-bot]

Changes by Praveen Arimbrathodiyil (@pravi):

• changed status to reopened

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Duplicate of #2972.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: duplicate
• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

SQLAlchemy does not have support for OUT parameters on any backend except Oracle at this time. In order to use OUT parameters now, you need to use the DBAPI function callproc(), see http://initd.org/psycopg/docs/cursor.html#cursor.callproc.

your function should look like this:

def execproc(procname, engine, queryParams=[]):
    connection = engine.raw_connection()
    cursor = connection.cursor()
    try:
        cursor.callproc(procname, queryParams)
        rows = list(cursor.fetchall())
        cursor.close()
        connection.commit()
        return rows
    finally:
        connection.close()

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Note that there has never been support for this (OUT parameters specifically). If your function worked on 0.7 with OUT parameters, it was a cooincidence.

If we continue, please do the following:

1. use correct formatting for code examples, e.g. press the "<>" button around code so that it is legible.

2. please provide a full reproducing sample, that includes a short PG stored procedure with the OUT parameter and example calling form.

[sqlalchemy-bot]

Krishnakant Mane wrote:

here is the stored procedure
create or replace function getVoucherDetails(voucher_code voucher_master.vouchercode%type ,out accountname text, out transactionFlag char(10), out transactionamount numeric(13,2)) returns setof record as $$
begin
return query select account_name,bpchar(typeflag), amount from view_voucherbook where vouchercode = voucher_code and flag = 1;
end;
$$ language plpgsql;

Here is my rpc call in Python..
in my last posting, I have already provided the execproc function which uses sqlalchemy to get the results.

def xmlrpc_getVoucherDetails(self,queryParams,client_id):
    """
    purpose: gets the transaction related details given a vouchercode.
    returns a 2 dymentional list containing rows with 3 columns.takes one parameter QueryParams, which is list containing vouchercode
    description:
    The function used to get the detailed view of a voucher given its vouchercode.
    returns a 2 dymentional list containing rows for a transaction.
    the 3 columns are, accountname, typeflag (Cr/Dr) and amount.
    The function uses the getVoucherDetails stored procedure.
    """
    transactions = dbconnect.execproc("getVoucherDetails",dbconnect.engines[client_id],queryParams)
    transactionRecords = transactions.fetchall()
    voucherDetails = []
    for transactionRow in transactionRecords:
        voucherDetails.append([transactionRow["accountname"],transactionRow["transactionFlag"],'%.2f'%float(transactionRow["transactionamount"])])
    print queryParams
    return voucherDetails

It used to work, meaning the out parameters were getting perfectly mapped in the said previous version of sqlalchemy.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Please understand that I cannot diagnose the error without actually running a program. I don't have a stored procedure with OUT parameters available to me with which to test and PG's docs are scant on how these are used, nor it is clear from psycopg2's documentation how they function, so the only way for me to know is experimentation.

To that end, please provide the source code for the actual getVoucherDetails stored procedure as well as the exact arguments that are being sent to it. I have no idea what the issue is as I have never worked with PG OUT parameters, it is not any feature that was ever supported in SQLAlchemy.

However, as this is not any feature I've ever implemented, I've given you a workaround, and my time is very valuable. I would ask that you please use the workaround given, thanks.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

just as a reference, here's stored procedures with traditional parameters. The literal_column('') format you're using isn't correct, you should have at least an asterisk '*' in there as below.

from sqlalchemy import create_engine, func, select, literal_column

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
c = e.connect()
t = c.begin()

c.execute(
"""
CREATE FUNCTION somefunc(integer) RETURNS integer AS $$
DECLARE
    quantity ALIAS FOR $1;
BEGIN
    quantity := quantity + 50;
    RETURN quantity;
END;
$$ LANGUAGE plpgsql;
""")


result = c.execute(select([func.somefunc(40)]))
assert result.scalar() == 90

result = c.execute(select([literal_column('*')]).select_from(func.somefunc(40)))
assert result.scalar() == 90

[sqlalchemy-bot]

Praveen Arimbrathodiyil (@pravi) wrote:

You can install gnukhata from http://people.debian.org/~praveen/gnukhata/ and run it to reproduce the issue. The complete code is at https://gitlab.com/gnukhata/core_engine/blob/master/gnukhataserver/rpc_transaction.py

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

that file contains no source code for an actual stored procedure. A stored proc looks like this :http://www.postgresqltutorial.com/postgresql-create-function/

You need to provide a simple reproducing test case, it's not my job to install/debug your whole application.

[sqlalchemy-bot]

Praveen Arimbrathodiyil (@pravi) wrote:

create or replace function getVoucherDetails(voucher_code voucher_master.vouchercode%type ,out accountname text, out transactionFlag char(10), out transactionamount numeric(13,2)) returns setof record as $$ begin return query select account_name,bpchar(typeflag), amount from view_voucherbook where vouchercode = voucher_code and flag = 1; end; $$ language plpgsql;

This was already provided earlier by Krishnakant. Since you are already doing your job well, there is no point in wasting your precious time anymore. Have a good day.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Sorry, didn't see it as it wasn't formatted. trying now.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

OK, here is the next test, I need:

1. don't know what "voucher_master.vouchercode%type ", is, I put VARCHAR there.

2. function_with_params = function(queryParams) I have no idea how that could have ever worked, please illustrate

3. literal_column('') - this emits "SELECT FROM ", can never have worked, please illustrate

4. need to know what parameters you're providing in queryparams.

5. please ensure correct formatting!

6. I am sorry this is difficult, but when we get the below program to reproduce your error, that is essentially what you need to provide when you file a bug report in the future. By not providing enough detail, such as basic test cases, it spends lots of additional time on everyone's part.

from sqlalchemy import create_engine, func, select, literal_column

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
c = e.connect()
t = c.begin()

c.execute(
"""
create or replace function getVoucherDetails(
        voucher_code varchar,
         out accountname text,
         out transactionFlag char(10),
        out transactionamount numeric(13,2))
returns setof record as $$

begin return query select
account_name, bpchar(typeflag), amount from view_voucherbook where
vouchercode = voucher_code and flag = 1;

end;

$$ language plpgsql;
""")

c.execute("""
    create table view_voucherbook (
        account_name VARCHAR,
        typeflag INTEGER,
        amount NUMERIC,
        vouchercode VARCHAR,
        flag INTEGER
    )
""")
c.execute("""
    INSERT INTO view_voucherbook(account_name, typeflag, amount,
        vouchercode, flag)
    VALUES ('name', 1, 50, 'code', 1)
""")

def execproc(procname, engine, queryParams=[]):
    function = getattr(func, procname)
    function_with_params = function(queryParams)
    return engine.execute(select([literal_column('*')]).
            select_from(function_with_params).
            execution_options(autocommit=True))

result = execproc("getVoucherDetails", c, queryParams=[])

[sqlalchemy-bot]

Krishnakant Mane wrote:

Thanks, but the code you gave did not work either. I can happyly recall that it was you who had helped me in getting this to work a couple of years back. It kept on working till date with out parameters and now nothing seems to do it.
Another interesting observation is that it worked even before 0.7.5 version.
Kindly let me know what else can I do.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

@krishnakant_mane : "the code you gave did not work either." - which "code", the one that uses psycopg2 callproc? and "did not work", what was the full output of the program?

I can happyly recall that it was you who had helped me in getting this to work a couple of years back.

calling a Postgresql function is very easy. But using one with OUT parameters, I have no idea how to do that. psycopg2 should support it but I have no idea where psycopg2 places the values of these OUT parameters, unless a special value-holding object is being used in the parameters; this would be inside of "queryParams" in your function, but you have yet to show me what exact objects are being placed in this "queryParams" structure.

It kept on working till date with out parameters and now nothing seems to do it.

how do you know the version of psycopg2 is not the issue here?

Kindly let me know what else can I do.

Please copy the test case I have listed at https://bitbucket.org/zzzeek/sqlalchemy/issue/3103/nosuchcolumnerror-could-not-locate-column#comment-11079974, experiment with it locally, and modify it such that it reproduces your error. Do not use any additional libraries, it has to be entirely self contained so that I can run it here. It needs to run on a brand new machine that has only a blank database and psycopg2 installed. thanks.

[sqlalchemy-bot]

Praveen Arimbrathodiyil (@pravi) wrote:

how do you know the version of psycopg2 is not the issue here?

Because we use the same version of psycopg2 with both versions of sqlalchemy (0.7.8 and 0.9.6) and gnukhata works with 0.7.8, but fails with 0.9.6.

[sqlalchemy-bot]

Praveen Arimbrathodiyil (@pravi) wrote:

update title to mention out parameters

[sqlalchemy-bot]

Changes by Praveen Arimbrathodiyil (@pravi):

• changed title from "NoSuchColumnError: "Could not locate column in row" to "NoSuchColumnError: "Could not locate column in row"

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed milestone from "0.9.7" to "0.9.8"

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed milestone from "0.9.8" to "0.9.7"