iframe-proxy | Sunbelt Computer Software

124 lines (101 loc) · 5.18 KB
# Copyright 2026 by Teradata Corporation. All rights reserved.
# This sample program demonstrates how to FastLoad a JSON file.
# It also illustrates dual treatment of a nested JSON object: the raw JSON
# string is stored verbatim in the "address" column, while the flattened
# sub-field "city" is stored in a separate column.
import json
import teradatasql
with teradatasql.connect (host="whomooz", user="guest", password="please") as con:
    with con.cursor () as cur:
        sTableName = "FastLoadJSON"
        sRequest = "DROP TABLE " + sTableName
        print (sRequest)
        cur.execute (sRequest, ignoreErrors=3807)
        sRequest = "DROP TABLE " + sTableName + "_ERR_1"
        print (sRequest)
        cur.execute (sRequest, ignoreErrors=3807)
        sRequest = "DROP TABLE " + sTableName + "_ERR_2"
        print (sRequest)
        cur.execute (sRequest, ignoreErrors=3807)
        # Each record has three top-level keys:
        #   id      -- scalar integer
        #   name    -- scalar string
        #   address -- nested object; its raw JSON string maps to the "address" column
        #              and its sub-field "city" maps to the "city" column.
        records = [
            {"id": 1, "name": "Alice", "address": {"city": "Boston"}},
            {"id": 2, "name": "Bob",   "address": {"city": "Austin"}},
            {"id": 3, "name": "Carol", "address": {"city": "Chicago"}},
            {"id": 4, "name": "Dave",  "address": {"city": "Denver"}},
            {"id": 5, "name": "Erin",  "address": {"city": "Eugene"}},
            {"id": 6, "name": "Frank", "address": {"city": "Fresno"}},
            {"id": 7, "name": "Grace", "address": {"city": "Houston"}},
            {"id": 8, "name": "Hank",  "address": {"city": "Irvine"}},
            {"id": 9, "name": "Iris",  "address": {"city": "Jacksonville"}},
        jsonFileName = "dataPy.json"
        print (f"Writing {jsonFileName}")
        with open (jsonFileName, "w", encoding="UTF-8") as f:
            json.dump (records, f)
        try:
            sRequest = "CREATE TABLE " + sTableName + " (id INTEGER, name VARCHAR(20), address VARCHAR(200), city VARCHAR(20)) UNIQUE PRIMARY INDEX (id)"
            print (sRequest)
            cur.execute (sRequest)
            try:
                print ("con.autocommit = False")
                con.autocommit = False
                    # The INSERT column list (id, name, address, city) drives name-based matching:
                    #   ?1 -> id      (scalar)
                    #   ?2 -> name    (scalar)
                    #   ?3 -> address (raw JSON object string -- dual treatment)
                    #   ?4 -> city    (flattened sub-field of address)
                    sInsert = (
                        "{fn teradata_require_fastload}"
                        "{fn teradata_read_json(" + jsonFileName + ")}"
                        "INSERT INTO " + sTableName + " (id, name, address, city) VALUES (?, ?, ?, ?)"
                    print (sInsert)
                    cur.execute (sInsert)
                    # obtain the warnings and errors for transmitting the data to the database -- the acquisition phase
                    sRequest = "{fn teradata_nativesql}{fn teradata_get_warnings}" + sInsert
                    print (sRequest)
                    cur.execute (sRequest)
                    [ print (row) for row in cur.fetchall () ]
                    sRequest = "{fn teradata_nativesql}{fn teradata_get_errors}" + sInsert
                    print (sRequest)
                    cur.execute (sRequest)
                    [ print (row) for row in cur.fetchall () ]
                    sRequest = "{fn teradata_nativesql}{fn teradata_logon_sequence_number}" + sInsert
                    print (sRequest)
                    cur.execute (sRequest)
                    [ print (row) for row in cur.fetchall () ]
                    print ("con.commit()")
                    con.commit ()
                    # obtain the warnings and errors for the apply phase
                    sRequest = "{fn teradata_nativesql}{fn teradata_get_warnings}" + sInsert
                    print (sRequest)
                    cur.execute (sRequest)
                    [ print (row) for row in cur.fetchall () ]
                    sRequest = "{fn teradata_nativesql}{fn teradata_get_errors}" + sInsert
                    print (sRequest)
                    cur.execute (sRequest)
                    [ print (row) for row in cur.fetchall () ]
                finally:
                    print ("con.autocommit = True")
                    con.autocommit = True
                sRequest = "SELECT id, name, address, city FROM " + sTableName + " ORDER BY 1"
                print (sRequest)
                cur.execute (sRequest)
                [ print (row) for row in cur.fetchall () ]
            finally:
                sRequest = "DROP TABLE " + sTableName
                print (sRequest)
                cur.execute (sRequest, ignoreErrors=3807)
        finally:
            print (f"os.remove({jsonFileName})")
            try:
                os.remove (jsonFileName)
            except OSError as e:
                print (f"os.remove failed: {e}")
Sunbelt Computer Software

PL/B Language Development and Support

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Sunbelt Computer Software

PL/B Language Development and Support

Uh oh!

FilesExpand file tree

FastLoadJSON.py

Latest commit

History

FastLoadJSON.py

File metadata and controls
