python3.4用函數操作mysql5.7數據庫

 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 # __author__ = "blzhu"
 4 """
 5 python study
 6 Date：2017
 7 """
 8 # -*- coding: utf-8 -*-
 9 __author__ = 'djstava@gmail.com'
10 
11 import logging
12 import pymysql
13 
14 
15 class MySQLCommand(object):
16 def __init__(self, host, port, user, passwd, db, table, charset):
17 self.host = host
18 self.port = port
19 self.user = user
20 self.password = passwd
21 self.db = db
22 self.table = table
23 self.charset = charset
24 
25 def connectMysql(self):
26 try:
27 self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, passwd=self.password,
28 db=self.db, charset=self.charset)
29 self.cursor = self.conn.cursor()
30 print('connect ' + self.table + ' correctly!')
31 except:
32 print('connect mysql error.')
33 
34 def queryMysql(self):
35 sql = "SELECT * FROM " + self.table
36 
37 try:
38 print("query Mysql:")
39 self.cursor.execute(sql)
40 #row = self.cursor.fetchone()
41 for d in self.cursor:
42 print(str(d[0]), str(d[1]), str(d[2]))
43 # print(row)
44 
45 except:
46 print(sql + ' execute failed.')
47 
48 def insertMysql(self, id, name, sex):
49 sql = "INSERT INTO " + self.table + " VALUES(" + id + "," + "'" + name + "'," + "'" + sex + "')"
50 try:
51 print("insert Mysql:")
52 self.cursor.execute(sql)
53 print(sql)
54 except:
55 print("insert failed.")
56 
57 def updateMysqlSN(self, name, sex):
58 sql = "UPDATE " + self.table + " SET sex='" + sex + "'" + " WHERE name='" + name + "'"
59 print("update sn:" + sql)
60 
61 try:
62 self.cursor.execute(sql)
63 self.conn.commit()
64 except:
65 self.conn.rollback()
66 
67 def deleteMysql(self, id): # 刪除
68 sql = "DELETE FROM %s WHERE id='%s'" % (self.table,id)
69 #"delete from student where zid='%s'" % (id)
70 try:
71 self.cursor.execute(sql)
72 print(sql)
73 self.conn.commit()
74 print("delete the " + id + "th row successfully!")
75 except:
76 print("delete failed!")
77 self.conn.rollback()
78 
79 def closeMysql(self):
80 self.conn.commit() # 不執行此句，所作的操作不會寫入到數據庫中
81 self.cursor.close()
82 self.conn.close()
83 
84 
85 if __name__ == '__main__':
86 zblmysql = MySQLCommand(host='localhost', user='root', passwd='root', db='zbltest1', port=3306, table='student2',
87 charset='utf8')
88 zblmysql.connectMysql()
89 zblmysql.queryMysql()
90 zblmysql.insertMysql('5', 'zbl5', 'man')
91 zblmysql.queryMysql()
92 zblmysql.deleteMysql(id=2)
93 zblmysql.queryMysql()
94 zblmysql.updateMysqlSN(name='zbl5',sex='woman')
95 zblmysql.queryMysql()
96 zblmysql.closeMysql()