Python teradatasql get column_name and data_type

Question

I am using teradatasql to connect DB and get the table definition. Below is my code which returns the definition for table. Here I trying find any default function which returns the colum_name and data_type of table as a separate function.

with teradatasql.connect ('{"host":"whomooz","user":"guest","password":"please"}') as con:
    with con.cursor () as cur:
    try:
                sRequest = "show table MYTABLE"
                print(sRequest)
                cur.execute(sRequest)
                [print(row) for row in sorted(cur.fetchall())]
            except Exception as ex:
                print("Ignoring", str(ex).split("\n")[0])

here I am looking for any inbuilt function which can return column_name and data_type.

output should be like

customer_name VARCHAR

address VARCHAR

type SMALLINT

I looked at the teradatasql docs but did not find any reference

You can query [dbc.ColumnsV](https://docs.teradata.com/r/ANYCOtbX9Q1iyd~Uiok8gA/GQEcUS4PIFPa9D3bD4xqUA) though you will need to decode the ColumnType codes (e.g. CV = VARCHAR, I2 = SMALLINT). HELP TABLE is another option but again returns Type codes. — Fred, Dec 07 '21 at 22:29

score 1 · Answer 1 · answered Dec 08 '21 at 19:26

We offer a sample program that demonstrates how to prepare a SQL request and use the fake_result_sets feature to obtain result set column metadata and question-mark parameter marker metadata.

See below for example code that prepares a select * for a table in conjunction with fake_result_sets and prints the result set column metadata.

import json
import teradatasql
with teradatasql.connect ('{"host":"whomooz","user":"guest","password":"please"}') as con:
    with con.cursor () as cur:
        cur.execute ("{fn teradata_rpo(S)}{fn teradata_fake_result_sets}select * from dbc.dbcinfo")
        [ print ("DatabaseName={} TableName={} ColumnName={} TypeName={}".format (m ["DatabaseName"], m ["ObjectName"], m ["Name"], m ["TypeName"])) for m in json.loads (cur.fetchone () [7]) ]

Prints the following:

DatabaseName=DBC TableName=dbcinfo ColumnName=InfoKey TypeName=VARCHAR
DatabaseName=DBC TableName=dbcinfo ColumnName=InfoData TypeName=VARCHAR

Python teradatasql get column_name and data_type

1 Answers1