-
Notifications
You must be signed in to change notification settings - Fork 42
/
db2connect.py
69 lines (49 loc) · 1.96 KB
/
db2connect.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
# This program requires the python module ibm-db to be installed.
# Install it using the below command
# pip3 install ibm-db
import ibm_db
# connectction details
dsn_hostname = "764264db-9824-4b7c-82df-40d1b13897c2.bs2io90l08kqb1od8lcg.databases.appdomain.cloud" # e.g.: "dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net"
dsn_uid = "xbt38733" # e.g. "abc12345"
dsn_pwd = "eZliaghna1RicCav" # e.g. "7dBZ3wWt9XN6$o0J"
dsn_port = "32536" # e.g. "50000"
dsn_database = "bludb" # i.e. "BLUDB"
dsn_driver = "{IBM DB2 ODBC DRIVER}" # i.e. "{IBM DB2 ODBC DRIVER}"
dsn_protocol = "TCPIP" # i.e. "TCPIP"
dsn_security = "SSL" # i.e. "SSL"
#Create the dsn connection string
dsn = (
"DRIVER={0};"
"DATABASE={1};"
"HOSTNAME={2};"
"PORT={3};"
"PROTOCOL={4};"
"UID={5};"
"PWD={6};"
"SECURITY={7};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port, dsn_protocol, dsn_uid, dsn_pwd, dsn_security)
# create connection
conn = ibm_db.connect(dsn, "", "")
print ("Connected to database: ", dsn_database, "as user: ", dsn_uid, "on host: ", dsn_hostname)
# create table
SQL = """CREATE TABLE IF NOT EXISTS products(rowid INTEGER PRIMARY KEY NOT NULL,product varchar(255) NOT NULL,category varchar(255) NOT NULL)"""
create_table = ibm_db.exec_immediate(conn, SQL)
print("Table created")
# insert data
SQL = "INSERT INTO products(rowid,product,category) VALUES(?,?,?);"
stmt = ibm_db.prepare(conn, SQL)
row1 = (1,'Television','Electronics')
ibm_db.execute(stmt, row1)
row2 = (2,'Laptop','Electronics')
ibm_db.execute(stmt, row2)
row3 = (3,'Mobile','Electronics')
ibm_db.execute(stmt, row3)
# query data
SQL="SELECT * FROM products"
stmt = ibm_db.exec_immediate(conn, SQL)
tuple = ibm_db.fetch_tuple(stmt)
while tuple != False:
print (tuple)
tuple = ibm_db.fetch_tuple(stmt)
# export the fields name and type from collection test into the file data.csv
# close connection
ibm_db.close(conn)