Python操作mysql資料庫實現增刪查改功能的方法，pythonmysql

Python操作mysql資料庫實現增刪查改功能的方法，pythonmysql

本文執行個體講述了Python操作mysql資料庫實現增刪查改功能的方法。分享給大家供大家參考，具體如下：

#coding=utf-8import MySQLdbclass Mysql_Oper:  def __init__(self,host,user,passwd,db):    self.host=host    self.user=user    self.passwd=passwd    self.database=db  def db_connecet(self):    try:      #串連      conn=MySQLdb.connect(host=self.host,user=self.user,passwd=self.passwd,db=self.database,charset="utf8")      cursor = conn.cursor()    except MySQLdb.Error,e:        print "Mysql Error %d: %s" % (e.args[0], e.args[1])  def drop_table(self,table):    try:      #刪除表      sql = "drop table if exists" + table      cursor.execute(sql)    except MySQLdb.Error,e:        print "Mysql Error %d: %s" % (e.args[0], e.args[1])  def create_table(self,table):    try:      if table=="dept":        #建立        sql = "create table if not exists dept(deptno int primary key, dname varchar(50),loc varchar(50))"        cursor.execute(sql)      elif table=="emp":        sql == "create table if not exists emp(empno INT PRIMARY KEY,ename VARCHAR(50),job VARCHAR(50),mgr INT,hiredate DATE,sal DECIMAL(7,2),COMM DECIMAL(7,2),deptno INT,loc varchar(50),CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno))"        cursor.execute(sql)      elif table=="salgrade":        sql = "create table if not exists salgrade(grade INT PRIMARY KEY,losal INT,hisal INT)"        cursor.execute(sql)      elif table=="stu":        #建立        sql = "create table if not exists dept(sid INT PRIMARY KEY,sname VARCHAR(50),age INT,gander VARCHAR(10),province VARCHAR(50),tuition INT)"        cursor.execute(sql)      else:        print u"輸入錯誤的表名，表明為dept、emp、salgrade、stu..."    except MySQLdb.Error,e:        print "Mysql Error %d: %s" % (e.args[0], e.args[1])  def inser_onedata_table(self,table):    try:      if table=="dept":        sql = "insert into dept values(%s,%s,%s)"        param = (40, 'cai wu bu', 'wu han')        n = cursor.execute(sql,param)        print 'insert',n      elif table=="emp":        sql = "insert into emp values(%s,%s,%s,%s,%s,%s,%s,%s)"        param = (1009, 'a niu', 'dong shi zhang', NULL, '2001-11-17', 50000, NULL, 10)        n = cursor.execute(sql,param)        print 'insert',n      elif table=="salgrade":        sql = "insert into salgrade values(%s,%s,%s)"        param = (1, 7000, 12000)        n = cursor.execute(sql,param)        print 'insert',n      elif table=="stu":        sql = "insert into stu values(%s,%s,%s,%s,%s,%s)"        param = ('1', '001', '23', 'nan', 'bei jing', '1500')        n = cursor.execute(sql,param)        print 'insert',n      else:        print u"輸入錯誤的表名，表明為dept、emp、salgrade、stu..."    except MySQLdb.Error,e:        print "Mysql Error %d: %s" % (e.args[0], e.args[1])  def inser_muldata_table(self,table):    try:      if table=="dept":        sql = "insert into dept values(%s,%s,%s)"        param = ((10, 'jiao yan bu', 'bei jing'),(20, 'xue gong bu', 'shang hai'),(30, 'xiao shou bu', 'guang zhou'))        n = cursor.executemany(sql,param)        print 'insert',n      elif table=="emp":        sql = "insert into emp values(%s,%s,%s,%s,%s,%s,%s,%s)"        param = ((1004, 'liu bei', 'jing li', 1009, '2001-04-02', 29750, NULL, 20),             (1006, 'guan yu', 'jing li', 1009, '2001-05-01', 28500, NULL, 30),             (1008, 'zhu ge liang', 'fen xi shi', 1004, '2007-04-19', 30000, NULL, 20),             (1013, 'pang', 'fen xi shi', 1004, '2001-12-03', 30000, NULL, 20),             (1002, 'dai', 'xiao shou yuan', 1006, '2001-02-20', 16000, 3000, 30),             (1003, 'tian zheng', 'xiao shou yuan', 1006, '2001-02-22', 12500, 5000, 30),             (1005, 'xie xun', 'xiao shou yuan', 1006, '2001-09-28', 12500, 14000, 30),             (1010, 'wei yi xiao', 'xiao shou yuan', 1006, '2001-09-08', 15000, 0, 30)             )        n = cursor.executemany(sql,param)        print 'insert',n      elif table=="salgrade":        sql = "insert into salgrade values(%s,%s,%s)"        param = ((2, 12010, 14000),(3, 14010, 20000),(4, 20010, 30000),(5, 30010, 99990))        n = cursor.executemany(sql,param)        print 'insert',n      elif table=="stu":        sql = "insert into stu values(%s,%s,%s,%s,%s,%s)"        param = ( ('2', '002', '25', 'nan', 'liao ning', '2500'),               ('3', '003', '22', 'nan', 'bei jing', '3500'),               ('4', '004', '25', 'nan', 'bei jing', '1500'),               ('5', '005', '23', 'nv', 'bei jing', '1000'),               ('6', '006', '22', 'nv', 'shan dong', '2500'),               ('7', '007', '21', 'nv', 'bei jing', '1600'),               ('8', '008', '23', 'nan', 'bei jing', '3500'),               ('9', '009', '23', 'nv', 'guang zhou', '2500'),               ('10', '010', '18', 'nan', 'shan xi', '3500'),               ('11', '011', '23', 'nan', 'hu bei', '4500'),               ('12', '011', '24', 'nan', 'bei jing', '1500'),               ('13', '011', '24', 'nan', 'liao ning', '2500'),               ('14', '011', '22', 'nan', 'bei jing', '3500'),               ('15', '011', '25', 'nan', 'bei jing', '1500'),               ('16', '011', '23', 'nv', 'bei jing', '1000'),               ('17', '011', '22', 'nv', 'shan dong', '2500'),               ('18', '011', '21', 'nv', 'bei jing', '1600'),               ('19', '011', '23', 'nan', 'bei jing', '3500'),               ('20', '011', '23', 'nv', 'guang zhou', '2500'),               ('21', '011', '18', 'nan', 'shan xi', '3500'),               ('22', '011', '23', 'nan', 'hu bei', '4500'),               ('23', '011', '23', 'nan', 'bei jing', '1500'),               ('24', '011', '25', 'nan', 'liao ning', '2500'),               ('25', '011', '22', 'nan', 'bei jing', '3500'),               ('26', '011', '25', 'nan', 'bei jing', '1500'),               ('27', '011', '23', 'nv', 'bei jing', '1000'),               ('28', '011', '22', 'nv', 'shan dong', '2500'),               ('29', '011', '21', 'nv', 'bei jing', '1600'),               ('30', '011', '23', 'nan', 'bei jing', '3500'),               ('31', '011', '23', 'nv', 'guang zhou', '2500'),               ('32', '011', '18', 'nan', 'shan xi', '3500'),               ('33', '033', '23', 'nan', 'hu bei', '4500'),               ('34', '034', '23', 'nan', 'bei jing', '1500'),               ('35', '035', '25', 'nan', 'liao ning', '2500'),               ('36', '036', '22', 'nan', 'bei jing', '3500'),               ('37', '037', '25', 'nan', 'bei jing', '1500'),               ('38', '038', '23', 'nv', 'bei jing', '1000'),               ('39', '039', '22', 'nv', 'shan dong', '2500'),               ('40', '040', '21', 'nv', 'bei jing', '1600'),               ('41', '041', '23', 'nan', 'bei jing', '3500'),               ('42', '042', '23', 'nv', 'guang zhou', '2500'),               ('43', '043', '18', 'nan', 'shan xi', '3500'),               ('44', '044', '23', 'nan', 'hu bei', '4500'),               ('45', '045', '23', 'nan', 'bei jing', '1500'),               ('46', '046', '25', 'nan', 'liao ning', '2500'),               ('47', '047', '22', 'nan', 'bei jing', '3500'),               ('48', '048', '25', 'nan', 'bei jing', '1500'),               ('49', '049', '23', 'nv', 'bei jing', '1000'),               ('50', '050', '22', 'nv', 'shan dong', '2500'),               ('51', '051', '21', 'nv', 'bei jing', '1600'),               ('52', '052', '23', 'nan', 'bei jing', '3500'),               ('53', '053', '23', 'nv', 'guang zhou', '2500'),               ('54', '054', '18', 'nan', 'shan xi', '3500'),               ('55', '055', '23', 'nan', 'hu bei', '4500')            )        n = cursor.executemany(sql,param)        print 'insert',n      else:        print u"輸入錯誤的表名，表明為dept、emp、salgrade、stu..."    except MySQLdb.Error,e:        print "Mysql Error %d: %s" % (e.args[0], e.args[1])  def update_table(self,table,no,upno):    try:      if table=="dept":        #建立        sql = "update dept set deptno=%s where deptno=" +no        param = (upno)        n = cursor.execute(sql,param)        print 'update',n      elif table=="emp":        sql = "update emp set empno=%s where empno=" +no        param = (upno)        n = cursor.execute(sql,param)        print 'update',n      elif table=="salgrade":        sql = "update salgrade set grade=%s where grade=" +no        param = (upno)        n = cursor.execute(sql,param)        print 'update',n      elif table=="stu":        sql = "update stu set sname=%s where sname=" +no        param = (upno)        n = cursor.execute(sql,param)        print 'update',n      else:        print u"輸入錯誤的表名，表明為dept、emp、salgrade、stu..."    except MySQLdb.Error,e:        print "Mysql Error %d: %s" % (e.args[0], e.args[1])  def query_data(self,table):    try:      #查詢      sql="select * from "+table      n = cursor.execute(sql)      print cursor.fetchall()      for row in cursor.fetchall():        print row        for r in row:          print r    except MySQLdb.Error,e:        print "Mysql Error %d: %s" % (e.args[0], e.args[1])  def delete_data(self,table,no)    try:      if table=="dept":        sql = "delete from dept where deptno=%s"        param = (upno)        n = cursor.execute(sql,param)        print 'delete',n      elif table=="emp":        sql = "delete from emp where empno=%s"        param = (upno)        n = cursor.execute(sql,param)        print 'delete',n      elif table=="salgrade":        sql = "delete from salgrade where grade=%s"        param = (upno)        n = cursor.execute(sql,param)        print 'delete',n      elif table=="stu":        sql = "delete from stu where sname=%s "        param = (upno)        n = cursor.execute(sql,param)        print 'delete',n      else:        print u"輸入錯誤的表名，表明為dept、emp、salgrade、stu..."    except MySQLdb.Error,e:        print "Mysql Error %d: %s" % (e.args[0], e.args[1])  del down_db(self):    try:      cursor.close()      #提交      conn.commit()      #關閉      conn.close()    except MySQLdb.Error,e:        print "Mysql Error %d: %s" % (e.args[0], e.args[1])mysqlDB=Mysql_Oper("127.0.0.1","root","root","exam")mysqlDB.db_connecet()mysqlDB.drop_table("dept")for table in ["dept","emp","salgrade","stu"]  mysqlDB.create_table(table)  mysqlDB.inser_onedata_table(table)  mysqlDB.inser_muldata_table(table)  mysqlDB.query_data(table)mysqlDB.down_db()

後期我會把資料整合到CSV檔案中，操作CSV檔案對資料進行操作