HOW TO HANDLE SPECIAL CHARACTERS WITH ENGINE.EXECUTE
Krishnakant Mane
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.
Michael Bayer
On Feb 11, 2012, at 8:02 AM, Krishnakant Mane wrote:
> 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.
This is because the function is not using bound parameters. Dealing with individual datatypes and how they are formatted to the database is something you should let the DBAPI handle.
SQLAlchemy includes the capability to call functions built in via the "func" parameter. Your execproc could be written as:
from sqlalchemy import create_engine, func
engine = create_engine('mysql://root@localhost/test', echo=True)
def execproc(procname, engine, queryParams=[]):
function = getattr(func, procname)
function_with_params = function(*queryParams)
return engine.execute(function_with_params.execution_options(autocommit=True))
print execproc("concat", engine, ["dog", " ", "cat"]).scalar()
Krishnakant Mane
I will see if this works perfectly with postgresql.
I had tryed func before but did not get any success.
May be this time it will work.
Happy hacking.
Krishnakant.
Krishnakant Mane
> function = getattr(func, procname)
> function_with_params = function(*queryParams)
> return engine.execute(function_with_params.execution_options(autocommit=True))
>
Hi Michael,
I tryed this code with postgresql (psycopg2 ).
I have a group table with the fields.
groupcode integer, groupname text and groupdesc text.
When I pass in a procedure name called get groups with all the above
listed columns as output parameters (actually the procedure returns
setof rows), I get the could not locate column on the groupcode field.
I had previously mailed you with a longish function called execProc
which you corrected and maild back.
With that long function without func, I never had this problem.
could you please point out what could have gone wrong?
Note that I am not paisting the function here as it is exactly the same
as you had provided.
I will however paist the line which gave me the error.
I will infact give you the code concerning the function where your
version of execProc was called.
#note that we have a dbconnect module containing an array of engines and
also the execProc method.
res = dbconnect.execproc("getAllGroups",dbconnect.engines[client_id])
#since we know that the function getAllGroups exists and that it returns
the exact 3 fields from the groups table,
#we are shure we have some records to process.
#We will loop through the generated resultset in res
#We wish to make a 2 dymentional list of rows and columns to be
transfered over an xmlrpc connection.
result = []
for row in res:
result.append([row["groupcode"],row["groupname"],row["groupdesc"]])
return result
I know for sure that the names of the fields used inside row[] are
absolutely correct, and the code actually works when I use the old
execProc which did not have all the sqlalchemy's func trickery.
Could you please explain?
happy hacking.
Krishnakant.
Michael Bayer
On Feb 12, 2012, at 5:22 AM, Krishnakant Mane wrote:
> On 11/02/12 21:10, Michael Bayer wrote:
>> def execproc(procname, engine, queryParams=[]):
>> function = getattr(func, procname)
>> function_with_params = function(*queryParams)
>> return engine.execute(function_with_params.execution_options(autocommit=True))
>>
>
>
> Hi Michael,
> I tryed this code with postgresql (psycopg2 ).
>
> I have a group table with the fields.
> groupcode integer, groupname text and groupdesc text.
>
> When I pass in a procedure name called get groups with all the above listed columns as output parameters (actually the procedure returns setof rows), I get the could not locate column on the groupcode field.
OK sorry, you want individual columns from the function which means "select *" from it. Here is that, using a function from postgresql's website:
from sqlalchemy import create_engine, func, select, literal_column
engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True)
engine.execute("""
CREATE OR REPLACE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL;
""")
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)
)
for row in execproc("dup", engine, [42]):
print row.f1, row.f2
Krishnakant Mane
Firstly at the outset, let me say that SQL Alchemy is one of the best
and most impressive library I ever saw in my career.
I lead a project called GNUKhata, www.gnukhata.org which is a free and
open source software aimed at chartered accountants, small to medium
business enterprises and retail shops.
The project is government funded and we have enough desire and funds to
make it the most popular and ubiquitous software for accounting as
firefox is to internet.
I wish to tell you that SA is the power house behind our core engine
that uses postgresql as the database server.
SA has made our work so easy and sql has become bliss with it.
I infact wish to add this projject as a testimony for your great work.
We are soon to launch the software and it is going to be a great success
given the marketing we are doing and the social projects we wish to
undertake with it.
Now coming back to your function.e
You see, I wish to use this execProc function for executing all the
stored procedureshen including inserts, updates, deletes and selects.
With regard to the latest revision you sent for the function,
I wish to know if I can use it for all the mentioned purposes?
For example if I have a table already in the database called groups with
groupcode, groupname and groupdesc as my fields, can I use this version
of execProc to access a stored procedure related to the table?
Suppose the stored procedure has 2 out parameters, groupname and groupdesc.
can I execute this procedure, say getGroups() through this execProc
function.
Then, can I do a fetchall and then,
for row in rows:
print row["groupname"] ...
and so on?
I think this might be pritty obvious, but the example you posted talked
about creating the table etc. So just wished to get it cleared.
Happy hacking.
Krishnakant.
Krishnakant Mane
Mikeal, I am sorry, I was half asleep when I went through the code,
This week long sprint on our project has tired me out.
I think its pritty clear now.
(unless you would love to explain the last 2 llines for clearity sake ).
Happy hacking.
Krishnakant.
On 12/02/12 22:01, Michael Bayer wrote:
Michael Bayer
def execproc(procname, engine, queryParams=[]):
conn = engine.connect()
try:
trans = conn.begin()
result = conn.execute(
"SELECT * FROM %s(%s)" % (
procname,
", ".join("%s" for arg in queryParams),
), queryParams
)
trans.commit()
return list(result)
finally:
conn.close()
still another, using the DBAPI directly. This uses only psycopg2 and the Python standard library:
import psycopg2
import collections
def execproc(procname, queryParams=[]):
conn = psycopg2.connect(user="scott", password="tiger",
host="localhost", database="test")
cursor = conn.cursor()
cursor.execute(
"SELECT * FROM %s(%s)" % (
procname,
", ".join("%s" for arg in queryParams),
), queryParams
)
conn.commit()
result = list(cursor)
conn.close()
tup = collections.namedtuple("row", [d[0] for d in cursor.description])
return [tup(*row) for row in result]
All three versions of this will execute any psycopg2 stored procedure, including ones that INSERT, UPDATE, or DELETE and return the results as a named result set. The result rows here are not quite the same thing as "out" parameters which are an Oracle concept but fortunately this is much easier to do than Oracle "out" parameters.
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
Krishnakant Mane
Excelent work, you are a real artist.
Just wished to know what is the trans object, and do i need to use a
connection object for the thing to work?
I mean, do I need the conn = engine.connect() line?
I already have a set of live engines so I never make an extra connection
in my execProc method, as you must have seen in my very first email with
explanations.
Thanks a million for the splendid work and your most valued help.
happy hacking.
Krishnakant.
Michael Bayer
Transaction is a SQLAlchemy object that represents the scope of the "transaction" within the DBAPI. The DBAPI always presents a transaction, that's why the pure DBAPI version calls conn.commit() at the end.
You could also say conn = engine.connect().execution_options(autocommit=True).
Krishnakant Mane
> Transaction is a SQLAlchemy object that represents the scope of the "transaction" within the DBAPI. The DBAPI always presents a transaction, that's why the pure DBAPI version calls conn.commit() at the end.
>
So does that mean I will have to use a connection object and not do the
execution with just engine?
Note that the engine parameter I pass to the execProc is already
connected to a database.
So do I need to create an additional connection object?
I thought that the engine had its own implesit connection which it uses
for executing the functions or any sql for that matter.
> You could also say conn = engine.connect().execution_options(autocommit=True).
>
Ah, meaning the connection object is just a formal requirement is it?
Happy hacking.
Krishnakant.