CODEWITHSUNDEEP
pythonsql-serverpostgresql
callum202
callum202

Reputation: 3

Executing SQL Server stored procedures with parameters in Python

I'm trying to execute a stored procedure to query a table, and am having trouble passing through a parameter successfully. 

title=cursor.execute("SELECT titlequery(%s)", str(member_id))`

titlequery() is created by this:

CREATE OR REPLACE FUNCTION public.titlequery(mid text)
  RETURNS text AS
$BODY$

 SELECT title FROM Member WHERE member_id=mid;

$BODY$
  LANGUAGE sql

And the error I'm getting:

modules.pg8000.core.ProgrammingError: ('ERROR', '42P18', 'could not determine data type of parameter $2', 'postgres.c', '1356', 'exec_parse_message', '', '')

Does anyone know what's happening here?

Upvotes: 0

Views: 3718

Answers (1)

Azat Ibrakov
Azat Ibrakov

Reputation: 10971

PEP-249 specifies API for database drivers and pg8000 follows this API as well

pg8000 is a DB-API 2.0 compatible pure-Python interface to the PostgreSQL database engine.

From PEP-249 execute method specification:

Parameters may be provided as sequence or mapping and will be bound to variables in the operation.

We can see at pg8000 sources an example of how to pass parameters to query.

So you should pass a tuple/list of values, not value itself.

Also we should execute query first and then fetch its results using fetchone or fetchmany or fetchall because execute itself returns None (more at sources). I guess OP needs one record, so we're going to use fetchone.

Note: fetchone method returns record represented as tuple, so if we need first coordinate, then we should get it using zero index.

In your case you should try:

parameters = (str(member_id),)  # WARNING: don't miss the comma
cursor.execute("SELECT titlequery(%s)", parameters)
title = cursor.fetchone()[0]

or

parameters = [str(member_id)]
cursor.execute("SELECT titlequery(%s)", parameters)
title = cursor.fetchone()[0]

Example

This worked for me

import pg8000

table_definition = """
    CREATE TABLE Member(
    title VARCHAR(40) NOT NULL,
    member_id VARCHAR(40) NOT NULL)
"""
procedure_definition = """
    CREATE OR REPLACE FUNCTION public.titlequery(mid text)
    RETURNS text AS
    $BODY$
     SELECT title FROM Member WHERE member_id=mid;
    $BODY$
    LANGUAGE sql
"""

connection = pg8000.connect(database='database',
                            user='username',
                            password='password',
                            host='hostname',
                            port=5432)
cursor = connection.cursor()

# Preparation
cursor.execute(table_definition)
cursor.execute(procedure_definition)
values = ('Information', 'A000042553')
cursor.execute('INSERT INTO Member (title, member_id) VALUES (%s, %s)',
               values)

# Reading stored procedure result
parameters = ('A000042553',)
cursor.execute("SELECT titlequery(%s)", parameters)
title = cursor.fetchone()[0]
print(title)

# Cleanup
cursor.close()
connection.close()

gives us

Information

Upvotes: 1

Related Questions