mogrify/query for adodbapi

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

mogrify/query for adodbapi

William Dode
Hi,

With psycopg2 I use "mogrify" or the "query" attribute to retrieve the
exact sql query. It's very usefull for debugging.

Could it be possible to have the same with adodbapi ? I mean, is it
possible with ado to retrieve the sql query ?

How do you do with other databases ?

bye

thanks to read my bad english...

--
William Dodé - http://flibuste.net
Informaticien Indépendant

_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: mogrify/query for adodbapi

Vernon D. Cole
This sounds like a handy feature.  Recent releases of adodbapi allow the programmer to specify the paramstyle she wishes to use. If she uses 'qmark' (the default) her query is used unchanged. If she uses 'named' or 'format', then her query is converted to 'qmark' before being used.
  The cursor stores an ADODB.Command structure as its .cmd attribute. The (converted) query text is stored in the .cmd.CommandText attribute. There is no storage of the original query, although it might be an optimization to keep it around to avoid re-parsing.

Q) Should a reference to cursor.query return the original query text, or the reformatted version?

Q) What is "mogrify"? Is that a reformatted version?
--
Vernon

On Sun, Feb 13, 2011 at 6:04 AM, William Dode <[hidden email]> wrote:
Hi,

With psycopg2 I use "mogrify" or the "query" attribute to retrieve the
exact sql query. It's very usefull for debugging.

Could it be possible to have the same with adodbapi ? I mean, is it
possible with ado to retrieve the sql query ?

How do you do with other databases ?

bye

thanks to read my bad english...

--
William Dodé - http://flibuste.net
Informaticien Indépendant

_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig


_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: mogrify/query for adodbapi

William Dode
On 13-02-2011, Vernon Cole wrote:

> --===============0804799855==
> Content-Type: multipart/alternative; boundary=0016e644df66158a5b049c2cec40
>
> --0016e644df66158a5b049c2cec40
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: quoted-printable
>
> This sounds like a handy feature.  Recent releases of adodbapi allow the
> programmer to specify the paramstyle she wishes to use. If she uses 'qmark'
> (the default) her query is used unchanged. If she uses 'named' or 'format',
> then her query is converted to 'qmark' before being used.
>   The cursor stores an ADODB.Command structure as its .cmd attribute. The
> (converted) query text is stored in the .cmd.CommandText attribute. There i=
> s
> no storage of the original query, although it might be an optimization to
> keep it around to avoid re-parsing.
>
> Q) Should a reference to cursor.query return the original query text, or th=
> e
> reformatted version?

Same as mogrify

>>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)", (42, 'bar'))
>>> cur.query
"INSERT INTO test (num, data) VALUES (42, E'bar')"


>
> Q) What is "mogrify"? Is that a reformatted version?

mogrify(operation[, parameters])¶

    Return a query string after arguments binding. The string returned is exactly the one that would be sent to the database running the execute() method or similar.

    >>> cur.mogrify("INSERT INTO test (num, data) VALUES (%s, %s)", (42, 'bar'))
    "INSERT INTO test (num, data) VALUES (42, E'bar')"

I use to log it and some times use it to copy and paste.


--
William Dodé http://flibuste.net
Informaticien Indépendant

_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: mogrify/query for adodbapi

M.-A. Lemburg
William Dode wrote:

> On 13-02-2011, Vernon Cole wrote:
>> --===============0804799855==
>> Content-Type: multipart/alternative; boundary=0016e644df66158a5b049c2cec40
>>
>> --0016e644df66158a5b049c2cec40
>> Content-Type: text/plain; charset=ISO-8859-1
>> Content-Transfer-Encoding: quoted-printable
>>
>> This sounds like a handy feature.  Recent releases of adodbapi allow the
>> programmer to specify the paramstyle she wishes to use. If she uses 'qmark'
>> (the default) her query is used unchanged. If she uses 'named' or 'format',
>> then her query is converted to 'qmark' before being used.
>>   The cursor stores an ADODB.Command structure as its .cmd attribute. The
>> (converted) query text is stored in the .cmd.CommandText attribute. There i=
>> s
>> no storage of the original query, although it might be an optimization to
>> keep it around to avoid re-parsing.
>>
>> Q) Should a reference to cursor.query return the original query text, or th=
>> e
>> reformatted version?
>
> Same as mogrify
>
>>>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)", (42, 'bar'))
>>>> cur.query
> "INSERT INTO test (num, data) VALUES (42, E'bar')"
>
>
>>
>> Q) What is "mogrify"? Is that a reformatted version?
>
> mogrify(operation[, parameters])¶
>
>     Return a query string after arguments binding. The string returned is exactly the one that would be sent to the database running the execute() method or similar.
>
>     >>> cur.mogrify("INSERT INTO test (num, data) VALUES (%s, %s)", (42, 'bar'))
>     "INSERT INTO test (num, data) VALUES (42, E'bar')"
>
> I use to log it and some times use it to copy and paste.

Since database adapater usually try to avoid merging the parameters
into the query string before sending them off to the server and
instead send the query and parameters separately (which is more
efficient and also allows caching of access plans on the server
side based on the query string), wouldn't it be better to log
both the query string and a Python repr() of the parameters
instead ?

That's how we have do such logging in our code and it works nicely.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Feb 14 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: mogrify/query for adodbapi

William Dode
On 14-02-2011, M.-A. Lemburg wrote:

> William Dode wrote:
>> On 13-02-2011, Vernon Cole wrote:
>>> --===============0804799855==
>>> Content-Type: multipart/alternative; boundary=0016e644df66158a5b049c2cec40
>>>
>>> --0016e644df66158a5b049c2cec40
>>> Content-Type: text/plain; charset=ISO-8859-1
>>> Content-Transfer-Encoding: quoted-printable
>>>
>>> This sounds like a handy feature.  Recent releases of adodbapi allow the
>>> programmer to specify the paramstyle she wishes to use. If she uses 'qmark'
>>> (the default) her query is used unchanged. If she uses 'named' or 'format',
>>> then her query is converted to 'qmark' before being used.
>>>   The cursor stores an ADODB.Command structure as its .cmd attribute. The
>>> (converted) query text is stored in the .cmd.CommandText attribute. There i=
>>> s
>>> no storage of the original query, although it might be an optimization to
>>> keep it around to avoid re-parsing.
>>>
>>> Q) Should a reference to cursor.query return the original query text, or th=
>>> e
>>> reformatted version?
>>
>> Same as mogrify
>>
>>>>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)", (42, 'bar'))
>>>>> cur.query
>> "INSERT INTO test (num, data) VALUES (42, E'bar')"
>>
>>
>>>
>>> Q) What is "mogrify"? Is that a reformatted version?
>>
>> mogrify(operation[, parameters])¶
>>
>>     Return a query string after arguments binding. The string returned is exactly the one that would be sent to the database running the execute() method or similar.
>>
>>     >>> cur.mogrify("INSERT INTO test (num, data) VALUES (%s, %s)", (42, 'bar'))
>>     "INSERT INTO test (num, data) VALUES (42, E'bar')"
>>
>> I use to log it and some times use it to copy and paste.
>
> Since database adapater usually try to avoid merging the parameters
> into the query string before sending them off to the server and
> instead send the query and parameters separately (which is more
> efficient and also allows caching of access plans on the server
> side based on the query string), wouldn't it be better to log
> both the query string and a Python repr() of the parameters
> instead ?

I would like to see the query after the binding. For example with
msaccess "insert into t (a), (?)", (True,) will insert -1 with
postgresql it will insert 1

Also i would like to can copy and paste the query for debugging. For
example with msaccess, if a field is misswritted the error will not show
wich field is it. If i copy-paste it on msaccess it will say me wich
field is it.

I thought maybe ado could show me this. If not i will do it by hand for
debugging and of course use the params for the real query.

>
> That's how we have do such logging in our code and it works nicely.
>


--
William Dodé - http://flibuste.net
Informaticien Indépendant

_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: mogrify/query for adodbapi

Vernon D. Cole

There may be some help for you. ADO will read and write ACCESS data tables, so you can migrate your more challenging data communications tasks to Python and eventually replace your ACCESS application with something a bit more trustworthy.

Vernon Cole
(sent from my 'droid phone)

On Feb 14, 2011 7:49 AM, "William Dode" <[hidden email]> wrote:
> On 14-02-2011, M.-A. Lemburg wrote:
>> William Dode wrote:
>>> On 13-02-2011, Vernon Cole wrote:
>>>> --===============0804799855==
>>>> Content-Type: multipart/alternative; boundary=0016e644df66158a5b049c2cec40
>>>>
>>>> --0016e644df66158a5b049c2cec40
>>>> Content-Type: text/plain; charset=ISO-8859-1
>>>> Content-Transfer-Encoding: quoted-printable
>>>>
>>>> This sounds like a handy feature. Recent releases of adodbapi allow the
>>>> programmer to specify the paramstyle she wishes to use. If she uses 'qmark'
>>>> (the default) her query is used unchanged. If she uses 'named' or 'format',
>>>> then her query is converted to 'qmark' before being used.
>>>> The cursor stores an ADODB.Command structure as its .cmd attribute. The
>>>> (converted) query text is stored in the .cmd.CommandText attribute. There i=
>>>> s
>>>> no storage of the original query, although it might be an optimization to
>>>> keep it around to avoid re-parsing.
>>>>
>>>> Q) Should a reference to cursor.query return the original query text, or th=
>>>> e
>>>> reformatted version?
>>>
>>> Same as mogrify
>>>
>>>>>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)", (42, 'bar'))
>>>>>> cur.query
>>> "INSERT INTO test (num, data) VALUES (42, E'bar')"
>>>
>>>
>>>>
>>>> Q) What is "mogrify"? Is that a reformatted version?
>>>
>>> mogrify(operation[, parameters])¶
>>>
>>> Return a query string after arguments binding. The string returned is exactly the one that would be sent to the database running the execute() method or similar.
>>>
>>> >>> cur.mogrify("INSERT INTO test (num, data) VALUES (%s, %s)", (42, 'bar'))
>>> "INSERT INTO test (num, data) VALUES (42, E'bar')"
>>>
>>> I use to log it and some times use it to copy and paste.
>>
>> Since database adapater usually try to avoid merging the parameters
>> into the query string before sending them off to the server and
>> instead send the query and parameters separately (which is more
>> efficient and also allows caching of access plans on the server
>> side based on the query string), wouldn't it be better to log
>> both the query string and a Python repr() of the parameters
>> instead ?
>
> I would like to see the query after the binding. For example with
> msaccess "insert into t (a), (?)", (True,) will insert -1 with
> postgresql it will insert 1
>
> Also i would like to can copy and paste the query for debugging. For
> example with msaccess, if a field is misswritted the error will not show
> wich field is it. If i copy-paste it on msaccess it will say me wich
> field is it.
>
> I thought maybe ado could show me this. If not i will do it by hand for
> debugging and of course use the params for the real query.
>
>>
>> That's how we have do such logging in our code and it works nicely.
>>
>
>
> --
> William Dodé - http://flibuste.net
> Informaticien Indépendant
>
> _______________________________________________
> DB-SIG maillist - [hidden email]
> http://mail.python.org/mailman/listinfo/db-sig


_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: mogrify/query for adodbapi

Federico Di Gregorio
In reply to this post by William Dode
[snip]
On 14/02/11 15:49, William Dode wrote:

> I would like to see the query after the binding. For example with
> msaccess "insert into t (a), (?)", (True,) will insert -1 with
> postgresql it will insert 1
>
> Also i would like to can copy and paste the query for debugging. For
> example with msaccess, if a field is misswritted the error will not show
> wich field is it. If i copy-paste it on msaccess it will say me wich
> field is it.
>
> I thought maybe ado could show me this. If not i will do it by hand for
> debugging and of course use the params for the real query.
If the python library sends the query+parameters you can't because it is
the backend that will build the final query. Currently psycopg does all
argument escaping and quoting on the client so we have the final query
ready for inspection.

Anyway, if the client library sends query+parameters to the backend it
is possible to build a "fake" query for logging/debugging purpuse. For
example, we plan to move psycopg .executemany() to prepared queries and
even in that case .mogrify() will return the full query, but in that
case it will be not the one we sent to the db.

federico

--
Federico Di Gregorio                                       [hidden email]
 There's no greys, only white that's got grubby. I'm surprised you
  don't know that. And sin, young man, is when you treat people as
  things. Including yourself.                       -- Granny Weatherwax


_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig

signature.asc (270 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: mogrify/query for adodbapi

William Dode
On 14-02-2011, Federico Di Gregorio wrote:

> This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
> --===============1638910873==
> Content-Type: multipart/signed; micalg=pgp-sha1;
> protocol="application/pgp-signature";
> boundary="------------enig8B948CD4F063A9F68C5802DF"
>
> This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
> --------------enig8B948CD4F063A9F68C5802DF
> Content-Type: text/plain; charset=UTF-8
> Content-Transfer-Encoding: quoted-printable
>
> [snip]
> On 14/02/11 15:49, William Dode wrote:
>> I would like to see the query after the binding. For example with=20
>> msaccess "insert into t (a), (?)", (True,) will insert -1 with=20
>> postgresql it will insert 1
>>=20
>> Also i would like to can copy and paste the query for debugging. For=20
>> example with msaccess, if a field is misswritted the error will not sho=
> w=20
>> wich field is it. If i copy-paste it on msaccess it will say me wich=20
>> field is it.
>>=20
>> I thought maybe ado could show me this. If not i will do it by hand for=
>=20
>> debugging and of course use the params for the real query.
>
> If the python library sends the query+parameters you can't because it is
> the backend that will build the final query. Currently psycopg does all
> argument escaping and quoting on the client so we have the final query
> ready for inspection.
>
> Anyway, if the client library sends query+parameters to the backend it
> is possible to build a "fake" query for logging/debugging purpuse. For
> example, we plan to move psycopg .executemany() to prepared queries and
> even in that case .mogrify() will return the full query, but in that
> case it will be not the one we sent to the db.

I understand, i will do myself a fake query for logging/debugging...

--
William Dodé - http://flibuste.net
Informaticien Indépendant

_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig