时间:2021-07-01 10:21:17 帮助过:8人阅读
一、默认获取的结果是元祖
- <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">)
- </span><span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000">charset 必须写utf8, 不能写utf-8; port 要写int 类型,不能加上引号</span>
- <span style="color: #008080"> 3</span> cur = conn.cursor() <span style="color: #008000">#</span><span style="color: #008000"> 建立游标,游标认为你是仓库管理员</span>
- <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">)
- </span><span style="color: #008080"> 5</span> res = cur.fetchone() <span style="color: #008000">#</span><span style="color: #008000"> 只获取一条结果,它的结果是一个一维元祖,第一次获取到第一条</span>
- <span style="color: #008080"> 6</span> <span style="color: #008000">#</span><span style="color: #008000">#print(‘fetchone‘,cur.fetchone()) # 第二次获取到的是第二行;</span>
- <span style="color: #008080"> 7</span> <span style="color: #0000ff">print</span><span style="color: #000000">(res)
- </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>
- <span style="color: #008080"> 9</span> <span style="color: #0000ff">print</span><span style="color: #000000">(res)
- </span><span style="color: #008080">10</span> <span style="color: #0000ff">print</span>(res[0][1<span style="color: #000000">])
- </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>
- <span style="color: #008080">12</span> <span style="color: #008000">#</span><span style="color: #008000"> 如果确定只有一条数据的用fetchone, 超过一条数据的那就用fetchall</span>
二、将获取的结果转字典,方便使用
- <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">)
- </span><span style="color: #008080">2</span> cur = conn.cursor(cursor=pymysql.cursors.DictCursor) <span style="color: #008000">#</span><span style="color: #008000"> 将元祖转为字典</span>
- <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>
- <span style="color: #008080">4</span> <span style="color: #000000">cur.execute(sql)
- </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>
- <span style="color: #008080">6</span> res =<span style="color: #000000"> cur.fetchall()
- </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>
- <span style="color: #008080">8</span> cur.close()<span style="color: #008000">#</span><span style="color: #008000">关闭游标</span>
- <span style="color: #008080">9</span> conn.close()<span style="color: #008000">#</span><span style="color: #008000">关闭连接</span>
在python 中会经常用到mysql,因此可以写了一个函数用于mysql的操作,要使用时调用以下即可。
- <span style="color: #008080"> 1</span> <span style="color: #0000ff">import</span><span style="color: #000000"> pymysql
- </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">):
- </span><span style="color: #008080"> 3</span> conn = pymysql.connect(host=host,user=<span style="color: #000000">user,
- </span><span style="color: #008080"> 4</span> password=<span style="color: #000000">password,
- </span><span style="color: #008080"> 5</span> db=<span style="color: #000000">db,
- </span><span style="color: #008080"> 6</span> port=<span style="color: #000000">port,
- </span><span style="color: #008080"> 7</span> charset=<span style="color: #000000">charset)
- </span><span style="color: #008080"> 8</span> cur = conn.cursor(cursor=<span style="color: #000000">pymysql.cursors.DictCursor)
- </span><span style="color: #008080"> 9</span> <span style="color: #000000"> cur.execute(sql)
- </span><span style="color: #008080">10</span> <span style="color: #008000">#</span><span style="color: #008000"> select, update, delete, insert 长度都是6位</span>
- <span style="color: #008080">11</span> <span style="color: #008000">#</span><span style="color: #008000"> SELECT</span>
- <span style="color: #008080">12</span> sql_start = sql[:6].upper() <span style="color: #008000">#</span><span style="color: #008000"> sql 的前6位字符串,判断它是什么类型的sql语句</span>
- <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">:
- </span><span style="color: #008080">14</span> res =<span style="color: #000000"> cur.fetchall()
- </span><span style="color: #008080">15</span> <span style="color: #0000ff">else</span><span style="color: #000000">:
- </span><span style="color: #008080">16</span> <span style="color: #000000"> conn.commit()
- </span><span style="color: #008080">17</span> res = <span style="color: #800000">‘</span><span style="color: #800000">ok</span><span style="color: #800000">‘</span>
- <span style="color: #008080">18</span> <span style="color: #000000"> cur.close()
- </span><span style="color: #008080">19</span> <span style="color: #000000"> conn.close()
- </span><span style="color: #008080">20</span> <span style="color: #0000ff">return</span><span style="color: #000000"> res
- </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>
- <span style="color: #008080">22</span> <span style="color: #000000">op_mysql(
- </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">,
- </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">,
- </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">,
- </span><span style="color: #008080">26</span> port=3306<span style="color: #000000">,
- </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">,
- </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">,
- </span><span style="color: #008080">29</span> sql=sql)
python 之操作mysql 数据库实例
标签:str 执行sql 连接 div 操作 sele 需要 第一条 exec