-
Notifications
You must be signed in to change notification settings - Fork 1
/
database.py
76 lines (61 loc) · 1.44 KB
/
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
import pymysql.cursors
# Open database connection
db = pymysql.connect("localhost","root","hackmeid","hackmeid" )
cursor = db.cursor()
cursor.execute("SELECT VERSION()")
data = cursor.fetchone()
print ("Database version : %s " % data)
db.close()
#Membuat Tabel Database
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
sql = """CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
cursor.execute(sql)
db.close()
#Operasi Insert
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()
#Read Operation
sql = "SELECT * FROM EMPLOYEE \
WHERE INCOME > '%d'" % (1000)
try:
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
print ("fname = %s,lname = %s,age = %d,sex = %s,income = %d" % \
(fname, lname, age, sex, income ))
except:
print ("Error: unable to fetch data")
db.close()
#Update Operation
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()
#Delete Operation
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()