-
Notifications
You must be signed in to change notification settings - Fork 22
/
FastLoadCSV.py
106 lines (84 loc) · 3.68 KB
/
FastLoadCSV.py
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
# Copyright 2020 by Teradata Corporation. All rights reserved.
# This sample program demonstrates how to FastLoad a CSV file.
import csv
import os
import teradatasql
with teradatasql.connect (host="whomooz", user="guest", password="please") as con:
with con.cursor () as cur:
sTableName = "FastLoadCSV"
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)
records = [
"c1,c2",
"1,",
"2,abc",
"3,def",
"4,mno",
"5,",
"6,pqr",
"7,uvw",
"8,xyz",
"9,",
]
print ("records", records)
csvFileName = "dataPy.csv"
print ("open", csvFileName)
with open (csvFileName, 'w', newline='') as csvFile:
csvWriter = csv.writer (csvFile, delimiter='\n')
csvWriter.writerow (records)
try:
sRequest = "CREATE TABLE " + sTableName + " (c1 INTEGER NOT NULL, c2 VARCHAR(10))"
print (sRequest)
cur.execute (sRequest)
try:
print ("con.autocommit = False")
con.autocommit = False
try:
sInsert = "{fn teradata_require_fastload}{fn teradata_read_csv(" + csvFileName + ")}INSERT INTO " + sTableName + " (?, ?)"
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 * 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)
finally:
print ("os.remove", csvFileName)
os.remove (csvFileName)