概述

本文实例讲述了Python操作MysqL数据库实现增删查改功能的方法。分享给大家供大家参考，具体如下：

#coding=utf-8
import MysqLdb
class 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.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,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.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)"
        param = (1009,'a niu','dong shi zhang',NULL,'2001-11-17',50000,10)
        n = cursor.execute(sql,n
      elif table=="salgrade":
        sql = "insert into salgrade values(%s,%s)"
        param = (1,7000,12000)
        n = cursor.execute(sql,n
      elif table=="stu":
        sql = "insert into stu values(%s,%s)"
        param = ('1','001','23','nan','bei jing','1500')
        n = cursor.execute(sql,n
      else:
        print u"输入错误的表名，表明为dept、emp、salgrade、stu..."
    except MysqLdb.Error,e.args[1])
  def inser_muldata_table(self,%s)"
        param = ((10,'jiao yan bu','bei jing'),(20,'xue gong bu','shang hai'),(30,'xiao shou bu','guang zhou'))
        n = cursor.executemany(sql,%s)"
        param = ((1004,'liu bei','jing li',1009,'2001-04-02',29750,20),(1006,'guan yu','2001-05-01',28500,30),(1008,'zhu ge liang','fen xi shi',1004,'2007-04-19',30000,(1013,'pang','2001-12-03',(1002,'dai','xiao shou yuan',1006,'2001-02-20',16000,3000,(1003,'tian zheng','2001-02-22',12500,5000,(1005,'xie xun','2001-09-28',14000,(1010,'wei yi xiao','2001-09-08',15000,30)
             )
        n = cursor.executemany(sql,%s)"
        param = ((2,12010,14000),(3,14010,20000),(4,20010,30000),(5,30010,99990))
        n = cursor.executemany(sql,%s)"
        param = ( ('2','002','25','liao ning','2500'),('3','003','22','3500'),('4','004','1500'),('5','005','nv','1000'),('6','006','shan dong',('7','007','21','1600'),('8','008',('9','009','guang zhou',('10','010','18','shan xi',('11','011','hu bei','4500'),('12','24',('13',('14',('15',('16',('17',('18',('19',('20',('21',('22',('23',('24',('25',('26',('27',('28',('29',('30',('31',('32',('33','033',('34','034',('35','035',('36','036',('37','037',('38','038',('39','039',('40','040',('41','041',('42','042',('43','043',('44','044',('45','045',('46','046',('47','047',('48','048',('49','049',('50','050',('51','051',('52','052',('53','053',('54','054',('55','055','4500')
            )
        n = cursor.executemany(sql,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,n
      elif table=="salgrade":
        sql = "update salgrade set grade=%s where grade=" +no
        param = (upno)
        n = cursor.execute(sql,n
      elif table=="stu":
        sql = "update stu set sname=%s where sname=" +no
        param = (upno)
        n = cursor.execute(sql,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.args[1])
  def delete_data(self,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,n
      elif table=="salgrade":
        sql = "delete from salgrade where grade=%s"
        param = (upno)
        n = cursor.execute(sql,n
      elif table=="stu":
        sql = "delete from stu where sname=%s "
        param = (upno)
        n = cursor.execute(sql,e.args[1])
  del down_db(self):
    try:
      cursor.close()
      #提交
      conn.commit()
      #关闭
      conn.close()
    except MysqLdb.Error,e.args[1])
MysqLDB=MysqL_Oper("127.0.0.1","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文件对数据进行操作

更多关于Python相关内容感兴趣的读者可查看本站专题：《Python+MySQL数据库程序设计入门教程》、《Python常见数据库操作技巧汇总》、《Python数学运算技巧总结》、《Python数据结构与算法教程》、《Python函数使用技巧总结》、《Python字符串操作技巧汇总》、《Python入门与进阶经典教程》及《Python文件与目录操作技巧汇总》

希望本文所述对大家Python程序设计有所帮助。

总结

以上是编程之家为你收集整理的Python操作mysql数据库实现增删查改功能的方法全部内容，希望文章能够帮你解决Python操作mysql数据库实现增删查改功能的方法所遇到的程序开发问题。

如果您也喜欢它,动动您的小指点个赞吧