Re: [python-win32] adodbapi: paramstyle 'named' doesn't work as expected
Sibyille: Thanks for the report and the excellent analysis. I was afraid no one would ever use "named" paramstyle. Dennis: Any code suggestion would be very welcome. Would you be able to suggest a patch? -- Vernon Cole On Sat, Feb 23, 2019 at 7:30 AM Sibylle Koczian wrote: > Am 21.02.2019 um 16:26 schrieb Dennis Lee Bieber: > > > > If I were coding something, I'd likely use the native style to > reduce > > the cost of conversion overhead. Relatively speaking, that name > extraction > > code is /slow/ -- it splits the query on :, then loops over each > character > > looking for something (non-alphanumeric and not _) on which to terminate > > the resulting name... and that loop is done at Python source code level > (it > > doesn't even use a > > > > for i,c in enumerate(chunk): > > > > which would exit the loop on the end of the chunk; instead it manually > > increments the counter to be "next character" [which fails when there is > no > > next character]. > > > > > > Well - I started to write my application without much thought about > execution speed, because the database tables concerned are small to very > small. If I can use a dictionary for query parameters then the function > producing the parameters doesn't need to know much about the SQL query > using them. That's why I like pyformat and named. Looking at the apibase > code I think pyformat might be a little bit faster? > > But when I saw things didn't work as expected that had to be cleared up > in any case. > > Greetings > Sibylle > ___ > python-win32 mailing list > python-win32@python.org > https://mail.python.org/mailman/listinfo/python-win32 > ___ python-win32 mailing list python-win32@python.org https://mail.python.org/mailman/listinfo/python-win32
Re: [python-win32] adodbapi: paramstyle 'named' doesn't work as expected
Am 21.02.2019 um 16:26 schrieb Dennis Lee Bieber: If I were coding something, I'd likely use the native style to reduce the cost of conversion overhead. Relatively speaking, that name extraction code is /slow/ -- it splits the query on :, then loops over each character looking for something (non-alphanumeric and not _) on which to terminate the resulting name... and that loop is done at Python source code level (it doesn't even use a for i,c in enumerate(chunk): which would exit the loop on the end of the chunk; instead it manually increments the counter to be "next character" [which fails when there is no next character]. Well - I started to write my application without much thought about execution speed, because the database tables concerned are small to very small. If I can use a dictionary for query parameters then the function producing the parameters doesn't need to know much about the SQL query using them. That's why I like pyformat and named. Looking at the apibase code I think pyformat might be a little bit faster? But when I saw things didn't work as expected that had to be cleared up in any case. Greetings Sibylle ___ python-win32 mailing list python-win32@python.org https://mail.python.org/mailman/listinfo/python-win32
Re: [python-win32] adodbapi: paramstyle 'named' doesn't work as expected
Am 20.02.2019 um 19:28 schrieb Dennis Lee Bieber: On Wed, 20 Feb 2019 17:22:16 +0100, Sibylle Koczian declaimed the following: UPCMD = "UPDATE tblHaupt SET item = :item, ort = :ort_id WHERE id = :h_id" As a total wild guess... try ending the SQL with a ; or space ... :h_id;" or ... :h_id " That "wild guess" was exactly right. After adding ; or space to my update command my test script finished as expected. After removing the closing ORDER BY clause from a SELECT the IndexError appeared at the execution of that command. I'd think that SQL statements ending with a :name parameter can't be that unusual, especially UPDATE and probably DELETE. But possibly the "named" paramstyle isn't much used. Thank you very much, Sibylle ___ python-win32 mailing list python-win32@python.org https://mail.python.org/mailman/listinfo/python-win32
Re: [python-win32] adodbapi: paramstyle 'named' doesn't work as expected
Dennis Lee Bieber wrote: The loop is looking for the end of the parameter name by looking for a non-alphanumeric character. But your update command just... ends -- there is no non-alphanumeric character after the name to terminate the loop. INSERT syntax has a closing ) to terminate. I WOULD consider this a subtle bug in the ADODBAPI code. You are being too kind. This bug is not subtle in any way. Python is so good at string parsing -- there must be a better way to write that. -- Tim Roberts, t...@probo.com Providenza & Boekelheide, Inc. smime.p7s Description: S/MIME Cryptographic Signature ___ python-win32 mailing list python-win32@python.org https://mail.python.org/mailman/listinfo/python-win32
[python-win32] adodbapi: paramstyle 'named' doesn't work as expected
Hello,
I'm trying to write a small application to work with a Microsoft Access
database. This application will have to insert and update records, and
I'd like to use paramstyle='named'. With INSERT commands this seems to
work, with UPDATE I get an exception I can't explain.
Example: The database table called tblHaupt contains the fields id
(integer, primary key, autoincrement), item (varchar), ort (integer,
foreign key into another table). There are more fields, but the UPDATE
command I've been trying out only uses these three.
Code to try it:
import adodbapi
CONNFORM = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};"
DBLOKAL = r"X:\Path\to\database\db.accdb"# or db.mdb
UPCMD = "UPDATE tblHaupt SET item = :item, ort = :ort_id WHERE id = :h_id"
updata = {"item": "Testkeks D", "ort_id": 12, "h_id": 745}
connstr = CONNFORM.format(DBLOKAL)
conn = adodbapi.connect(connstr, paramstyle='named')
curs = conn.cursor()
curs.execute(UPCMD, updata)
Result:
Traceback (most recent call last):
Python Shell, prompt 62, line 1
# Used internally for debug sandbox under external interpreter
File "C:\Program
Files\Python37\Lib\site-packages\adodbapi\adodbapi.py", line 867, in execute
operation = self._reformat_operation(operation, parameters) # if
'named' will set self._parameter_names
File "C:\Program
Files\Python37\Lib\site-packages\adodbapi\adodbapi.py", line 751, in
_reformat_operation
operation, self._parameter_names =
api.changeNamedToQmark(operation) # convert :name to ?
File "C:\Program
Files\Python37\Lib\site-packages\adodbapi\apibase.py", line 598, in
changeNamedToQmark
c = chunk[i]
builtins.IndexError: string index out of range
##
What's the matter here? INSERT into the same table works. The same
UPDATE command using paramstyle 'pyformat' works. Why not UPDATE with
'named'? Is something wrong with my parameter dictionary and I don't see it?
Python version is 3.7.2 (64bit), adodbapi.__version__ = 2.6.0.6, Windows
10 Home, Version 1809 (64bit).
Thank you for explanations,
Sibylle
___
python-win32 mailing list
python-win32@python.org
https://mail.python.org/mailman/listinfo/python-win32
