repl.it
@yskov/

ibm api

Python

No description

fork
loading
Files
  • main.py
main.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
#Replace the placeholder values with the actuals for your Db2 Service Credentials
import ibm_db



dsn_driver = "{IBM DB2 ODBC DRIVER}"
dsn_database = "BLUDB"            # e.g. "BLUDB"
dsn_hostname = "dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net"            # e.g.: "dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net"
dsn_port = "50000"                    # e.g. "50000"
dsn_protocol = "TCPIP"            # i.e. "TCPIP"
dsn_uid = "zff09391"                 # e.g. "abc12345"
dsn_pwd = "7d2zw3-qgtfm7t07"                 # e.g. "7dBZ3wWt9XN6$o0J"

#Create database connection
#DO NOT MODIFY THIS CELL. Just RUN it with Shift + Enter
dsn = (
    "DRIVER={0};"
    "DATABASE={1};"
    "HOSTNAME={2};"
    "PORT={3};"
    "PROTOCOL={4};"
    "UID={5};"
    "PWD={6};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port, dsn_protocol, dsn_uid, dsn_pwd)

try:
    conn = ibm_db.connect(dsn, "", "")
    print ("Connected to database: ", dsn_database, "as user: ", dsn_uid, "on host: ", dsn_hostname)

except:
    print ("Unable to connect: ", ibm_db.conn_errormsg() )


#Lets first drop the table INSTRUCTOR in case it exists from a previous attempt
#dropQuery = "drop table INSTRUCTOR"

#Now execute the drop statment
#dropStmt = ibm_db.exec_immediate(conn, dropQuery)

#Construct the Create Table DDL statement - replace the ... with rest of the statement


dropQuery = "drop table FINANCES"


#Now execute the drop statment
dropStmt = ibm_db.exec_immediate(conn, dropQuery)
#create qeury
createQuery = "CREATE TABLE  FINANCES (charge VARCHAR(20),due VARCHAR(4), amount INTEGER, status VARCHAR(7))"


#method and execution of the createQuery 
createStmt = ibm_db.exec_immediate(conn, createQuery)



#Construct the query - replace ... with the insert statement
insertQuery =  "INSERT INTO FINANCES VALUES ('milan','1st',65.0,'warning'),('storage facility','1st',109.0,'done'),('lowes','3rd',25.0,'done'),('chase nadeem','4th',120.0,'done'),('ltf dues','8th',10.0,'warning'),('state farm','8th',96.0,'done'),('ups','10th',30.0,'done'),('sprint','15th',90.0,'done'),('audible','15th',14.95,'done'),('xfinity','15th',75.0,'done'),('home depot','15th',45.0,'done'),('number barn','15th',2.0,'done'),('discover nadeem','17th',120.0,'done'),('affirm credit','20th',70.0,'done'),('fitness','24th',25.0,'-'),('youtube premium','24th',10.0,'-'),('google drive','24th',2.0,'-'),('blue cross dental','25th',22.0,'-'),('blue cross medical','25th',51.0,'-'),('blue cross vision','25th',13.0,'-'),('Resume nerd','26th',4.0,NULL),('paypal credit','26th',95.0,NULL),('honda 2016','28th',367.0,NULL),('student loan payment','28th',30.0,NULL),('chase slate','28th',60.0,NULL),('cap one','28th',85.0,NULL),('chase sapphire','28th',60.0,'done'),('chase freedom 1','28th',120.0,'done'),('chase freedom 2','28th',50.0,'done'),('discover mine','28th',60.0,'done'),('ameriprise','28th',238.0,NULL),('sams club','28th',40.0,NULL),('discount tire card','28th',50.0,NULL),('best buy credit','28th',60.0,NULL),('amazon','28th',60.0,NULL)"
    



#execute the insert statement
insertStmt = ibm_db.exec_immediate(conn, insertQuery)

#replace ... with the insert statement that inerts the remaining two rows of data
##insertQuery2 = "insert into INSTRUCTOR values (2, 'raul', 'chong', 'markham', 'ca'), (3, 'hima', 'vasudevan', 'chicago', 'us')"

#execute the statement
#insertStmt2 = ibm_db.exec_immediate(conn, insertQuery2)

#Construct the query that retrieves all rows from the INSTRUCTOR table
selectQuery = "select * from finances"

#Execute the statement
selectStmt = ibm_db.exec_immediate(conn, selectQuery)

#Fetch the Dictionary (for the first row only) - replace ... with your code
ibm_db.fetch_both(selectStmt)


#Fetch the rest of the rows and print the ID and FNAME for those rows
while ibm_db.fetch_row(selectStmt) != False:
   print (" charge",  ibm_db.result(selectStmt, 0))




ibm_db.close(conn)