Python操作mysql数据库实现增删查改功能的方法

yipeiwu_com5年前Python基础

本文实例讲述了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:
        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文件对数据进行操作

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

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

相关文章

在Python中使用Neo4j的方法

在Python中使用Neo4j的方法

Neo4j是面向对象基于Java的 ,被设计为一个建立在Java之上、可以直接嵌入应用的数据存储。此后,其他语言和平台的支持被引入,Neo4j社区获得持续增长,获得了越来越多的技术支持者...

实例讲解python函数式编程

函数式编程是使用一系列函数去解决问题,按照一般编程思维,面对问题时我们的思考方式是“怎么干”,而函数函数式编程的思考方式是我要“干什么”。 至于函数式编程的特点暂不总结,我们直接拿例子来...

在OpenCV里使用Camshift算法的实现

在OpenCV里使用Camshift算法的实现

前面学习过Meanshift算法,在观察这个结果标记时,会发现有这样一个问题,如下图: 汽车比较远时,用一个很小的窗口就可以把它框住,这是符合近大远小的投影原理,当比较近的时候如下:...

如何高效使用Python字典的方法详解

前言 众所周知字典(dict)对象是 Python 最常用的数据结构,社区曾有人开玩笑地说:"Python企图用字典装载整个世界",字典在Python中的重要性不言而喻,这里整理了几个关...

如何使用Flask-Migrate拓展数据库表结构

前言 在我们用 sqlchemy 模块创建完几个表时,如果在实际生产环境中,需要对表结构进行更改,应该怎么办呢?总不能把表删除了吧,这样数据就会丢失了。 更好的解决办法是使用数据库迁移框...