Referencing fields (columns) by field name in Teradata

Question

I am using python (3.4.3) to ODBC to a Teradata database, (rather new to this) I am wondering (if possible) to reference values of rows by their field name as i am looping through them instead of by their list index. (in case i change my tables) Much like a record set in VBA with the ! syntax (recordset!FIELD_NAME)

If i run this code,

udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0", logConsole=False)
session = udaExec.connect(method="odbc", dsn="TEST")
cursor = session.cursor()
rows = list(cursor.execute("SELECT TOP 1 * FROM RES_TBL"))
print(rows)

My output is: [<teradata.util.Row object at 0x000000000402D080>]

I eventually was able to store each row as string in a list so i could see them\ mess with them, but i feel like thats a bad idea for larger data sets. I am sorry if this is not a good question, but anything helps!!

my full code currently is:

import teradata
import pyodbc
import json
udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0", logConsole=False)
session = udaExec.connect(method="odbc", dsn="TEST")
cursor = session.cursor()
rows = list(cursor.execute("SELECT TOP 1 * FROM RES_TBL"))
print(rows)
for row in session.execute("SELECT TOP 1 * FROM RES_TBL"):
    testlist = []
    testlist.append(str(row))
    print(testlist)

score 3 · Accepted Answer · answered Sep 24 '16 at 20:57

3

Maybe you don't want to use pandas for some reason but otherwise I'd suggest this:

import pandas ad pd
cursor = session.execute(SQL_script)
df = pd.DataFrame.from_records(cursor)
cols = []
for row in cursor.description:
    cols.append(row[0])
df.columns = cols
session.close()

answered Sep 24 '16 at 20:57

Diego

812
7
25

i've never heard of panda, but shall research it!! thanks so much for the post!! – BLang Sep 26 '16 at 04:46
1

Pandas is a way to go if you want to work with tables in Python. So far very powerful library for what I need to do throughout. – Diego Sep 26 '16 at 16:34

score 2 · Answer 2 · edited May 23 '17 at 10:34

Hello for anyone else that is trying to figure this out, I HAVE SOLVED IT w00t!

I found great links for help here, there are some programming wizzards in those links, i learned lots!! return SQL table as JSON in python

Output pyodbc cursor results as python dictionary

http://developer.teradata.com/tools/reference/teradata-python-module

Here is the solution!!!

import teradata
import pyodbc
import json
###Dev environment for learning teradata interactions with python

#form the ODBC connection to teradata
try:
    udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0", logConsole=False)
    session = udaExec.connect(method="odbc", dsn="TEST")
    cursor = session.cursor()
    columnNames = []
    rows = cursor.execute("SELECT TOP 1 * FROM RES_TBL")
    for col in rows.description:
        columnNames.append(col[0])

    for row in rows: #session.execute("SELECT TOP 1 * FROM RES_TBL"):
        testlist = []
        testlist.append(dict(zip(columnNames, row)))
    print(testlist)
except:
    raise
finally:
    cursor.close()

Referencing fields (columns) by field name in Teradata

2 Answers2