-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathWrite Back to Database.py
102 lines (79 loc) · 3.34 KB
/
Write Back to Database.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
'''
Note:
You can modify the code as per your requirement but things which must have to be correct is server name ,
databasename,user & password in order to successfully write back to database.
'''
from Spotfire.Dxp.Data import *
#these are neccessary imports required to show messagebox
import clr
clr.AddReference("System.Windows.Forms")
from System.Windows.Forms import MessageBox, MessageBoxButtons
from System.Windows.Forms import DialogResult
#these are neccessary imports requiered to insert data into sql
from Spotfire.Dxp.Data.Import import DatabaseDataSource
from Spotfire.Dxp.Data.Import import DatabaseDataSourceSettings
from Spotfire.Dxp.Application.Visuals import TablePlot
from Spotfire.Dxp.Application.Visuals import VisualTypeIdentifiers
from Spotfire.Dxp.Data import IndexSet
from Spotfire.Dxp.Data import RowSelection
from Spotfire.Dxp.Data import DataValueCursor
from Spotfire.Dxp.Data import DataSelection
from Spotfire.Dxp.Data import DataPropertyClass
#tableName='test_data'
#YOU CAN ADD AS MANY COLUMNS AS YOU WANT TO FETCH FROM TABLE
tableName='test_data'
columnToFetch_id='id'
columnToFetch_name='name'
columnToFetch_age='age'
columnToFetch_gender='gender'
activeTable=Document.Data.Tables[tableName]
#GIVES TOTAL NO OF ROW COUNT
rowCount = activeTable.RowCount
rowsToInclude = IndexSet(rowCount,True)
cursor1 = DataValueCursor.CreateFormatted(activeTable.Columns[columnToFetch_id])
cursor2 = DataValueCursor.CreateFormatted(activeTable.Columns[columnToFetch_name])
cursor3 = DataValueCursor.CreateFormatted(activeTable.Columns[columnToFetch_age])
cursor4 = DataValueCursor.CreateFormatted(activeTable.Columns[columnToFetch_gender])
ctr1 = 0
count=0
#FROM WHICH YOU WANT TO SEARCH AND COMPARE
name = Document.Properties["name"]
#print name
#COLUMN WHICH YOU WANT TO SET
age = Document.Properties["age"]
ctrl = 0
#print age
#LOOP REQUIRED TO FETCH ALL VALUES OF COLUMNS
for row in activeTable.GetRows(rowsToInclude,cursor1,cursor2,cursor3,cursor4):
#rowIndex = row.Index
#print rowIndex
ctrl = ctrl +1
val2 = cursor2.CurrentValue
if(val2 == name):
count=count+1
val1 = cursor1.CurrentValue
#print val1
vname = cursor2.CurrentValue
val3 = cursor3.CurrentValue
val4 = cursor4.CurrentValue
new_id = int(val1)+1
if(ctrl == rowCount):
break
#CHECKING WHETHER THAT NAME IS PRESENT IN TABLE OR NOT
if(count != 0):
MessageBox.Show("this name is present")
#INSERTING DATA INTO TABLE STARTS HERE
#sqlCommand = "UPDATE empdata SET " + columnToFetch_age + "='" + str(age) + "'WHERE (name=" + name + ");"
#sqlCommand = "insert into empdata(name) values ('" +vname +"');"
sqlCommand = "insert into empdata(id,name,age,gender) values ('"+str(new_id)+"','"+name+"','"+str(age)+"','" +val4+"');"
#print val2
#sqlCommand ="create table "+val4+" (id int);"
dbsettings = DatabaseDataSourceSettings( "System.Data.SqlClient","Server=localhost;Database=OperationsDB;UID=OperationsUser;PWD=OperationsUser",sqlCommand)
ds = DatabaseDataSource(dbsettings)
newDataTable = Document.Data.Tables.Add("temp12",ds)
Document.Data.Tables.Remove(newDataTable)
#newDataTable = Document.Data.Tables.Add("temp",ds)
#INSERTING DATA INTO TABLE ENDS HERE
MessageBox.Show("Data Inserted Successfully !!")
else:
MessageBox.Show("this name is not present")