XYjUh84c

1import pymysql
2import results as results
3
4db = pymysql.connect("localhost","root","PASSWORD","testdb" )
5
6cursor = db.cursor()
7
8cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
9
10sql = """CREATE TABLE EMPLOYEE (
11FIRST_NAME CHAR(20) NOT NULL,
12LAST_NAME CHAR(20),
13AGE INT,
14SEX CHAR(1),
15INCOME FLOAT)"""
16
17cursor.execute(sql)
18
19sql = """INSERT INTO EMPLOYEE (FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)
20VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
21
22try:
23    cursor.execute(sql)
24    # commit if no issues
25    db.commit()
26except:
27    print("exception")
28    db.rollback()
29
30sql = """INSERT INTO EMPLOYEE (FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)
31VALUES ('Juan', 'Ramirez', 30, 'M', 5000)"""
32try:
33    cursor.execute(sql)
34    # commit if no issues
35    db.commit()
36except:
37    print("exception")
38    db.rollback()
39
40sql = """INSERT INTO EMPLOYEE (FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)
41VALUES ('Mary', 'Smith', 20, 'M', 3000)"""
42try:
43    cursor.execute(sql)
44    # commit if no issues
45    db.commit()
46except:
47    print("exception")
48    db.rollback()
49
50sql = """select * from employee"""
51
52try:
53    cursor.execute(sql)
54
55    results = cursor.fetchall()
56    for row in results:
57        fname = row[0]
58        lname = row[1]
59        age = row[2]
60        sex = row[3]
61        income = row[4]
62
63        print("fname = %s, lname = %s, age = %d, sex = %s, income = %d" % (fname,lname, age, sex,income))
64
65except:
66    print("ERROR unable to fetch")
67
68db.close()