· 9 years ago · Dec 07, 2016, 03:32 AM
1# Contoh kode untuk mengkoneksikan ke database MySQL
2
3#!/usr/bin/python
4
5import MySQLdb
6
7# Open database connection
8db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
9
10# prepare a cursor object using cursor() method
11cursor = db.cursor()
12
13# execute SQL query using execute() method.
14cursor.execute("SELECT VERSION()")
15
16# Fetch a single row using fetchone() method.
17data = cursor.fetchone()
18
19print "Database version : %s " % data
20
21# disconnect from server
22db.close()
23
24# Contoh kode untuk membuat sebuah tabel:
25
26#!/usr/bin/python
27
28import MySQLdb
29
30# Open database connection
31db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
32
33# prepare a cursor object using cursor() method
34cursor = db.cursor()
35
36# Drop table if it already exist using execute() method.
37cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
38
39# Create table as per requirement
40sql = """CREATE TABLE EMPLOYEE (
41 FIRST_NAME CHAR(20) NOT NULL,
42 LAST_NAME CHAR(20),
43 AGE INT,
44 SEX CHAR(1),
45 INCOME FLOAT )"""
46
47cursor.execute(sql)
48
49# disconnect from server
50db.close()
51
52# Contoh kode untuk menambahkan sebuah data:
53
54#!/usr/bin/python
55
56import MySQLdb
57
58# Open database connection
59db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
60
61# prepare a cursor object using cursor() method
62cursor = db.cursor()
63
64# Prepare SQL query to INSERT a record into the database.
65sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
66 LAST_NAME, AGE, SEX, INCOME)
67 VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
68try:
69 # Execute the SQL command
70 cursor.execute(sql)
71 # Commit your changes in the database
72 db.commit()
73except:
74 # Rollback in case there is any error
75 db.rollback()
76
77# disconnect from server
78db.close()
79
80
81# Contoh kode untuk membaca sebuah data:
82
83#!/usr/bin/python
84
85import MySQLdb
86
87# Open database connection
88db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
89
90# prepare a cursor object using cursor() method
91cursor = db.cursor()
92
93# Prepare SQL query to INSERT a record into the database.
94sql = "SELECT * FROM EMPLOYEE \
95 WHERE INCOME > '%d'" % (1000)
96try:
97 # Execute the SQL command
98 cursor.execute(sql)
99 # Fetch all the rows in a list of lists.
100 results = cursor.fetchall()
101 for row in results:
102 fname = row[0]
103 lname = row[1]
104 age = row[2]
105 sex = row[3]
106 income = row[4]
107 # Now print fetched result
108 print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
109 (fname, lname, age, sex, income )
110except:
111 print "Error: unable to fecth data"
112
113# disconnect from server
114db.close()
115
116# Contoh kode untuk meng update data:
117
118#!/usr/bin/python
119
120import MySQLdb
121
122# Open database connection
123db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
124
125# prepare a cursor object using cursor() method
126cursor = db.cursor()
127
128# Prepare SQL query to UPDATE required records
129sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
130 WHERE SEX = '%c'" % ('M')
131try:
132 # Execute the SQL command
133 cursor.execute(sql)
134 # Commit your changes in the database
135 db.commit()
136except:
137 # Rollback in case there is any error
138 db.rollback()
139
140# disconnect from server
141db.close()
142
143$ Contoh kode untuk menghapus data:
144
145#!/usr/bin/python
146
147import MySQLdb
148
149# Open database connection
150db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
151
152# prepare a cursor object using cursor() method
153cursor = db.cursor()
154
155# Prepare SQL query to DELETE required records
156sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
157try:
158 # Execute the SQL command
159 cursor.execute(sql)
160 # Commit your changes in the database
161 db.commit()
162except:
163 # Rollback in case there is any error
164 db.rollback()
165
166# disconnect from server
167db.close()