当前位置：移动技术网 > IT编程>脚本编程>Python > Python操作mysql数据库实现增删查改功能的方法

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

2018年01月27日 | 移动技术网IT编程 | 我要评论

舞曲mp3,淘宝网包包,安亭事件

本文实例讲述了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程序设计有所帮助。

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！点击进行留言回复

python如何查看网页代码

用python查看网页代码的方法：1、使用“import”导入requests包import requests2、使用requests包的get()函数通过网页... [阅读全文]
Python如何用wx模块创建文本编辑器

用python的wx模块创建文本编辑器的方法：1、设置按钮的位置import wxapp = wx.app()win = wx.frame(none,title... [阅读全文]
python如何保存文本文件

python保存文本文件的方法：使用python内置的open()类可以打开文本文件，向文件里面写入数据可以用write()函数，写完之后，使用close()函... [阅读全文]
python如何编写win程序

python可以编写win程序。win程序的格式是exe，下面我们就来看一下使用python编写exe程序的方法。编写好python程序后py2exe模块即可将... [阅读全文]
Python替换NumPy数组中大于某个值的所有元素实例

我有一个2d(二维) numpy数组，并希望用255.0替换大于或等于阈值t的所有值。据我所知，最基础的方法是：shape = arr.shaperesult ... [阅读全文]
使用Numpy对特征中的异常值进行替换及条件替换方式

原始数据为excel文件，由传感器获得，通过pyhton xlrd模块读入，读入后为数组形式，由于其存在部分异常值和缺失值，所以便利用numpy对其中的异常值进... [阅读全文]
Python 实现将numpy中的nan和inf,nan替换成对应的均值

nan：not a numberinf：infinity;正无穷numpy中的nan和inf都是float类型t!=t 返回bool类型的数组(矩阵)np.co... [阅读全文]
给ubuntu18安装python3.7的详细教程

参考文章准备工作安装工具sudo apt updatesudo apt upgradesudo apt install gccsudo apt install ... [阅读全文]
python爬虫把url链接编码成gbk2312格式过程解析

1. 问题　　抓取某个网站，发现请求参数是乱码格式，这是点击 textview，发现请求参数如下图所示3. 那么=%b9%fa%ce%f1%d4%ba%b7%a... [阅读全文]
pyecharts在数据可视化中的应用详解

使用pyecharts进行数据可视化安装 pip install pyecharts也可以在pycharm软件里进行下载pyecharts库包。下载成功后进行查... [阅读全文]

网友评论


验证码：

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

2018年01月27日 | 移动技术网IT编程 | 我要评论

您可能感兴趣的文章:

相关文章:

网友评论