Download DML and DDL sql from Teradata using python

Question

What approach should I follow to download DDL, DML and Stored Procedures from the teradata database using python.

I have created the sample code but what is the approach to download these sql files for data migration process.

udaExec = teradata.UdaExec(appName="HelloWorld", version="1.0",logConsole=False)
session = udaExec.connect(method="odbc", system="xxx",username="xxx", password="xxx");
for row in session.execute("show tables {} > {}".format(tables, export_tables)):
     print(row)

Unlike MSSQL which had mssql-scripter to download .sql files, does teradata provide any such option to download. Also, does it provide support to download sequences, views and procedures ?

For the Schema Migration process, what should be the best approach to download these files from the teradata as a source ?

Answer 1

Happy to share that I got the solution for this approach. In order to get the files in sql format use the given code to extract DDL and DML Code.

The given code is for sample database dbc.

with teradatasql.connect(host='enter_host_ip', user='---', password='---') as connect:
#get the table name and database name in csv file using select statement
df = pd.read_csv("result.csv", index_col=None)
for tables_name in df['TableName']:
    query = "SHOW TABLE DBC."+ tables_name
    try:
        df = pd.read_sql(query, connect)
        df1 = df['Request Text'][0]
        writePath = "C:\\Users\\SQL\\"+tables_name+".sql"
        with open(writePath, 'a') as f:
            dfAsString = df1
            f.write(dfAsString)
    except Exception as e1:
        print(tables_name)            
        pass

Note : Out of 192 tables I was able to get DDL/DML scripts for 189 tables. For tables perform manual intervention.