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

Federico Di Gregorio fog@mixadlive.com
27 Jun 2001 17:30:56 +0200

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


> 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