[DB-SIG] DBAPI-2.0 clarifications
brian zimmer
bzimmer@ziclix.com
Sat, 17 Mar 2001 08:28:36 -0600
This is a multi-part message in MIME format.
------=_NextPart_000_0000_01C0AEBC.4338E260
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Federico,
Thanks for asking some of the same questions I have while writing zxJDBC (http://www.ziclix.com/zxjdbc). Below I'll add a bit of
follow up to what you asked.
>
> Scavenging the mail folder uncovered M.-A. Lemburg's letter:
> > Federico Di Gregorio wrote:
> > >
> > > if a db support transactions, how two cursors derived from the same
> > > connection are supposed to see changes done to the db? if cursor A
> > > does an insert, is cursor B supposed to see the change suddenly or
> > > only after a commit() on the connection? imho, it would be better
> >
> > This is left undefined in the DB API spec since database usually
> > have their own specific idea about how to handle such a situation
> > (it is called transaction isolation level in the ODBC docs). The
> > transaction isolation level can be specified on a per connection
> > basis if the ODBC driver supports this.
>
> yes, i know what isolation level is. my question was about cursors
> *derived from the same connection*. now, as you say, isolation level,
> where vailable can be set on a "per connection" basis, so, are two
> cursors created from the *same* connection **required** to see the
> changes immediately or not?
>
I believe they are required to see the changes immediately. Opening two connections is the only way to isolate the transactions.
Essentially, and please correct me if I'm wrong, a cursor is only a statement from a connection and has no transactional ability on
it's own, so the connection must handle them as all together or commit on each one.
> > > the api is not very clear on what a "set" is. it just says that not
> > > all db support "multiple result sets". imho, it is logical that a call
> > > to executemany() (with a SELECT operation) produces multiple sets.
> > > is this the right interpretation or multiple result sets are something
> > > different? what should happen if a SELECT is passed to executemany()
> > > and the driver does not support nextset()?
> >
> > No, .executemany() is there to allow passing multiple rows of
> > arguments to the database in one go. The statement is then executed
> > for each row of arguments binding those arguments to the parameter
> > markers.
>
> exactly as we implemented it in psycopg.
>
> > Multiple result sets refer to the output format and only comes
> > into play for stored procedure which may return multiple result
> > sets. .nextset() then switches to the next available result set.
>
> ok. now, how are multiple SELECTs derived from calling executemany()
> on a SELECT statement supposed to work? all the results should be
> concatenated into a single set? or we can extend .nextset() to cover
> this situation?
I have the same question. Consider this example:
>>> c = db.cursor()
>>> c.execute("select b from x where id = ?", [(3,), (4,), (5,)])
>>> f = c.fetchall()
What is the expected result? In zxJDBC it's implemented as such:
>>> print f
[(172,)]
>>> c.nextset()
>>> f = c.fetchall()
>>> print f
[(728,)]
and so on. Or should it be:
>>> print f
[(172, 728, 827)]
> > > 5/ the description field
> > >
> > > how are the precision and scale field to be interpreted? are the fields
>
> nobody knows what precision and scale are? i'll bet on number of significant
> digits for precision and number of digits to the right of the decimal point
> for scale, but that's *my* interpretation, and i really don't know how to
> extract both from the results the db backend sends to me.
>
> > > mandatory? sometimes obtaining the information in the description is
> > > plain slow (like the display_size, you have to scan every single row
> > > in the result set) or requires accessing system tables. very few programs
> > > use the most esoteric fields (most programs only use type_code and name.)
> > > what about (i know that this will make people flame me) add a new method
> > > to obtain a detailed description and put a shorter description in the
> > > current .description field?
> >
> > These two fields can probably be safely set to None (mxODBC does
> > this), since today 132-column printers and ASCII terminals
> > for formatting result set outputs are not really relevant anymore ;-)
>
> an api specification is there to provide uniformity. the answer
> "These two fields can probably be safely set to None" is, imho, not good
> *unless* the api specifies that the two fields are optional. this is
> why i am asking all that questions and why (in the future) i will probably
> propose a little revision of the API to clarify the obscure points.
>
For what it's worth I set them to None as well.
> thank you again for your answer, ciao,
> federico
>
> p.s. a beginning of the test suite is available in the suite/ subdirectory
> of the psycopg package. the only well-implemented tests are, at now, the
> ones on type singletons (STRING, NUMBER, etc...) but i plan to have a full
> suite in a month or so. the suite will be included in psycopg 0.4.7 but
> if you want to contribute you can already get it from the cvs, see
> http://initd.org/Software/psycopg for more information on our cvs server.
>
I've included my small testing framework as an example of how I test. I utilize PyUnit and can test just about any DB API 2.0
compliant implementation. I essentially read an xml document describing the factory method which opens the connection and then run
the connection through any number of tests. It works in jython as well as cpython with no additional changes. Try changing the
sample 'test.xml' to your factory method and see if it works, I'd be quite curious. It works perfectly well for mxODBC and zxJDBC
at the moment as both are set in the xml document.
try:
D:\home\development\src\sourceforge\zxJDBC\src\test>python runner.py test.xml
testing [mysql]
checking connection ... ok
checking simple queries with cursor.execute(), no parameters ... ok
------------------------------------------------------------------------------
Ran 2 tests in 0.230s
OK
Stay tuned for a new release of zxJDBC to coincide with some awesome changes to the Jython internals coming out with the 2.1
release. Finn rocked!
thanks,
brian
------=_NextPart_000_0000_01C0AEBC.4338E260
Content-Type: application/x-zip-compressed;
name="test.zip"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="test.zip"
UEsDBBQAAAAIAH0/cSrXTMacVgQAAOMKAAANAAAAc2ltcGxldGVzdC5wea1WW0/bSBR+TqX+h6Oo
FQ5yUycpoIVltSGFNrsU2FxUsQitJuNjMurY450ZQ8yv3zNjk0uBFQ+82ePvfOc713Gz2Xz7Brbh
j9LOVQafmWUzZhDGOXKRCM6soOP+xRC67cghPfrdMN6H+4VFY9t5Gd5Cp93dhW4UdT5GvY+dXejs
7vd6+509mN2LNEUNx4sc3i3tByovtbiZWwgGLWcXwZEWLIO/K/Svtdnv94JLsWhzlf62NJ7MhYFc
qxvNUqDHRCOCUYm9YxoPoFQFcKLSGAtjtZgVFkFYYFn8UWnPkKpYJKU7LLKY3Nk5gkWdGlCJf/ly
NoUvmKFmEi6KmRQcTgXHzKC3Z+TfnZo5xjArvcmJUzGuVcCJImafuwNAQd813KI2LpddT1E7qllD
UBoCZp14DSp3hi1SXIJkdmXbfjYHq1BjEJmnnqucopoTKcV5J6SEGUJhMClk6DkIDd+Hk6/n0wn0
zy7he3806p9NLg8ITc1AX/EWKy6R5lIQNcWmWWZLku8pvh2PBl/Jpn80PB1OLl0UJ8PJ2fF4DCfn
I+jDRX80GQ6mp/0RXExHF+fj4zbAGJ2wKpXPZxoSYksVJTNGy4Q0q+gvqcSGFMoY5uwWqdQcxS3p
Y8Cps15aRamyGx8roVfpPACRQKZsCHdaUOtY9bi+3n5V4xCGGW+HsPMLTJBShXAhGUf4AOPCUfR6
UQhHylgH/dYHiLqdTudDpxfthTAd9+vImm4W376hZCtNyV9w9I1glkdFJqybuZC6Nc0TIV3jGHoT
KTpLLpkxYFy1cEK44MGg7d4GNNetfYdrxJiAQTvNA4MycYeNxiNsew3hAO6hHc/gEPwTV1mG3AYt
z9jgy/NCG6UDb2J16bkbvI0L5NSeQTPWKgfaMpSlaoOI7Kbp0VXElUFOkVTET3FwjcyVZpMFAuG6
37rqQVZIGULGUoRbpvmc6aAbtUIw1lkuj+gk1yJluoQfWDqGVqtSs+5PUM9Q/olbbbqrPNSsLaKV
BRoIOiFsOVS0RQ9Cbr0CZbem7DjKO/EalL2asuso7eI1KD/VlD1HydVrUO7UlJ9eL5e7NeWOV8le
g3Kvptz9qTz10LgbLBW2mopEZEzKZVdzqQzWY+QG0yLTn9VdtjabTw7Xi2Zqjf5pMcuzRzDyeaYy
XOp6GPiVLI220NkmyqvwmCpTNZIJ2uh/YnmsNelvZgpMwecVurmKfc7MP88wVL6ilZwqFU8kyUWz
lqfakq8s58h/DKpoaL+uUdAG9h9drfkS4Pdyo0EbybVE7SIE6hGqPq2cNeSmj7HfxH8VqMtnnFS7
Gv4liKA28pdRpX1ZXNpQlK2c0dWE9C9gajUv3rcEInEksshssN2i3yWV/twljYTYeDtBS1tRyors
IWCJWZC04PAQaK01cZG7WEk7VR20ums+43D7aU90Y7KVM+LYdOY+X0XXm97oZr/qXDtndNnd0HK/
eh9fN+E9BDX8f8fqP1BLAwQUAAAACAABP3Eq3Au2hOIEAADFCgAACQAAAHJ1bm5lci5weY1WbW/b
NhD+3AL9DwehReVBVe1kwxBnGeZmSeOhTQ3bQREEgUFLlM2GEgWSTqL8+t2dXqyiKVB/MX28e+7t
uaODIHj1En6D/yq/NQX8K7xYCydhUcpEZSoRXqF4MpvCQTwkTdZ+PU3HYHdFIW1cVtE9jOJDOBgO
R++Hh+9HhzA6Gh8cjX8/gvWTynNp4eyxhNed+akpK6s2Ww/h6YDshrC2ShTQaP/VmP3zpBKtHuPE
5H93xsutclBas7EiBzxmVkpwJvMPwspjqMwOEoSyMlXOW7XeeQnKgyjS98YyQm5SlVUk3BUpuvNb
CV7a3IHJ+MfHyyv4KDE5oWG2W2uVwCeVyMJJthfon6RuK1NYV2xyTlEsmijg3CAyl+4YpMJ7C/fS
OirlAUM0jhrUCIyFUHgK3oIpyXCAEVeghd/bxj+twT7VFFTB0FtTYlZbBMU8H5TWsJawczLb6Ygx
UBu+TpcXX66WMLm8hq+T+Xxyubw+Rm3kAt7Ke1ljqbzUCqExNysKX2H4DPH5bH56gTaTD9NP0+U1
ZXE+XV6eLRZw/mUOE5hN5svp6dWnyRxmV/PZl8VZDLCQFFhdyp9XGjJEyw0WM5VeKO322V9jix1G
qFPYinuJrU6kusf4BCTIrF/tojbFhnNF7X05j0FlUBgfwYNVSB1vfuwv2+97HMG0SOII/jiCpcRS
SZhpkUh4B4sdQRweDiP4YJwn1c8TgOHBaDR6Nzoc/hnB1WLSZBbQKL56icU2lqipvJcO43CVizCx
IlMbJIojnVRmsFqh5moVIpt3GikkvLcnl6aQg/Grly8wCRLQ8cU3OKm1EbdnsNFmLbQLBxFok/Bp
QOpW+p0tYINlR4TwWw1NV1I7yYhaOI+gNVLskB0+DOJgcPNudNuD+DWneEK4AeWV4MlhEZ2/kLqU
HH6TK5YDYZzUGRZD0MzgptLScbYvSB6TGKOir05WK6G0PpAcS9Opx9hBscXdoGtnjVVP2taucf6D
FcfNQWKLCpl4VqujykTija2ehWjuYk65ELnk2gu7oWBzUYZa5OtUwOMYHm9GtxH8aIrKu1wW3rFp
4x6t2841es+Z5hJHPO23W5SlrsIGBFuOgTxneCerB2NTt0+bC9s0hvMY91B7Pbihy9vObCvc6ldN
Y1JGz2FTp5Yoc36BfkISXF6psX1+NBKsUHPqgsnFnVw5GtaWYVo8PT1HNKrvnp/hdwrcBRRcYpTk
JVMat3fXyAjUCddSbQrca9hX2jO0r1XUjXuMzSP80xYnbEIJkq1M7oJ6REmz5UkvVDZgBVqegp8C
0qyZLeI9RXx7bm44A5bzqZF2PeKb7hffKm4h8wzpEvQmIqgrRQDfz5Hv/+51usudEueVGXLYe47h
X42mMapI9C6V+0W3T7Xf43EbY1iXmI3ofRIxcYiLXgvbYEurCt87QkAxKHwibt642wDe4AMdd3Na
u10TjuABaTyyT3K5jo0jf/x9coJ7m41bLTZPyHwd91pEH6YhVovT6RGzXSHJzRC3QYI7ATfnGic1
7tGPP716PjKZ6jkJBzHXkdBq5W6fP5e+u1Nl+Xz++EjRuNGv1Yqye7ta5UIVq9VbgktoCfN7FZ/y
10xYR/7pLs6kTEP8c4KRVI522D2vtwB5E1sp0voVIr40YSdtU1le59BaHoxvUfo/UEsDBBQAAAAI
AMs+cSr9T4RTiwQAAI0NAAAIAAAAdGVzdC54bWztVltP4zgUfgaJ/3A2mgdYtWkKYkcLLbOlwNAR
l24vQjwhN3FagxNnbadt+PV77KRN6bTASrtv+1I1x993vnPzpfFtHnGYUqmYiJtO3fWcb2d7u3u7
jV+q1b1d+BV+ZHoiYrggmoyIotBPqM9C5hONDGh1O3DoegZp0V86wQloqrSLfitTqLvHcOh59Zp3
VKsfg+edeF9Pjj0YvbIoohIu5wl8WbLbIskkG0807LcPDA+BkpEYCnSjoP3xynzO5q4vorMleTBh
ChIpxpJEgH9DSSkoEeoZkfQUMpGCj64kDZjSko1STYFpIHFQE9J6iETAwswY0zhAOT2hmIuMFIjQ
fny/G8J3GlNJOHTTEWc+3DCfxopaPkF9Y1UTGsAos5QrE0W/iAKuBHq2lTsFynBdLmoPh9ZFIVR4
rYCQsE+0CV6CSAzxACPOgBNdct2tNShTDYDF1vVEJJjVBJ1injPGOYwopIqGKa9YH4iGh87g+n44
gNbdIzy0er3W3eDxFNE4CrhKpzT3xaKEM3SNuUkS6wzDty5uL3vta+S0zjs3ncGjyeKqM7i77Pfh
6r4HLei2eoNOe3jT6kF32Ove9y9dgD41geWl3F5pCNFbJLCYAdWEcVVm/4gtVhghD2BCphRb7VM2
xfgI+DhZn+0iF/HY5orospynwEKIha7ATDIcHS1+7q/llz2uQCf23Qoc/w4DiqWi0OXEp1CFfmpc
HB15FTgXShvobQvAO6zX69X6kfe1AsN+q8isWs13pNlW9t9OAxsQYBliEtGmE2XqL+7kKzsN32zS
lQU3kFgD6YBQTSfWBreDsJD4WsgMfE4ULkRz9/7ivO0+MHQ8Uw5EFHsdNJ22iGPqL2g7DSLHaURj
XUikkjswJTxdyD3lW9OpbSMouSRsBSWzYAPohWYzIYMC43NK5BNJtXjCYyBiesmoO6CzBP8wTLdg
N2pFwsWnqaWtVChF1HSUmWRqjI4ZaiH1wjYwttxJo2YYn6rzM5mSLZXGYN3i+Ers0eoa8uv8B5a/
LLv/+bI/ByP/xIZwUqtx4RM+wZGq/Xd9WAMt8i5wQo7d8bN2o8jN63KRr29pBO4UMsEzmONx9m6F
CpB7m/X/vDG30XVuWDpm49icCsV0TKj/MkzQO23jftSfI7Rv7s9bqo9HZjz+ENbNrhinS9jaRL3O
305T3uB/ME0YsMJT36f/7kTNt43UquC2Vt6aX1PKfg7csjUVldjxO/y/dLWcy22coHhfvGGtz/Bb
Cl5am2b4LSjBEpnPj4HYpiUIT+bfPnGK/D+89gM7x81tGzYdTefafjoLzafSlIv5khJzf1rSyjI+
dPCNos0dC3GK7xJ7trMY8WZMK3gRs4jg2GPf9slBBUZguEv8QR6Y9XW2HlfIBVkPbMW2MbJyvQjN
SFrj/nHl8OBdPdO7NbnStFFtubwiZmzvymgWrcuUps3lXiyvyBjbhzJKkyjZoLVi3ypYYtaS+1B5
xMXojejoqTRt0FtZXpEyLzpjf1cqeVkTWhg2yhSL60Nr1NjPw7ou3KjlLzi7g9Cev+z+BlBLAwQU
AAAACADdPnEq5blGcRcFAAAIDgAACQAAAGNvbmZpZy5wea1X72/bNhD9nAD5Hw5GgciBqtrJhiHJ
2s3NktZDmhq2sy7LAoOWKZstJQok7VQt+r/v+EOWrMRtB/SbRd699+7xeExardbeLhzAn4VeiAz+
IJpMiaIwymnMEhYTzXC5N+jDYdQxkTb6SX92ArHIEjaP8iJcQTc6gsNOp/usc/SsewTd45PD45Of
jmH6iaUplXD+MYcn6/QzkReSzRcagrO2yevAS8lIBv+46F992u+fWMzZxygW6Yt18njBFORSzCVJ
AX8mklJQItH3RNJTKMQSYoSSdMaUlmy61BSYBpLNnglpEVIxY0lhFpfZDOn0goKmMlUgEvvx6uoa
XtGMSsJhsJxyFsMli2mmqM0nyG9W1YLOYFrYlAujYuRVwIVAZGvdKVCG+xJWVCpj5aGF8EQeNQQh
ISDaiJcgcpPYRsUFcKKr3GirB1WpM2CZhV6IHKtaICjWec84hymFpaLJkocWA6PhXX/8+u31GHpX
N/CuNxz2rsY3pxiNvYC7dEUdFktzzhAaa5Mk0wXKtxBvzodnrzGn97J/2R/fmCou+uOr89EILt4O
oQeD3nDcP7u+7A1hcD0cvB2dRwAjaoQ5K7c7DQmipQLNnFFNGFdV9Td4xAoV8hksyIriUceUrVAf
wZ7Mi+89RS6yua0Voys7T4ElkAkdwr1k2DpaPDxfm1+dcQj9LI5C+PkYxhStojDgJKbwFEZLA3F0
1AnhpVDahL7pAXQOu93u0+5R55cQrkc9X1nLXMW9XTRbSA0fU87ZNITJZLpkXLNsMgnBUO/txpwo
BRck1kIWJ3u7OzOaYBzLmJ5MAkV5EoKNyUiKvZVSPM5Z2wTumM1ovQfPq7j1rgvHLfdjvU7kfJnS
TCvcur1bL3+gxb2QM7P6+Uul7gyHyBZpTpVQNUVezIYOYSCFWn+j12SBt5jjhXgOVyKrQhNnRXOZ
zTPTPxtyNVXrCkqtf9FsJuRX1H5DaYy1NlzBKcrpA0/swBwQqagM3PlGf7+5dAvtLfwHBx/uHT/J
c148yIvK+BBcRjsEzFgrWdniHpM3UZrEHzY3JJ2siPFXUjN1c8ZpIFv/PvkcRAe/tb+02qYaL9Mh
e6FeokJFGjjNgjp5G14AXoFWJsCvtEy0pHopM6hH3j7t3tUojLMbBCuUZuNr9O0G88odSI3Uftcp
fUiDzbfRBmFcEXoxdbq4bD3H4z/qTOuINQ+6LrVX78+YaC1VVaDrx8Cu3u6bVtu/e3CgEbYD/gpW
7Qaykfk47qPGmQLNZd2k87m3+0J5bm9ZSRs3aWvX8yH7FhfjxqX2nPa27N81CLyP3w2e4LKfkcEm
8Lo4N+F8gXFtjCT1Nid8WTZhCPbL8ZptSXMeqOXUrewkUqTwnqxIxAk+Ln6UjwqlaWoDfFe4lWhO
9UDiOy11YUCiuRTLPOjiDa5/OfsNb1mou6URBgVGQKmq3uBmoWFgOcAfOphUDlZ3wMLhW2jjogVR
Exz1wb4ucrpflls9CmVfNNsISzQrwcYz5mMs1F07KPvSG+137Sdut50BlLsH5btYv4XYcMa/YT/S
mPJZvN1Qdod4P8CRph/byb4O1LDBvZf/4/a6hPIQmvNqA9u8u49Ppq3o9qlunrC5YbUb7G6Y82ST
z7xwD8mar18TXtKkhu5LqbAxtIZcjgEOTdiNh8XNNzsgvSDz0yVjG60fy1q+byNUEwIu+z86HmfZ
+cpwx6nt/hK5RSjTD4FHcxoi819DHpgC/wNQSwECFAAUAAAACAB9P3Eq10zGnFYEAADjCgAADQAA
AAAAAAABACAAtoEAAAAAc2ltcGxldGVzdC5weVBLAQIUABQAAAAIAAE/cSrcC7aE4gQAAMUKAAAJ
AAAAAAAAAAEAIAC2gYEEAABydW5uZXIucHlQSwECFAAUAAAACADLPnEq/U+EU4sEAACNDQAACAAA
AAAAAAABACAAtoGKCQAAdGVzdC54bWxQSwECFAAUAAAACADdPnEq5blGcRcFAAAIDgAACQAAAAAA
AAABACAAtoE7DgAAY29uZmlnLnB5UEsFBgAAAAAEAAQA3wAAAHkTAAAAAA==
------=_NextPart_000_0000_01C0AEBC.4338E260--