FazBrowse GitHub Viewer
|
Trending
|
URL:
|
Home
Tools:
[Download Repo ZIP]
[View Raw Code]
[Original HTTPS Page]
python-driver/samples/FastLoadJSON.py at master · Teradata/python-driver · GitHub
Uh oh!
There was an error while loading.
Please reload this page
.
Teradata
/
python-driver
Public
Notifications
You must be signed in to change notification settings
Fork
29
Star
74
Code
Pull requests
0
Actions
Security and quality
0
Insights
Additional navigation options
Code
Pull requests
Actions
Security and quality
Insights
Expand file tree
Breadcrumbs
python-driver
/
samples
/
FastLoadJSON.py
Copy path
More file actions
More file actions
Latest commit
History
History
History
124 lines (101 loc) · 5.18 KB
Breadcrumbs
python-driver
/
samples
/
FastLoadJSON.py
Copy path
File metadata and controls
124 lines (101 loc) · 5.18 KB
Raw
Copy raw file
Download raw file
Open symbols panel
Edit and raw actions
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
# 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
os
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
try
:
# 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
}
"
)
Back
|
FazBrowse Home
|
New Git URL