数据库——pymysql模块的使用（13）

1.基本用法——建立链接，获取游标，执行sql语句，关闭

• 建立远程链接账号和权限

• mysql> grant all on *.* to 'root'@'%' identified by '123';
  Query OK, 0 rows affected, 1 warning (0.40 sec)
  
  mysql> flush privileges;
  Query OK, 0 rows affected (0.23 sec)

• #-*- coding:utf-8 -*-
  import pymysql
  user = input("用户名：").strip()
  pwd = input("密码：").strip()
  #建立链接
  conn = pymysql.connect(
      host = "192.168.110.1",
      port = 3306,
      user = "root",
      password = "123",
      db = "db1",
      charset = "utf8"
  )
  
  #拿游标
  cursor = conn.cursor()
  
  #执行sql
  sql = 'select * from user_info where name = %s and pwd = %s'
  print(sql)
  rows = cursor.execute(sql,(user,pwd))
  #关闭
  cursor.close()
  conn.close()
  
  if rows:
      print("登录成功")
  else:
      print("登录失败")

2.增删改

• #-*- coding:utf-8 -*-
  import pymysql
  #建立链接
  conn = pymysql.connect(
      host = "192.168.110.1",
      port = 3306,
      user = "root",
      password = "123",
      db = "db1",
      charset = "utf8"
  )
  
  #拿游标
  cursor = conn.cursor()
  
  #执行sql
  ###########增############
  # sql = 'insert into user_info(name,pwd) values (%s,%s)'
  # #插入一条记录
  # rows = cursor.execute(sql,('xxx',123))
  # conn.commit()
  
  # #插入多条记录
  # rows = cursor.executemany(sql,[('xyy','ba'),('yxy','abc'),('yyy','dhdf')])
  # conn.commit()
  # print(rows)
  
  ###########删############
  # sql = "delete from user_info where id = %s ;"
  # rows = cursor.execute(sql,(3,))
  # conn.commit()
  # print(rows)
  
  ###########改############
  
  sql = 'update user_info set pwd = %s where name = "egon4" '
  rows = cursor.execute(sql,'aaa')
  conn.commit()
  print(rows)
  #关闭
  cursor.close()
  conn.close()

3.查

• #-*- coding:utf-8 -*-
  import pymysql
  #建立链接
  conn = pymysql.connect(
      host = "192.168.110.1",
      port = 3306,
      user = "root",
      password = "123",
      db = "db1",
      charset = "utf8"
  )
  
  #拿游标
  # cursor = conn.cursor()
  cursor = conn.cursor(pymysql.cursors.DictCursor)#以字典形式显示
  #执行sql
  ###########查############
  sql = 'select * from user_info;'
  rows = cursor.execute(sql)
  # print(rows)
  #一次取一个
  # print(cursor.fetchone())  #打印一条记录
  # print(cursor.fetchone())
  # print(cursor.fetchone())
  # print(cursor.fetchone())
  # print(cursor.fetchone())
  # print(cursor.fetchone())
  # print(cursor.fetchone())
  
  # print(cursor.fetchmany(2))#一次取多个
  # print(cursor.fetchall())#取所有
  
  cursor.scroll(3,mode='absolute') # 相对绝对位置移动
  # cursor.scroll(3,mode='relative') # 相对当前位置移动
  print(cursor.fetchone())
  cursor.scroll(1,mode='relative') # 相对当前位置移动
  print(cursor.fetchone())
  #关闭
  cursor.close()
  conn.close()

4.获取插入的最后一条数据的自增ID

• #-*- coding:utf-8 -*-
  import pymysql
  #建立链接
  conn = pymysql.connect(
      host = "192.168.110.1",
      port = 3306,
      user = "root",
      password = "123",
      db = "db1",
      charset = "utf8"
  )
  
  #拿游标
  cursor = conn.cursor()
  
  #执行sql
  ###########增############
  sql = 'insert into user_info(name,pwd) values (%s,%s)'
  rows = cursor.executemany(sql,[('xyyx','ba'),('yxyx','abc'),('yyyx','dhdf')])
  print(cursor.lastrowid)#在插入语句后查询
  conn.commit()
  
  #关闭
  cursor.close()
  conn.close()