python 之操作mysql 数据库实例

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

<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