Re: callproc, string formatting

I think I fatfingered sending the reply I referred to, so it didn't exist on
the list. Either that or it's being moderated and hasn't made it yet. Here
it is, cut-n-pasted for your convenience:

On Wed, Nov 25, 2009 at 1:26 PM, Tim Roberts <[email protected]> wrote:

> Brian Jones wrote:
> >
> > 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.
>
> Well, that is exactly the right way to do it.
>

Is it? I got this to work in the interim after a lot of head banging:
--------------------------- snip ---------------------------
callproc_params = [self.fname, self.lname, self.dob, self.city, self.state,
self.zipcode]


curs.callproc('myapp.new_user', callproc_params)
--------------------------- /snip ---------------------------

This solution has zero string formatting or manual quoting, which is nice.
My date of birth field was initially a string, but I've altered that to make
it a datetime object, so psycopg2 seems to correctly format that for the db
as well.

In the earlier solution, *none* of the strings were quoted :(

Thanks very much for the input. If using callproc is the wrong way to call a
server side function in pgsql, please let me know. PEP 249 would seem to
indicate that it is.....?

brian

On Wed, Nov 25, 2009 at 2:47 PM, Brian Jones <[email protected]> wrote:

> 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
>



-- 
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