pythonfor-loopteradataf-string

For loop Teradata insert statement


I am pretty new to Python and looking for some assistance.

I have a large number of Teradaat tables and I am looking to take a single field from each of theose tables and put it into a new table. Instead of doing it manually I have created a list of all the table names and I am trying to create a for looped insert statement based on those name.

I have created the follwoing code that runs without errors but nothing is inserted. When I run the print statement and try it in Teradata that works so not sure why it is not working.

Any help would be much appreciated.

            cur.execute ("SELECT concat('CP.',TableName)FROM    DBC.TablesV WHERE   TableKind = 'T' and     DatabaseName = 'CP' and     TableName like 'ALB_%' and     length (tablename)=23;")
            for row  in cur.fetchall():
                if row[0] not in [""]:
                    #print(f"insert into CP.test1234 select distinct campaign_start from {row[0]};")
                    f"insert into CP.test1234 select distinct campaign_start from {row[0]};"

I was expecting campaign start date from each of the tables in the list to be inserted into CP.test1234


Solution

  • The use of f-string is for formatting not inserting, what you want is the execute.

    I have provided a code snippet to guide you:

    # if cur is your cursor connected to Teradata
    cur.execute("""
        SELECT concat('CP.', TableName)
        FROM DBC.TablesV
        WHERE TableKind = 'T'
          AND DatabaseName = 'CP'
          AND TableName LIKE 'ALB_%'
          AND length(tablename) = 23;
    """)
    
    # Fetch all table names you want
    table_names = cur.fetchall()
    
    # loops through your table names and execute the insert statement for each
    for row in table_names:
        if row[0]:  # checks if table is not None
            # the insert statement
            insert_statement = f"INSERT INTO CP.test1234 SELECT DISTINCT campaign_start FROM {row[0]};"
            
            # insert statement
            cur.execute(insert_statement)
    
    # Commit changes
    cur.connection.commit()