PyMySQL的使用

一、关顾PyMySQL的介绍

如果你学过python2.7使用过django开发过项目就会知道,在2.7的时候使用数据库连接工具默认是MySQLDB, 在python3.0后python采用PyMySQL连接,因此也只介绍PyMySQL的使用

• 1、在虚拟环境下安装包

  pip install pymysql
  

二、基本的使用

• 1、创建连接数据库

  import pymysql
  
  if __name__ == "__main__":
      try:
          # 创建一个连接
          db = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='py_test', port=3306,
                              charset='utf8')
  
          # 创建一个连接的句柄
          cursor = db.cursor()
  
          # 使用execute(）方法查询SQL语句
          cursor.execute("SELECT VERSION()")
  
          # 使用fetchone获取单条数据
          data = cursor.fetchone()
  
          print("Datebase version: %s" % data)
      except pymysql.Error as e:
          print(e)
      finally:
          if db:
              db.close()
  

• 2、创建数据库

  import pymysql
  
  if __name__ == "__main__":
      try:
          # 创建数据库连接
          db = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='py_test', port=3306,
                              charset='utf8')
          # 获取连接的句柄
          cursor = db.cursor()
  
          # 使用execute()执行sql语句
          cursor.execute('DROP TABLE IF EXISTS user')
          # 使用预处理语句创建表
          sql = """CREATE TABLE user(
                      id int primary key auto_increment comment "用户id",
                      name varchar (100) not null unique comment "用户名"
                      passwd varchar (100) not null comment "用户密码"
                )"""
  
          cursor.execute(sql)
      except pymysql.Error as e:
          print(e)
      finally:
          if db:
              db.close()
  

• 3、插入数据

  import pymysql
  
  if __name__ == "__main__":
      try:
          # 创建数据库连接
          db = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='py_test', port=3306,
                              charset='utf8')
          # 获取连接的句柄
          cursor = db.cursor()
          name = input('用户名:').strip()
          passwd = input('密码:').strip()
          # 定义sql语句
          sql = """
                  insert into user(name, passwd) values ('%s', '%s') 
              """ %(name, passwd)
          cursor.execute(sql)
          # 提交
          db.commit()
      except pymysql.Error as e:
          print(e)
      finally:
          if db:
              db.close()
  

• 4、查询数据

  import pymysql
  
  if __name__ == "__main__":
      try:
          # 创建数据库连接
          db = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='py_test', port=3306,
                              charset='utf8')
          # 获取连接的句柄
          cursor = db.cursor()
          # 执行查询语句
          sql = """
              select * from user
          """
          # 执行sql语句
          cursor.execute(sql)
          # 获取数据
          result = cursor.fetchall()
          for row in result:
              id = row[2]
              name = row[0]
              passwd = row[1]
              print('id=%s, name=%s, passwd=%s' % (id, name, passwd))
  
          print('----华丽的分割线-----')
          # 查询一条语句
          cursor.execute(sql)
          data = cursor.fetchone()
          print(data)
      except pymysql.Error as e:
          print(e)
      finally:
          if db:
              db.close()
  

• 5、修改数据

  import pymysql
  
  if __name__ == "__main__":
      try:
          # 创建数据库连接
          db = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='py_test', port=3306,
                              charset='utf8')
          # 获取连接的句柄
          cursor = db.cursor()
          name = input('输入用户名:').strip()
          id = int(input('输入id:').strip())
          sql = """
              update user set name ='%s' where id='%d'
          """ % (name, id)
          cursor.execute(sql)
          db.commit()
      except pymysql.Error as e:
          print(e)
      finally:
          if db:
              db.close()
  

• 6、删除数据

  import pymysql
  
  if __name__ == "__main__":
      try:
          # 创建数据库连接
          db = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='py_test', port=3306,
                              charset='utf8')
          # 获取连接的句柄
          id = int(input('请输入要删除的id:').strip())
          cursor = db.cursor()
          sql = """
              delete from user where id = '%d'
          """ % id
          cursor.execute(sql)
          db.commit()
      except pymysql.Error as e:
          print(e)
      finally:
          if db:
              db.close()