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()