Re: [IBMiOSS] Using Returned DB Data -- OPENSOURCE

No, I do not need them in a dictionary. I think that is overly complicated.

My searching did not lead me to the information you present. It is exactly what I was looking for.

Thank you, Kevin.

-----Original Message-----
From: OpenSource <opensource-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Kevin Adler
Sent: Thursday, June 25, 2020 11:09 AM
To: opensource@xxxxxxxxxxxxxxxxxx
Subject: Re: [IBMiOSS] Using Returned DB Data

So here's the docs for the Python DB API standard that PyODBC conforms to:
[1]https://www.python.org/dev/peps/pep-0249/

As not all databases return columns (or not in all situations) the
standard only supports retrieval by index, however implementations are
free to add extensions and PyODBC does this:
[2]https://github.com/mkleehammer/pyodbc/wiki/Features-beyond-the-DB-API#access-values-by-name
eg.

import pyodbc
conn = pyodbc.connect('DSN=*LOCAL')
cur = conn.cursor()
cur.execute('select * from qiws.qcustcdt')
for row in cur:
    print(row.CUSNUM, row.INIT, row.LSTNAM)

Above, I got rid of the fetchall(), which is not needed since the cursor
object is iterable and will do the fetches for you. I think that satisfies
your requirement for easy access, but if you _need_ it in a dictionary,
then I'm not sure it helps at all because there's no easy way that I can
find to get all the columns as attributes and convert them to a
dictionary. I think the zip solution you already have would be faster, but
you could use a dict comprehension if you wanted: { _[0]:
getattr(row,_[0]) for _ in cur.description}

----- Original message -----
From: Quintin Holmberg <qholmberg@xxxxxxxxxxxxxxxx>
Sent by: "OpenSource" <opensource-bounces@xxxxxxxxxxxxxxxxxx>
To: IBMi Open Source Roundtable <opensource@xxxxxxxxxxxxxxxxxx>
Cc:
Subject: [EXTERNAL] [IBMiOSS] Using Returned DB Data
Date: Thu, Jun 25, 2020 10:35 AM

This is more a python question than anything specific to the IBMi ...

Most of my experience is with VBA and ODBC. With those, I would access
data by ...

Dim DBConn As New ADODB.Connection
DBConn.Open "<connection string here>"
SQL = "SELECT something FROM somewhere"
Set RS = DBConn.Execute(SQL)
someDBData = RS.Fields(0).Value

So, now I'm using Python with ODBC and trying to figure out how to do
similar to above. However, there seems to be no equivalent to
'RS.Fields(0).Value?

Internet searching revealed this as the way to put it into a dictionary
for easy access (this is the function I wrote to try it out and it does
work) ...
def get_arinv_data(start_date, end_date):
   #get data
   sql = '''select <some stuff>
   from <some file>
   where somedate between '{0}' and '{1}' '''
.format(start_date.strftime('%Y-%m-%d'),end_date.strftime('%Y-%m-%d'))
   cursor.execute(sql)

   #place returned data in dictionary
   arinv_columns = [column[0] for column in cursor.description]
   arinv_data = []
   for arinv_row in cursor.fetchall():
   arinv_data.append(dict(zip(arinv_columns, arinv_row)))

   return arinv_data

Is this really the way? Is there nothing as simple as just accessing the
fields directly of a returned recordset?

--
Quintin

NOTICE – this email and any attached file (“Information”) may contain
technical data subject to the U.S. International Traffic in Arms
Regulation (“ITAR”) 22 CFR parts 120-130. The Information may not be
shared with any Foreign Person, as that term is defined in ITAR. The
Information is sent only for the use of the intended recipient and may
contain legally privileged and/or confidential information. If you are
not the intended recipient, you are notified that any dissemination or
copying of the Information is prohibited. If you received the
Information in error you must notify the sender immediately and destroy
the Information without printing or saving it.
--
This is the IBMi Open Source Roundtable (OpenSource) mailing list
To post a message email: OpenSource@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: [3]https://lists.midrange.com/mailman/listinfo/opensource ;
or email: OpenSource-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at [4]https://archive.midrange.com/opensource ;.

Help support midrange.com by shopping at amazon.com with our affiliate
link: [5]https://amazon.midrange.com ;

References

Visible links
1. https://www.python.org/dev/peps/pep-0249/
2. https://github.com/mkleehammer/pyodbc/wiki/Features-beyond-the-DB-API#access-values-by-name
3. https://lists.midrange.com/mailman/listinfo/opensource
4. https://archive.midrange.com/opensource
5. https://amazon.midrange.com/
--
This is the IBMi Open Source Roundtable (OpenSource) mailing list To post a message email: OpenSource@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/opensource
or email: OpenSource-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/opensource.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com