Python--模块--pymysql的使用

如何使用？

• 建立连接--》建立游标--》执行命令...

• # pip3 install pymysql
  import pymysql
  
  conn = pymysql.connect(host="127.0.0.1", port=3306,database="day35",user="root",password="123456")
  # 建立一个游标，这个游标可以类比为cmd命令行
  cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
  # cursor=pymysql.cursors.DictCursor 设为以字典的形式返回，不设置，则以元组的形式
  
  
  rows = cursor.execute("select * from ttt;") # 这里输入sql语句
  print(rows)
  
  res = cursor.fetchall() # 显示所有值
  print(res)
  
  cursor.close()
  conn.close()

 

如何批量插入数据？

• # 导入mysql驱动
  import pymysql
  
  conn = pymysql.connect(host="127.0.0.1", port=3306,database="day35",user="root",password="123456")
  cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
  
  # 创建表sites
  cursor.execute('drop table if exists sites')
  cursor.execute('create table sites(name varchar(20) primary key ,url varchar(200))')
  
  # 插入多行记录,注意mysql的占位符是%s
  sql = "insert into sites(name,url) values (%s,%s)"
  val = [
      ("Google","https://www.google.com"),
      ("Github","https://www.github.com"),
      ("Taobao","https://www.taobao.com"),
  ]
  
  cursor.executemany(sql,val)
  
  # 提交事务
  conn.commit()
  print(cursor.rowcount,"条记录插入成功")
  

    # print(cursor.lastrowid) # 查看最新的数据id

  
  
  cursor.close()
  
  # 运行查询：
  cursor = conn.cursor()
  cursor.execute('select * from sites')
  values = cursor.fetchall()
  for x in values:
      print(x)
      
  # 关闭cursor和connection 
  cursor.close()
  conn.close()

   

 如何写成一个函数来插入数据？

• 示例

• # 导入mysql驱动
  import pymysql
  
  
  def insert_mysql(dic: dict):
      '''
      输入:dict字典
      无输出
      '''
      db = pymysql.connect(
          host="192.168.49.160",
          port=3306,
          database="day01",
          user="liqi",
          password="liqi123456"
      )
      cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
  
      # 插入多行记录,注意mysql的占位符是%s
      sql = "insert into t1('test_type,produce_name') " \
            "values (%s,%s)"
      # 从输入的字典dic中拿数据,拿不到的话设置为null
      test_type = dic.get('test_type')
      produce_name = dic.get('produce_name')    
  
      # *=====================================================================*
  
      # 构建数据
      val = [
          test_type, produce_name,
      ]
      try:
          # 执行SQL语句,插入数据(插入多条数据用executemany)
          cursor.execute(sql, val)
          # 提交事务
          db.commit()
      except:
          # 发生错误时回滚
          db.rollback()
  
      print(cursor.rowcount, "条记录插入成功")
      # print(cursor.lastrowid) # 查看最新的数据id
      # 关闭数据库连接
      cursor.close()
  
  
  if __name__ == '__main__':
      dic = {
          'test_type': 'gemm',
          'produce_name': "Liqi1234",
      }
      insert_mysql(dic)

   

 

 插入数据后，如何查询最新那条出来？

• 示例代码：

• # 导入mysql驱动
  import pymysql
  
  
  def insert_mysql(dic: dict):
      '''
      输入:dict字典
      无输出
      '''
      db = pymysql.connect(
          host="192.168.49.160",
          port=3306,
          database="day01",
          user="liqi",
          password="liqi123456"
      )
      cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
  
      # 插入多行记录,注意mysql的占位符是%s
      sql = "insert into t1('test_type,produce_name') " \
            "values (%s,%s)"
      # 从输入的字典dic中拿数据,拿不到的话设置为null
      test_type = dic.get('test_type')
      produce_name = dic.get('produce_name')
  
      # *=====================================================================*
  
      # 构建数据
      val = [
          test_type, produce_name,
      ]
      try:
          # 执行SQL语句,插入数据(插入多条数据用executemany)
          cursor.execute(sql, val)
          # 提交事务
          db.commit()
      except:
          # 发生错误时回滚
          db.rollback()
  
      print(cursor.rowcount, "条记录插入成功")
  
      #  *======================查询====================*
      last_id = cursor.lastrowid - 1  # 记录上面的id 注意-1
  
      cursor = db.cursor(cursor=pymysql.cursors.DictCursor)  # 新建一个查询
      select_sql = "select * from t1"  # SQL语句
      cursor.execute(select_sql)
      cursor.scroll(last_id, mode="absolute")  # 移动游标   # 绝对模式
      
      print(cursor.fetchone())  # 查询出最新的
  
      # 关闭数据库连接
      cursor.close()
  
  
  if __name__ == '__main__':
      dic = {
          'test_type': 'gemm',
          'produce_name': "Liqi1234",
      }
      insert_mysql(dic)

   

• 

   

   

 

 

 

 

 

 

 

 

 

参考资料：书籍《python程序员面试宝典--猿媛之家著》--第五章-数据库相关