Hello all,
I have an interesting problem for which I am sure some simple solution
must be existing.
I have made a Python function which I will paist below.
Basically what the function does is that it takes 3 parameters, namely
the name of a stored procedure, engine instance and a set of parameters.
This function is used to make calls to postgresql based stored
procedures in a modular way.
The function is kept central and all my modules just pass the necessary
parameters and leave it to the function to do the rest.
I got it working perfectly, except that I don't know how to handle
special characters when constructing the query that makes a call to a
stored procedure. So if I have an insert query which has a value with a
single quote ('), it crashes.
As you will observe in the function, it takes the arguements from a list
called queryParams and constructs that part of the query that takes
input arguements for inserts or for the where clause during select etc.
So in those input parameters if any special character appears, the said
stored procedure naturally fails.
following is the exact function.
Some one please point me out what the fundamental mistake is and
probably send in a corrected version.
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).
description:
First it starts building a query string that commonly begins with
the common select * from syntax that is needed for calling a stored
procedure.
The code then goes to check if one or more parameters are
supplied. If yes then
a for loops runs that concatinate the parameters inside ()
During this process it checks the datatype of each supplied
parameter to stringify any parameter or keep it as integer.
This is done using the %s, %d and %f place holders.
After the query is built using the user input that consisted of the
proc name and parames, it executes the same using the supplied engine
instance.
The result of the execution contains the rows returned by the
stored procedure that was called.
"""
listCounter = 0
if len(queryParams) == 0:
queryString = "select * from %s() " % (procname)
else:
queryString = "select * from %s(" % (procname)
for param in queryParams:
if type(param) == str:
queryString = queryString + "'%s'" % (param)
if type(param) == int:
queryString = queryString + "%d" % (param)
if type(param) == float:
queryString = queryString + "%.2f" % (param)
if type(param) == NoneType:
queryString = queryString + "None"
if listCounter < (len(queryParams) - 1):
queryString = queryString + ","
listCounter = listCounter + 1
queryString = queryString + ")"
print queryString
res =
engine.execute(text(queryString).execution_options(autocommit=True))
return res
Thanks for help in advance.
Happy hacking.
Krishnakant.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
