当前位置:Gxlcms > 数据库问题 > [Python3]PyMySQL库

[Python3]PyMySQL库

时间:2021-07-01 10:21:17 帮助过:26人阅读

Cursor对象常用API:

  1. <span style="color: #000000;">close() 关闭当前cursor
  2. execute() 执行一个sql语句
  3. executemany() 执行批量sql语句
  4. fetchall() 取所有数据
  5. fetchone() 取一条数据</span>

一个基本示例

下面我们看一个基本的示例,让大家感受下pymysql库的能力。

基本前提,假设你在本地已经安装了MySQL服务或是你拥有远程访问某个MySQL服务的权限。

用下列sql创建一个数据表,以便下面的示例演示:

  1. <span style="color: #008000;">#</span><span style="color: #008000;"> -*- coding:utf-8 -*-</span>
  2. <span style="color: #0000ff;">import</span><span style="color: #000000;"> pymysql
  3. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> random
  4. </span><span style="color: #800080;">__author__</span> = <span style="color: #800000;">‘</span><span style="color: #800000;">谷白</span><span style="color: #800000;">‘</span>
  5. <span style="color: #0000ff;">if</span> <span style="color: #800080;">__name__</span> == <span style="color: #800000;">"</span><span style="color: #800000;">__main__</span><span style="color: #800000;">"</span><span style="color: #000000;">:
  6. </span><span style="color: #0000ff;">print</span>(<span style="color: #800000;">"</span><span style="color: #800000;">PyMySQL基本示例</span><span style="color: #800000;">"</span><span style="color: #000000;">)
  7. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 创建一个连接实例</span>
  8. conn =<span style="color: #000000;"> pymysql.connect(
  9. host</span>=<span style="color: #800000;">"</span><span style="color: #800000;">10.68.3.88</span><span style="color: #800000;">"</span>, <span style="color: #008000;">#</span><span style="color: #008000;"> mysql服务ip地址,若服务在本机则用localhost</span>
  10. port=3306, <span style="color: #008000;">#</span><span style="color: #008000;"> mysql服务端口</span>
  11. user=<span style="color: #800000;">"</span><span style="color: #800000;">liyiming</span><span style="color: #800000;">"</span>, <span style="color: #008000;">#</span><span style="color: #008000;"> 访问mysql的用户名</span>
  12. password=<span style="color: #800000;">"</span><span style="color: #800000;">liyiming</span><span style="color: #800000;">"</span>, <span style="color: #008000;">#</span><span style="color: #008000;"> 访问mysql的密码</span>
  13. db=<span style="color: #800000;">"</span><span style="color: #800000;">zzb_pro</span><span style="color: #800000;">"</span>, <span style="color: #008000;">#</span><span style="color: #008000;"> 默认连接到的数据库</span>
  14. charset=<span style="color: #800000;">"</span><span style="color: #800000;">utf8</span><span style="color: #800000;">"</span> <span style="color: #008000;">#</span><span style="color: #008000;"> 连接字符集</span>
  15. <span style="color: #000000;"> )
  16. </span><span style="color: #0000ff;">try</span><span style="color: #000000;">:
  17. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 创建用于交互的cursor对象</span>
  18. cursor =<span style="color: #000000;"> conn.cursor()
  19. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 先插入10条测试数据</span>
  20. <span style="color: #008000;">#</span><span style="color: #008000;"> 构建插入数据的sql</span>
  21. sql = <span style="color: #800000;">"</span><span style="color: #800000;">INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)</span><span style="color: #800000;">"</span>
  22. <span style="color: #008000;">#</span><span style="color: #008000;"> 生成10条测试数据</span>
  23. sql_data =<span style="color: #000000;"> []
  24. </span><span style="color: #0000ff;">for</span> index <span style="color: #0000ff;">in</span> range(0, 10<span style="color: #000000;">):
  25. email </span>= <span style="color: #800000;">"</span><span style="color: #800000;">%.10f@126.com</span><span style="color: #800000;">"</span> %<span style="color: #000000;"> random.random()
  26. password </span>=<span style="color: #000000;"> random.random()
  27. sql_data.append((email, password))
  28. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 执行sql,进行批量插入数据</span>
  29. <span style="color: #000000;"> cursor.executemany(sql, sql_data)
  30. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 提交至数据库</span>
  31. <span style="color: #000000;"> conn.commit()
  32. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 查询5条数据</span>
  33. sql = <span style="color: #800000;">"</span><span style="color: #800000;">SELECT * FROM `users` LIMIT 5</span><span style="color: #800000;">"</span>
  34. <span style="color: #008000;">#</span><span style="color: #008000;"> 执行sql</span>
  35. <span style="color: #000000;"> cursor.execute(sql)
  36. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 取查询到的所有数据</span>
  37. all_data =<span style="color: #000000;"> cursor.fetchall()
  38. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 遍历打印出来</span>
  39. <span style="color: #0000ff;">print</span>(<span style="color: #800000;">"</span><span style="color: #800000;">取所有查询到的数据</span><span style="color: #800000;">"</span><span style="color: #000000;">)
  40. </span><span style="color: #0000ff;">for</span> data <span style="color: #0000ff;">in</span><span style="color: #000000;"> all_data:
  41. </span><span style="color: #0000ff;">print</span>(<span style="color: #800000;">"</span><span style="color: #800000;">id: %d email: %s password: %s</span><span style="color: #800000;">"</span> %<span style="color: #000000;">
  42. (data[0], data[</span>1], data[2<span style="color: #000000;">]))
  43. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 取1条数据</span>
  44. <span style="color: #008000;">#</span><span style="color: #008000;"> cursor.execute(sql)</span>
  45. one_data =<span style="color: #000000;"> cursor.fetchone()
  46. </span><span style="color: #0000ff;">print</span>(<span style="color: #800000;">"</span><span style="color: #800000;">\n取1条数据</span><span style="color: #800000;">"</span><span style="color: #000000;">)
  47. </span><span style="color: #0000ff;">print</span>(<span style="color: #800000;">"</span><span style="color: #800000;">id: %d email: %s password: %s</span><span style="color: #800000;">"</span> %<span style="color: #000000;">
  48. (one_data[0], one_data[</span>1], one_data[2<span style="color: #000000;">]))
  49. </span><span style="color: #0000ff;">finally</span><span style="color: #000000;">:
  50. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 最后把数据库连接关闭</span>
  51. conn.close()

 

小结

  • 对SQL要熟悉,才能更好的应用PyMySQL库

  • 其次要注意在构造sql时,最好构建成参数化方式,见示例

  • 多练习最重要

[Python3]PyMySQL库

标签:utf-8   提交   创建   python3   实现   code   发送   charset   退出   

人气教程排行