python3.4用函数操作mysql5.7数据库

 #!/usr/bin/env python

 # -*- coding:utf-8 -*-

 # __author__ = "blzhu"

 """

 python study

 Date：2017

 """

 # -*- coding: utf-8 -*-

 __author__ = 'djstava@gmail.com'

 import logging

 import pymysql

 class MySQLCommand(object):

     def __init__(self, host, port, user, passwd, db, table, charset):

         self.host = host

         self.port = port

         self.user = user

         self.password = passwd

         self.db = db

         self.table = table

         self.charset = charset

     def connectMysql(self):

         try:

             self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, passwd=self.password,

                                         db=self.db, charset=self.charset)

             self.cursor = self.conn.cursor()

             print('connect ' + self.table + ' correctly!')

         except:

             print('connect mysql error.')

     def queryMysql(self):

         sql = "SELECT * FROM " + self.table

         try:

             print("query Mysql:")

             self.cursor.execute(sql)

             #row = self.cursor.fetchone()

             for d in self.cursor:

                 print(str(d[0]), str(d[1]), str(d[2]))

                 # print(row)

         except:

             print(sql + ' execute failed.')

     def insertMysql(self, id, name, sex):

         sql = "INSERT INTO " + self.table + " VALUES(" + id + "," + "'" + name + "'," + "'" + sex + "')"

         try:

             print("insert Mysql:")

             self.cursor.execute(sql)

             print(sql)

         except:

             print("insert failed.")

     def updateMysqlSN(self, name, sex):

         sql = "UPDATE " + self.table + " SET sex='" + sex + "'" + " WHERE name='" + name + "'"

         print("update sn:" + sql)

         try:

             self.cursor.execute(sql)

             self.conn.commit()

         except:

             self.conn.rollback()

     def deleteMysql(self, id):  # 删除

         sql = "DELETE FROM %s  WHERE id='%s'" % (self.table,id)

         #"delete from student where zid='%s'" % (id)

         try:

             self.cursor.execute(sql)

             print(sql)

             self.conn.commit()

             print("delete the " + id + "th row successfully!")

         except:

             print("delete failed!")

             self.conn.rollback()

     def closeMysql(self):

         self.conn.commit()  # 不执行此句，所作的操作不会写入到数据库中

         self.cursor.close()

         self.conn.close()

 if __name__ == '__main__':

     zblmysql = MySQLCommand(host='localhost', user='root', passwd='root', db='zbltest1', port=3306, table='student2',

                             charset='utf8')

     zblmysql.connectMysql()

     zblmysql.queryMysql()

     zblmysql.insertMysql('', 'zbl5', 'man')

     zblmysql.queryMysql()

     zblmysql.deleteMysql(id=2)

     zblmysql.queryMysql()

     zblmysql.updateMysqlSN(name='zbl5',sex='woman')

     zblmysql.queryMysql()

     zblmysql.closeMysql()