[DB-SIG] any 2.0-compliant packages for postgresql?

Nathan Clegg nathan@geerbox.net
Wed, 27 Jun 2001 12:52:07 -0700


Sorry that I misunderstood.  Most get around this problem by setting the
type of the paramater at preparation time.  This is an optional step that is
usually only done when the type is custom or a LOB.  But is there any kind
of value you wouldn't want to quote except for numbers?  Is there a
user-defined type that could not be placed in either of two categories:
"number" and "not necessarily a number" ?


-----Original Message-----
From: db-sig-admin@python.org [mailto:db-sig-admin@python.org]On Behalf
Of Federico Di Gregorio
Sent: Wednesday, June 27, 2001 8:31 AM
To: Nathan Clegg
Cc: Python DB-SIG Mailing List
Subject: RE: [DB-SIG] any 2.0-compliant packages for postgresql?


On 27 Jun 2001 08:03:12 -0700, Nathan Clegg wrote:
> Ahhhh...this is probably where our experience differs then.  My experience
> is that bound paramaters may only be used for literal values.  You cannot
> use a paramater to specify the name of a table, column, type, or any other
> object.  This is enforced, for example, by Oracle when a query with bound

i am *not* using the parameter to specify "the name of a table, column,
type, or any other object". it specifies a very simple literal value (a
string) to be inserted into the db. i am using the pyargs type of
sustitution but if you prefer i can change my example as follows:

    curs.execute("INSERT INTO test VALUES (?)", ['some text'])

that does not change the problem i outlined (see discussion below).

ciao,
federico

> paramaters is prepared by the engine.  I don't think postgresql supports
> bound paramaters internally, does it?  So it's always the package,
library,
> or module that is inserting the values directly, thus some of the
> limitations don't apply.
>
> In short, "real" bound paramaters can't be used for the purposes you
> described.  Since postgresql doesn't support "real" bound paramaters, the
> situation is less obvious.
>
>
>
> -----Original Message-----
> From: db-sig-admin@python.org [mailto:db-sig-admin@python.org]On Behalf
> Of Federico Di Gregorio
> Sent: Wednesday, June 27, 2001 7:38 AM
> To: Nathan Clegg
> Cc: Python DB-SIG Mailing List
> Subject: RE: [DB-SIG] any 2.0-compliant packages for postgresql?
>
>
> On 27 Jun 2001 07:25:49 -0700, Nathan Clegg wrote:
> > > Q: psycopg correctly put strings obtained by the Date and Time
> > > constructors inside quotes  ('') as requested by the api. it should do
> > > the same (and maybe escape) normal strings? if that's the case don't
we
> > > need a String constructor? how is the module supposed to know when a
> > > string is real string and need quoting and when i am passing it just
as
> > > convenience but it really is a number? example...
> >
> > This is the part I have issue with.  The api spec does require that
normal
> > strings be escaped and quoted.  In regards to passing numbers as strings
> for
> > convenience, I would suggest doing away with it.  If you mean it to be a
> > number, send a number, and furthermore specify %(name)d instead of
> %(name)s.
> > I appreciate conveniences, but I think this introduces the kind of
> ambiguity
> > that python's specification set out to squash in the first place.
> > Furthermore, all of the databases I have personally used have no problem
> > casting strings to numbers where required.  That is, if you pass
> postgresql
> > (or oracle, or mysql...) a quoted string '768' to compare against a
number
> > field, it with cast it to a number for you and get you the desired
result.
> > The module doesn't really need to implement this convenience casting
> because
> > the database engine already does.
>
>
> so, if i try to do as follows:
>
> curs.execute("CREATE TABLE test (a text)")
> curs.execute("INSERT INTO test VALUES (%(a)s)", {'a':'some text...'})
>
> the method call fails miserably because the generated SQL is:
>
>     INSERT INTO test VALUES (some text...)
>
> without quotes. footnote 5 requires argument binding to prodive quotes
> and escape sequences but how can the module know is the given string
> will be a string in the db too and requires quoting or it is something
> different (let's say a user-defined type in psogresql) that does not
> require it?
>
> anyway, your answer at least means that psycopg isn't more broken that
> all other drivers... ;)
>
> ciao,
> federico
>
> --
> Federico Di Gregorio
> MIXAD LIVE Chief of Research & Technology              fog@mixadlive.com
> Debian GNU/Linux Developer & Italian Press Contact        fog@debian.org
>    God is real. Unless declared integer. -- Anonymous FORTRAN programmer
>
>
> _______________________________________________
> DB-SIG maillist  -  DB-SIG@python.org
> http://mail.python.org/mailman/listinfo/db-sig
>



--
Federico Di Gregorio
MIXAD LIVE Chief of Research & Technology              fog@mixadlive.com
Debian GNU/Linux Developer & Italian Press Contact        fog@debian.org
  The devil speaks truth much oftener than he's deemed.
                                   He has an ignorant audience. -- Byron


_______________________________________________
DB-SIG maillist  -  DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig