Mailing List Archive

Connecting python to DB2 database
Having a problem with the DB2 connector

test.py
----------------------------------------------------------------
import ibm_db_dbi
connectstring =
'DATABASE=xxx;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=xxx;PWD=xxx;'
conn = ibm_db_dbi.connect(connectstring,'','')

curr = conn.cursor
print(curr)

cSQL = "SELECT * FROM TEST"
curr.execute(cSQL)
rows = curr.fetchall()
print(len(rows))
----------------------------------------------------------------

$python test.py
<ibm_db_dbi.Connection object at 0x000002154D2FED30>
Traceback (most recent call last):
File "temp.py", line 9, in <module>
curr.execute(cSQL)
AttributeError: 'function' object has no attribute 'execute'


The ibm_db_dbi library supposedly adheres to PEP 249 (DB-API Spec 2.0),
but it ain't happening here.


Googling got me nowhere. Any ideas?

python 3.8.2 on Windows 10
pip install ibm_db
--
https://mail.python.org/mailman/listinfo/python-list
Re: Connecting python to DB2 database [ In reply to ]
On Fri, Sep 3, 2021 at 3:42 PM DFS <nospam@dfs.com> wrote:
>
> Having a problem with the DB2 connector
>
> test.py
> ----------------------------------------------------------------
> import ibm_db_dbi
> connectstring =
> 'DATABASE=xxx;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=xxx;PWD=xxx;'
> conn = ibm_db_dbi.connect(connectstring,'','')
>
> curr = conn.cursor
> print(curr)

According to PEP 249, what you want is conn.cursor() not conn.cursor.

I'm a bit surprised as to the repr of that function though, which
seems to be this line from your output:

<ibm_db_dbi.Connection object at 0x000002154D2FED30>

I'd have expected it to say something like "method cursor of
Connection object", which would have been an immediate clue as to what
needs to be done. Not sure why the repr is so confusing, and that
might be something to report upstream.

ChrisA
--
https://mail.python.org/mailman/listinfo/python-list
Re: Connecting python to DB2 database [ In reply to ]
On 9/3/2021 1:47 AM, Chris Angelico wrote:
> On Fri, Sep 3, 2021 at 3:42 PM DFS <nospam@dfs.com> wrote:
>>
>> Having a problem with the DB2 connector
>>
>> test.py
>> ----------------------------------------------------------------
>> import ibm_db_dbi
>> connectstring =
>> 'DATABASE=xxx;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=xxx;PWD=xxx;'
>> conn = ibm_db_dbi.connect(connectstring,'','')
>>
>> curr = conn.cursor
>> print(curr)
>
> According to PEP 249, what you want is conn.cursor() not conn.cursor.
>
> I'm a bit surprised as to the repr of that function though, which
> seems to be this line from your output:
>
> <ibm_db_dbi.Connection object at 0x000002154D2FED30>
>
> I'd have expected it to say something like "method cursor of
> Connection object", which would have been an immediate clue as to what
> needs to be done. Not sure why the repr is so confusing, and that
> might be something to report upstream.
>
> ChrisA


Thanks. I must've done it right, using conn.cursor(), 500x.
Bleary-eyed from staring at code too long I guess.

Now can you get DB2 to accept ; as a SQL statement terminator like the
rest of the world? They call it "An unexpected token"...

--
https://mail.python.org/mailman/listinfo/python-list
Re: Connecting python to DB2 database [ In reply to ]
On Fri, Sep 3, 2021 at 11:37 PM DFS <nospam@dfs.com> wrote:
>
> On 9/3/2021 1:47 AM, Chris Angelico wrote:
> > On Fri, Sep 3, 2021 at 3:42 PM DFS <nospam@dfs.com> wrote:
> >>
> >> Having a problem with the DB2 connector
> >>
> >> test.py
> >> ----------------------------------------------------------------
> >> import ibm_db_dbi
> >> connectstring =
> >> 'DATABASE=xxx;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=xxx;PWD=xxx;'
> >> conn = ibm_db_dbi.connect(connectstring,'','')
> >>
> >> curr = conn.cursor
> >> print(curr)
> >
> > According to PEP 249, what you want is conn.cursor() not conn.cursor.
> >
> > I'm a bit surprised as to the repr of that function though, which
> > seems to be this line from your output:
> >
> > <ibm_db_dbi.Connection object at 0x000002154D2FED30>
> >
> > I'd have expected it to say something like "method cursor of
> > Connection object", which would have been an immediate clue as to what
> > needs to be done. Not sure why the repr is so confusing, and that
> > might be something to report upstream.
> >
> > ChrisA
>
>
> Thanks. I must've done it right, using conn.cursor(), 500x.
> Bleary-eyed from staring at code too long I guess.

Cool cool! Glad that's working.

> Now can you get DB2 to accept ; as a SQL statement terminator like the
> rest of the world? They call it "An unexpected token"...
>

Hmm, I don't know that the execute() method guarantees to allow
semicolons. Some implementations will strip a trailing semi, but they
usually won't allow interior ones, because that's a good way to worsen
SQL injection vulnerabilities. It's entirely possible - and within the
PEP 249 spec, I believe - for semicolons to be simply rejected.

ChrisA
--
https://mail.python.org/mailman/listinfo/python-list
Re: Connecting python to DB2 database [ In reply to ]
On Fri, 3 Sep 2021 09:29:20 -0400, DFS <nospam@dfs.com> declaimed the
following:

>
>Now can you get DB2 to accept ; as a SQL statement terminator like the
>rest of the world? They call it "An unexpected token"...

I've never seen a semi-colon used for SQL statements via a db-api
adapter. The semi-colon is, in my experience, only required by basic
interactive query utilities -- to tell the utility that the statement is
fully entered, and can be sent to the server.


--
Wulfraed Dennis Lee Bieber AF6VN
wlfraed@ix.netcom.com http://wlfraed.microdiversity.freeddns.org/

--
https://mail.python.org/mailman/listinfo/python-list
Re: Connecting python to DB2 database [ In reply to ]
On 9/3/2021 9:50 AM, Chris Angelico wrote:
> On Fri, Sep 3, 2021 at 11:37 PM DFS <nospam@dfs.com> wrote:
>>
>> On 9/3/2021 1:47 AM, Chris Angelico wrote:
>>> On Fri, Sep 3, 2021 at 3:42 PM DFS <nospam@dfs.com> wrote:
>>>>
>>>> Having a problem with the DB2 connector
>>>>
>>>> test.py
>>>> ----------------------------------------------------------------
>>>> import ibm_db_dbi
>>>> connectstring =
>>>> 'DATABASE=xxx;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=xxx;PWD=xxx;'
>>>> conn = ibm_db_dbi.connect(connectstring,'','')
>>>>
>>>> curr = conn.cursor
>>>> print(curr)
>>>
>>> According to PEP 249, what you want is conn.cursor() not conn.cursor.
>>>
>>> I'm a bit surprised as to the repr of that function though, which
>>> seems to be this line from your output:
>>>
>>> <ibm_db_dbi.Connection object at 0x000002154D2FED30>
>>>
>>> I'd have expected it to say something like "method cursor of
>>> Connection object", which would have been an immediate clue as to what
>>> needs to be done. Not sure why the repr is so confusing, and that
>>> might be something to report upstream.
>>>
>>> ChrisA
>>
>>
>> Thanks. I must've done it right, using conn.cursor(), 500x.
>> Bleary-eyed from staring at code too long I guess.
>
> Cool cool! Glad that's working.
>
>> Now can you get DB2 to accept ; as a SQL statement terminator like the
>> rest of the world? They call it "An unexpected token"...
>>
>
> Hmm, I don't know that the execute() method guarantees to allow
> semicolons. Some implementations will strip a trailing semi, but they
> usually won't allow interior ones, because that's a good way to worsen
> SQL injection vulnerabilities. It's entirely possible - and within the
> PEP 249 spec, I believe - for semicolons to be simply rejected.


The default in the DB2 'Command Line Plus' tool is semicolons aren't
"allowed".


db2 => connect to SAMPLE

db2 => SELECT COUNT(*) FROM STAFF;
SQL0104N An unexpected token ";" was found following "COUNT(*) FROM STAFF".
Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601

db2 => SELECT COUNT(*) FROM STAFF
1
-----------
35
1 record(s) selected.



But I should've known you can set the terminator value:

https://www.ibm.com/docs/en/db2/11.1?topic=clp-options

Option : -t
Description: This option tells the command line processor to use a
semicolon (;) as the statement termination character.
Default : OFF


$ db2 -t

turns it on in CommandLinePlus - and the setting applies to the DB-API
code too.
--
https://mail.python.org/mailman/listinfo/python-list
Re: Connecting python to DB2 database [ In reply to ]
On Sun, Sep 5, 2021 at 1:26 PM DFS <nospam@dfs.com> wrote:
>
> On 9/3/2021 9:50 AM, Chris Angelico wrote:
> > On Fri, Sep 3, 2021 at 11:37 PM DFS <nospam@dfs.com> wrote:
> >>
> >> On 9/3/2021 1:47 AM, Chris Angelico wrote:
> >>> On Fri, Sep 3, 2021 at 3:42 PM DFS <nospam@dfs.com> wrote:
> >>>>
> >>>> Having a problem with the DB2 connector
> >>>>
> >>>> test.py
> >>>> ----------------------------------------------------------------
> >>>> import ibm_db_dbi
> >>>> connectstring =
> >>>> 'DATABASE=xxx;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=xxx;PWD=xxx;'
> >>>> conn = ibm_db_dbi.connect(connectstring,'','')
> >>>>
> >>>> curr = conn.cursor
> >>>> print(curr)
> >>>
> >>> According to PEP 249, what you want is conn.cursor() not conn.cursor.
> >>>
> >>> I'm a bit surprised as to the repr of that function though, which
> >>> seems to be this line from your output:
> >>>
> >>> <ibm_db_dbi.Connection object at 0x000002154D2FED30>
> >>>
> >>> I'd have expected it to say something like "method cursor of
> >>> Connection object", which would have been an immediate clue as to what
> >>> needs to be done. Not sure why the repr is so confusing, and that
> >>> might be something to report upstream.
> >>>
> >>> ChrisA
> >>
> >>
> >> Thanks. I must've done it right, using conn.cursor(), 500x.
> >> Bleary-eyed from staring at code too long I guess.
> >
> > Cool cool! Glad that's working.
> >
> >> Now can you get DB2 to accept ; as a SQL statement terminator like the
> >> rest of the world? They call it "An unexpected token"...
> >>
> >
> > Hmm, I don't know that the execute() method guarantees to allow
> > semicolons. Some implementations will strip a trailing semi, but they
> > usually won't allow interior ones, because that's a good way to worsen
> > SQL injection vulnerabilities. It's entirely possible - and within the
> > PEP 249 spec, I believe - for semicolons to be simply rejected.
>
>
> The default in the DB2 'Command Line Plus' tool is semicolons aren't
> "allowed".
>

Yeah, but that's a REPL feature. In Python, the end of a command is
signalled by the end of the string; if you want a multiline command,
you have a string with multiple lines in it. Command line SQL has to
either force you to one line, or have some means of distinguishing
between "more text coming" and "here's a command, run it".

ChrisA
--
https://mail.python.org/mailman/listinfo/python-list