当前位置:Gxlcms > 数据库问题 > python 之操作mysql 数据库实例

python 之操作mysql 数据库实例

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


4. 获取结果
5. 关闭连接、关闭游标

一、默认获取的结果是元祖

  1. <span style="color: #008080"> 1</span> conn = pymysql.connect(host=<span style="color: #800000">‘</span><span style="color: #800000">localhost</span><span style="color: #800000">‘</span>,user=<span style="color: #800000">‘</span><span style="color: #800000">root</span><span style="color: #800000">‘</span>,passwd=<span style="color: #800000">‘</span><span style="color: #800000">123456</span><span style="color: #800000">‘</span>,port=3306,db=<span style="color: #800000">‘</span><span style="color: #800000">sakila</span><span style="color: #800000">‘</span>,charset=<span style="color: #800000">‘</span><span style="color: #800000">utf8</span><span style="color: #800000">‘</span><span style="color: #000000">)
  2. </span><span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000">charset 必须写utf8, 不能写utf-8; port 要写int 类型,不能加上引号</span>
  3. <span style="color: #008080"> 3</span> cur = conn.cursor() <span style="color: #008000">#</span><span style="color: #008000"> 建立游标,游标认为你是仓库管理员</span>
  4. <span style="color: #008080"> 4</span> cur.execute(<span style="color: #800000">‘</span><span style="color: #800000">select * from user limit 10;</span><span style="color: #800000">‘</span><span style="color: #000000">)
  5. </span><span style="color: #008080"> 5</span> res = cur.fetchone() <span style="color: #008000">#</span><span style="color: #008000"> 只获取一条结果,它的结果是一个一维元祖,第一次获取到第一条</span>
  6. <span style="color: #008080"> 6</span> <span style="color: #008000">#</span><span style="color: #008000">#print(‘fetchone‘,cur.fetchone()) # 第二次获取到的是第二行;</span>
  7. <span style="color: #008080"> 7</span> <span style="color: #0000ff">print</span><span style="color: #000000">(res)
  8. </span><span style="color: #008080"> 8</span> <span style="color: #0000ff">print</span>(<span style="color: #800000">‘</span><span style="color: #800000">fetchall</span><span style="color: #800000">‘</span>,cur.fetchall())<span style="color: #008000">#</span><span style="color: #008000"> 获取第二条开始的所有数据,获取sql语句执行的结果, 是个二维元祖,它把结果放到一个元祖里,每一条数据也是一个元祖</span>
  9. <span style="color: #008080"> 9</span> <span style="color: #0000ff">print</span><span style="color: #000000">(res)
  10. </span><span style="color: #008080">10</span> <span style="color: #0000ff">print</span>(res[0][1<span style="color: #000000">])
  11. </span><span style="color: #008080">11</span> <span style="color: #0000ff">print</span>(<span style="color: #800000">‘</span><span style="color: #800000">fetchone</span><span style="color: #800000">‘</span>,cur.fetchone()) <span style="color: #008000">#</span><span style="color: #008000"> #被获取完了,所以获取到的是None</span>
  12. <span style="color: #008080">12</span> <span style="color: #008000">#</span><span style="color: #008000"> 如果确定只有一条数据的用fetchone, 超过一条数据的那就用fetchall</span>

二、将获取的结果转字典,方便使用

  1. <span style="color: #008080">1</span> conn = pymysql.connect(host=<span style="color: #800000">‘</span><span style="color: #800000">localhost</span><span style="color: #800000">‘</span>,user=<span style="color: #800000">‘</span><span style="color: #800000">root</span><span style="color: #800000">‘</span>,passwd=<span style="color: #800000">‘</span><span style="color: #800000">123456</span><span style="color: #800000">‘</span>,port=3306,db=<span style="color: #800000">‘</span><span style="color: #800000">sakila</span><span style="color: #800000">‘</span>,charset=<span style="color: #800000">‘</span><span style="color: #800000">utf8</span><span style="color: #800000">‘</span><span style="color: #000000">)
  2. </span><span style="color: #008080">2</span> cur = conn.cursor(cursor=pymysql.cursors.DictCursor) <span style="color: #008000">#</span><span style="color: #008000"> 将元祖转为字典</span>
  3. <span style="color: #008080">3</span> sql = <span style="color: #800000">"</span><span style="color: #800000">select * from user where name=‘aa‘</span><span style="color: #800000">"</span>
  4. <span style="color: #008080">4</span> <span style="color: #000000">cur.execute(sql)
  5. </span><span style="color: #008080">5</span> <span style="color: #0000ff">print</span>(cur.fetchone()) <span style="color: #008000">#</span><span style="color: #008000">获取字典</span>
  6. <span style="color: #008080">6</span> res =<span style="color: #000000"> cur.fetchall()
  7. </span><span style="color: #008080">7</span> <span style="color: #0000ff">print</span>(res) <span style="color: #008000">#</span><span style="color: #008000">list 里存字典</span>
  8. <span style="color: #008080">8</span> cur.close()<span style="color: #008000">#</span><span style="color: #008000">关闭游标</span>
  9. <span style="color: #008080">9</span> conn.close()<span style="color: #008000">#</span><span style="color: #008000">关闭连接</span>

在python 中会经常用到mysql,因此可以写了一个函数用于mysql的操作,要使用时调用以下即可。

  1. <span style="color: #008080"> 1</span> <span style="color: #0000ff">import</span><span style="color: #000000"> pymysql
  2. </span><span style="color: #008080"> 2</span> <span style="color: #0000ff">def</span> op_mysql(host,user,password,db,sql,port=3306,charset=<span style="color: #800000">‘</span><span style="color: #800000">utf8</span><span style="color: #800000">‘</span><span style="color: #000000">):
  3. </span><span style="color: #008080"> 3</span> conn = pymysql.connect(host=host,user=<span style="color: #000000">user,
  4. </span><span style="color: #008080"> 4</span> password=<span style="color: #000000">password,
  5. </span><span style="color: #008080"> 5</span> db=<span style="color: #000000">db,
  6. </span><span style="color: #008080"> 6</span> port=<span style="color: #000000">port,
  7. </span><span style="color: #008080"> 7</span> charset=<span style="color: #000000">charset)
  8. </span><span style="color: #008080"> 8</span> cur = conn.cursor(cursor=<span style="color: #000000">pymysql.cursors.DictCursor)
  9. </span><span style="color: #008080"> 9</span> <span style="color: #000000"> cur.execute(sql)
  10. </span><span style="color: #008080">10</span> <span style="color: #008000">#</span><span style="color: #008000"> select, update, delete, insert 长度都是6位</span>
  11. <span style="color: #008080">11</span> <span style="color: #008000">#</span><span style="color: #008000"> SELECT</span>
  12. <span style="color: #008080">12</span> sql_start = sql[:6].upper() <span style="color: #008000">#</span><span style="color: #008000"> sql 的前6位字符串,判断它是什么类型的sql语句</span>
  13. <span style="color: #008080">13</span> <span style="color: #0000ff">if</span> sql_start == <span style="color: #800000">‘</span><span style="color: #800000">SELECT</span><span style="color: #800000">‘</span><span style="color: #000000">:
  14. </span><span style="color: #008080">14</span> res =<span style="color: #000000"> cur.fetchall()
  15. </span><span style="color: #008080">15</span> <span style="color: #0000ff">else</span><span style="color: #000000">:
  16. </span><span style="color: #008080">16</span> <span style="color: #000000"> conn.commit()
  17. </span><span style="color: #008080">17</span> res = <span style="color: #800000">‘</span><span style="color: #800000">ok</span><span style="color: #800000">‘</span>
  18. <span style="color: #008080">18</span> <span style="color: #000000"> cur.close()
  19. </span><span style="color: #008080">19</span> <span style="color: #000000"> conn.close()
  20. </span><span style="color: #008080">20</span> <span style="color: #0000ff">return</span><span style="color: #000000"> res
  21. </span><span style="color: #008080">21</span> sql = <span style="color: #800000">‘</span><span style="color: #800000">select * from user limit 5;</span><span style="color: #800000">‘</span>
  22. <span style="color: #008080">22</span> <span style="color: #000000">op_mysql(
  23. </span><span style="color: #008080">23</span> host=<span style="color: #800000">‘</span><span style="color: #800000">localhost</span><span style="color: #800000">‘</span><span style="color: #000000">,
  24. </span><span style="color: #008080">24</span> user=<span style="color: #800000">‘</span><span style="color: #800000">root</span><span style="color: #800000">‘</span><span style="color: #000000">,
  25. </span><span style="color: #008080">25</span> password=<span style="color: #800000">‘</span><span style="color: #800000">123456</span><span style="color: #800000">‘</span><span style="color: #000000">,
  26. </span><span style="color: #008080">26</span> port=3306<span style="color: #000000">,
  27. </span><span style="color: #008080">27</span> db=<span style="color: #800000">‘</span><span style="color: #800000">sakila</span><span style="color: #800000">‘</span><span style="color: #000000">,
  28. </span><span style="color: #008080">28</span> charset=<span style="color: #800000">‘</span><span style="color: #800000">utf8</span><span style="color: #800000">‘</span><span style="color: #000000">,
  29. </span><span style="color: #008080">29</span> sql=sql)

 

 

python 之操作mysql 数据库实例

标签:str   执行sql   连接   div   操作   sele   需要   第一条   exec   

人气教程排行