当前位置：Gxlcms > 数据库问题 > 数据库-python操作mysql(pymsql)

数据库-python操作mysql(pymsql)

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

-*- coding:utf-8 -*- __author__ = ‘shisanjun‘ import pymysql #创建连接 conn=pymysql.connect(host="192.168.0.121",port=3306,user="admin",password="admin",db="test2") #创建游标 cursor=conn.cursor() #执行sql,并还回影响的行数 effct_row=cursor.execute("update student set name=‘shi‘ where name=‘shisanjun‘") print(effct_row) #执行sql，并返回影响行数，多条记录执行 effct_row=cursor.executemany("insert into student (name,age,sex) value (%s, %s, %s)",[("tianshi",23,"F"),("xiatian",24,"F")]) conn.commit() #获取最新自增ID print(cursor.lastrowid) cursor.execute("select * from student") # 获取第一行数据 print(cursor.fetchone()) #获取前n行数据 print(cursor.fetchmany(3)) # 获取所有数据 print(cursor.fetchall()) conn.commit() cursor.close() conn.close() 
  1. 4
  2. 9<span style="color: #000000">
  3. (</span>1, <span style="color: #800000">‘</span><span style="color: #800000">shi</span><span style="color: #800000">‘</span>, 23, <span style="color: #800000">‘</span><span style="color: #800000">M</span><span style="color: #800000">‘</span><span style="color: #000000">)
  4. ((</span>2, <span style="color: #800000">‘</span><span style="color: #800000">shisanjun2</span><span style="color: #800000">‘</span>, 23, <span style="color: #800000">‘</span><span style="color: #800000">M</span><span style="color: #800000">‘</span>), (4, <span style="color: #800000">‘</span><span style="color: #800000">shisanjun3</span><span style="color: #800000">‘</span>, 24, <span style="color: #800000">‘</span><span style="color: #800000">F</span><span style="color: #800000">‘</span>), (5, <span style="color: #800000">‘</span><span style="color: #800000">shisanjun3</span><span style="color: #800000">‘</span>, 25, <span style="color: #800000">‘</span><span style="color: #800000">F</span><span style="color: #800000">‘</span><span style="color: #000000">))
  5. ((</span>6, <span style="color: #800000">‘</span><span style="color: #800000">shi</span><span style="color: #800000">‘</span>, 25, <span style="color: #800000">‘</span><span style="color: #800000">F</span><span style="color: #800000">‘</span>), (7, <span style="color: #800000">‘</span><span style="color: #800000">shi</span><span style="color: #800000">‘</span>, 26, <span style="color: #800000">‘</span><span style="color: #800000">F</span><span style="color: #800000">‘</span>), (8, <span style="color: #800000">‘</span><span style="color: #800000">shi</span><span style="color: #800000">‘</span>, 26, <span style="color: #800000">‘</span><span style="color: #800000">F</span><span style="color: #800000">‘</span>), (9, <span style="color: #800000">‘</span><span style="color: #800000">tianshi</span><span style="color: #800000">‘</span>, 23, <span style="color: #800000">‘</span><span style="color: #800000">F</span><span style="color: #800000">‘</span>), (10, <span style="color: #800000">‘</span><span style="color: #800000">xiatian</span><span style="color: #800000">‘</span>, 24, <span style="color: #800000">‘</span><span style="color: #800000">F</span><span style="color: #800000">‘</span>))

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

  • cursor.scroll(1,mode=‘relative‘)  # 相对当前位置移动
  • cursor.scroll(2,mode=‘absolute‘) # 相对绝对位置移动

三：fetch数据类型

关于默认获取的数据是元祖类型，如果想要或者字典类型的数据

  1. <span style="color: #008000">#</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
  2. <span style="color: #800080">__author__</span> = <span style="color: #800000">‘</span><span style="color: #800000">shisanjun</span><span style="color: #800000">‘</span>
  3. <span style="color: #0000ff">import</span><span style="color: #000000"> pymysql
  4. </span><span style="color: #008000">#</span><span style="color: #008000">创建连接</span>
  5. conn=pymysql.connect(host=<span style="color: #800000">"</span><span style="color: #800000">192.168.0.121</span><span style="color: #800000">"</span>,port=3306,user=<span style="color: #800000">"</span><span style="color: #800000">admin</span><span style="color: #800000">"</span>,password=<span style="color: #800000">"</span><span style="color: #800000">admin</span><span style="color: #800000">"</span>,db=<span style="color: #800000">"</span><span style="color: #800000">test2</span><span style="color: #800000">"</span><span style="color: #000000">)
  6. </span><span style="color: #008000">#</span><span style="color: #008000">创建游标</span>
  7. cursor=conn.cursor(cursor=<span style="color: #000000">pymysql.cursors.DictCursor)
  8. r</span>=cursor.execute(<span style="color: #800000">"</span><span style="color: #800000">select * from student</span><span style="color: #800000">"</span><span style="color: #000000">)
  9. result</span>=<span style="color: #000000">cursor.fetchall()
  10. </span><span style="color: #0000ff">print</span><span style="color: #000000">(r)
  11. </span><span style="color: #0000ff">print</span><span style="color: #000000">(result)
  12. conn.commit()
  13. cursor.close()
  14. conn.close()
  15. </span>9<span style="color: #000000">
  16. [{</span><span style="color: #800000">‘</span><span style="color: #800000">stu_id</span><span style="color: #800000">‘</span>: 1, <span style="color: #800000">‘</span><span style="color: #800000">name</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">shi</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">age</span><span style="color: #800000">‘</span>: 23, <span style="color: #800000">‘</span><span style="color: #800000">sex</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">M</span><span style="color: #800000">‘</span>}, {<span style="color: #800000">‘</span><span style="color: #800000">stu_id</span><span style="color: #800000">‘</span>: 2, <span style="color: #800000">‘</span><span style="color: #800000">name</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">shisanjun2</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">age</span><span style="color: #800000">‘</span>: 23, <span style="color: #800000">‘</span><span style="color: #800000">sex</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">M</span><span style="color: #800000">‘</span>}, {<span style="color: #800000">‘</span><span style="color: #800000">stu_id</span><span style="color: #800000">‘</span>: 4, <span style="color: #800000">‘</span><span style="color: #800000">name</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">shisanjun3</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">age</span><span style="color: #800000">‘</span>: 24, <span style="color: #800000">‘</span><span style="color: #800000">sex</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">F</span><span style="color: #800000">‘</span>}, {<span style="color: #800000">‘</span><span style="color: #800000">stu_id</span><span style="color: #800000">‘</span>: 5, <span style="color: #800000">‘</span><span style="color: #800000">name</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">shisanjun3</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">age</span><span style="color: #800000">‘</span>: 25, <span style="color: #800000">‘</span><span style="color: #800000">sex</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">F</span><span style="color: #800000">‘</span>}, {<span style="color: #800000">‘</span><span style="color: #800000">stu_id</span><span style="color: #800000">‘</span>: 6, <span style="color: #800000">‘</span><span style="color: #800000">name</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">shi</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">age</span><span style="color: #800000">‘</span>: 25, <span style="color: #800000">‘</span><span style="color: #800000">sex</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">F</span><span style="color: #800000">‘</span>}, {<span style="color: #800000">‘</span><span style="color: #800000">stu_id</span><span style="color: #800000">‘</span>: 7, <span style="color: #800000">‘</span><span style="color: #800000">name</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">shi</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">age</span><span style="color: #800000">‘</span>: 26, <span style="color: #800000">‘</span><span style="color: #800000">sex</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">F</span><span style="color: #800000">‘</span>}, {<span style="color: #800000">‘</span><span style="color: #800000">stu_id</span><span style="color: #800000">‘</span>: 8, <span style="color: #800000">‘</span><span style="color: #800000">name</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">shi</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">age</span><span style="color: #800000">‘</span>: 26, <span style="color: #800000">‘</span><span style="color: #800000">sex</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">F</span><span style="color: #800000">‘</span>}, {<span style="color: #800000">‘</span><span style="color: #800000">stu_id</span><span style="color: #800000">‘</span>: 9, <span style="color: #800000">‘</span><span style="color: #800000">name</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">tianshi</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">age</span><span style="color: #800000">‘</span>: 23, <span style="color: #800000">‘</span><span style="color: #800000">sex</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">F</span><span style="color: #800000">‘</span>}, {<span style="color: #800000">‘</span><span style="color: #800000">stu_id</span><span style="color: #800000">‘</span>: 10, <span style="color: #800000">‘</span><span style="color: #800000">name</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">xiatian</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">age</span><span style="color: #800000">‘</span>: 24, <span style="color: #800000">‘</span><span style="color: #800000">sex</span><span style="color: #800000">‘</span>: <span style="color: #800000">‘</span><span style="color: #800000">F</span><span style="color: #800000">‘</span>}]

 

数据库-python操作mysql(pymsql)

标签：scroll   dict   默认   ast   pre   类型   安装   rom   连接   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！