Re: callproc, string formatting

Hi Eric,

See the last reply I sent -- maybe the solution I came up with can help
simplify your code.

HTH.
brian

On Wed, Nov 25, 2009 at 2:42 PM, Eric Chamberlain <
Eric.Chamberlain-/Guk6kGqSwklJZD/[email protected]> wrote:

>  I ran into this problem a few months back.  I use a prepared statement
> mix-in to get the job done as I wasn't able to get psycopg2 to escape
> strings as it should (maybe there's an API call I'm missing?).  Here is a
> link to a mix-in that got me started.  I made heavy modifications to it to
> make it work for my needs, but at least it's one option.
>
>  http://code.activestate.com/recipes/576698/
>
>  Other things to consider are:
> > Is the proc in a schema that your user doesn't have in their search path?
>  If so, put the schema name in front of the proc name.
> > Does that proc have two instances of itself with the same number of args?
>  If so, you may need to explicitly cast the arguments being passed to the
> function.  for instance, replace %(state)s with %(state)s::VARCHAR or
> whatever that functions parameter is for that proc.
> > Does the proc exist in the database?  I know it's a dumb question, but
> maybe it got removed because of a drop cascade or some other such nonsense.
>
>   Eric Chamberlain
>
>
>  On Nov 25, 2009, at 8:23 AM, Brian Jones wrote:
>
> Hi all,
>
> I haven't used psycopg2 since 2006 or so, and didn't use it to call server
> side functions, so I'm a bit out of phase and could use some clarification:
>
> I need to call a server side function called 'myapp.new_user'. My first
> attempt looked something like this:
>
> --------------------------- snip ---------------------------
> qdict = {'fname': self.fname, 'lname': self.lname, 'dob': self.dob, 'city':
> self.city, 'state': self.state, 'zip': self.zipcode}
>
> sqlcall = """SELECT * FROM myapp.new_user( %(fname)s, %(lname)s,
> %(dob)s, %(city)s, %(state)s, %(zip)s""" % qdict
>
> curs.execute(sqlcall)
>
> --------------------------- /snip ---------------------------
> That fails because none of the strings are not quoted at all, ever.
>
> Of course, I foolishly tried going back and putting quotes around all of
> those named string formatting args, and of course that fails when you have
> something like a quoted "NULL" trying to move into a date column (dob is
> allowed to be null). It has other issues too, like being error-prone and a
> PITA, but hey, it was pre-coffee time.
>
> My next attempt looked like this:
> --------------------------- snip ---------------------------
> curs.execute("""SELECT * FROM myapp.new_user( %(fname)s, %(lname)s,
> %(dob)s, %(city)s, %(state)s, %(zip)s""", qdict)
> --------------------------- /snip ---------------------------
>
> Effectively just replacing the "%" with a "," to pass an actual param list
> instead of passing only one arg that contains a string formatting operation.
>
>
> But this winds up with unquoted strings as well.
>
> So I looked into callproc a bit, but my first attempt isn't looking too
> promising:
> --------------------------- snip ---------------------------
> # you can't pass a dict to callproc
> callproc_params = [self.fname, self.lname, self.dob, self.city, self.state,
> self.zip]
> curs.callproc('myapp.new_user', callproc_params)
> --------------------------- /snip ---------------------------
>
> The error I get is:
>
> "function myapp.new_user(unknown, unknown, unknown, unknown, unknown,
> unknown) does not exist
> HINT: No function matches the given name and argument types. You might need
> to add explicit type casts"
>
> Can anyone help me understand the right way to do this, or point me to
> documentation on how to perform this type of operation?
>
> thanks.
> brian
> --
> Brian K. Jones
> Python Magazine  http://www.pythonmagazine.com
> My Blog          http://www.protocolostomy.com
> <ATT00001..c>
>
>
>
> ------------------------------
> Confidentiality Notice: This e-mail may contain proprietary information
> some of which may be legally privileged. It is for the intended recipient(s)
> only. If you believe that it has been sent to you in error, please notify
> the sender by reply e-mail and delete the message. Any disclosure, copying,
> distribution or use of this information by someone other than the intended
> recipient(s) is prohibited and may be unlawful.
>



-- 
Brian K. Jones
Python Magazine  http://www.pythonmagazine.com
My Blog          http://www.protocolostomy.com

_______________________________________________
Psycopg mailing list
Psycopg-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/psycopg