目的

掌握python对MYSQL数据库的增删改查

实现

1:python对MYSQL数据库的增删改查代码练习
2:封装增删改查类,以供调用
3:mysql自己查阅资料安装

代码

代码练习

   import pymysql
  
  # 创建连接pymysql.connect(主机ip,用户名,密码,表名)
  db = pymysql.connect("localhost", "root", "123456", "student")
  # 创建一个cursor对象
  cursor = db.cursor()
  
  # sql语句
  
  # 删除表(user是表名)
  # sql = "drop table if exists user"
  
  # 创建表
  # sql = "create table bandcard(id int auto_increment primary key, money int not null)"
  
  # 插入数据
  # sql = "insert into bandcard values (0,300)"
  # sql = "insert into bandcard values (0,100),(0,200),(0,300),(0,400),(0,900),(0,600)"
  
  # 更新数据
  # sql = "update bandcard set money=10000000 where id=1"
  
  # 删除数据
  # sql = "delete from bandcard where money=0"
  
  # 查询数据
  sql = "select * from bandcard where money>300"
  
  # 执行sql语句
  try:
      cursor.execute(sql)
      # fetchall()获取查询符合条件所有的数据
      resultList = cursor.fetchall()
      for row in resultList:
          print(row[0], row[1])
  
      # 查询不要commit
      # db.commit()
  except:
      # 插入出错,回滚
      db.rollback()
  # 获取返回信息
  # data = cursor.fetchone()
  
  # print(data)
  # 关掉cursor指针
  cursor.close()
  # 关掉连接
  db.close()

封装

main

  from mysqlDef import MysqlDef
  
  
  def main():
      # 连接
      dataC = MysqlDef("localhost", "root", "123456", "student")
      dataC.connection()
      # 查询返回所有符合条件的结果
      resultAll = dataC.getAllData("select * from bandcard where money>300")
      for row in resultAll:
          print(row[0], row[1])
  
      # 查询返回所有符合条件的第一条结果
      resultOne = dataC.getOneData("select * from bandcard where money>300")
      print(resultOne)
  
      # 创建表
      dataC.create("create table card(id int auto_increment primary key, money int not null)")
  
      # 插入一行和多行
      dataC.insert("insert into card values (0,4000)")
      dataC.insert("insert into card values (0,100),(0,200),(0,300),(0,400),(0,900),(0,600)")
  
      # 更新数据
      dataC.update("update card set money=10000000 where id=2")
  
      # 删除数据
      dataC.delete("delete from card where money=10000000")
  
  if __name__ == '__main__':
      main()

mysqlDef

  import pymysql
  
  
  class MysqlDef(object):
      def __init__(self, ip, userName, passWord, dataBase):
          self.dataBase = dataBase
          self.ip = ip
          self.userName = userName
          self.passWord = passWord
  
      def connection(self):
          self.db = pymysql.connect(self.ip, self.userName, self.passWord, self.dataBase)
          self.cursor = self.db.cursor()
  
      def close(self):
          self.cursor.close()
          self.db.close()
  
      def getOneData(self, sql):
          result = None
          try:
              self.connection()
              self.cursor.execute(sql)
              result = self.cursor.fetchone()
              self.close()
          except:
              print("查询失败")
          return result
  
      def getAllData(self, sql):
          result = None
          try:
              self.connection()
              self.cursor.execute(sql)
              result = self.cursor.fetchall()
              self.close()
          except:
              print("查询失败")
          return result
  
      def insert(self, sql):
          return self.__edit(sql)
  
      def create(self, sql):
          return self.__edit(sql)
  
      def update(self, sql):
          return self.__edit(sql)
  
      def delete(self, sql):
          return self.__edit(sql)
  
      def __edit(self, sql):
          count = 0
          try:
              self.connection()
              count = self.cursor.execute(sql)
              self.db.commit()
              self.close()
          except:
              print("提交事务失败!")
              self.db.rollback()
          return count