[Python3]PyMySQL库

Cursor对象常用API：

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

一个基本示例

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

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

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

<span style="color: #008000;">#</span><span style="color: #008000;"> -*- coding:utf-8 -*-</span>
<span style="color: #0000ff;">import</span><span style="color: #000000;"> pymysql
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> random
</span><span style="color: #800080;">__author__</span> = <span style="color: #800000;">‘</span><span style="color: #800000;">谷白</span><span style="color: #800000;">‘</span>
<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;">:    
    </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;">)    
    </span><span style="color: #008000;">#</span><span style="color: #008000;"> 创建一个连接实例</span>
    conn =<span style="color: #000000;"> pymysql.connect(
        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>
        port=3306,  <span style="color: #008000;">#</span><span style="color: #008000;"> mysql服务端口</span>
        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>
        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>
        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>
        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>
<span style="color: #000000;">    )
    </span><span style="color: #0000ff;">try</span><span style="color: #000000;">:        
        </span><span style="color: #008000;">#</span><span style="color: #008000;"> 创建用于交互的cursor对象</span>
        cursor =<span style="color: #000000;"> conn.cursor()        
        
        
        </span><span style="color: #008000;">#</span><span style="color: #008000;"> 先插入10条测试数据</span>
        <span style="color: #008000;">#</span><span style="color: #008000;"> 构建插入数据的sql</span>
        sql = <span style="color: #800000;">"</span><span style="color: #800000;">INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)</span><span style="color: #800000;">"</span>
        <span style="color: #008000;">#</span><span style="color: #008000;"> 生成10条测试数据</span>
        sql_data =<span style="color: #000000;"> []        
        </span><span style="color: #0000ff;">for</span> index <span style="color: #0000ff;">in</span> range(0, 10<span style="color: #000000;">):
            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()
            password </span>=<span style="color: #000000;"> random.random()
            sql_data.append((email, password))        
        
        </span><span style="color: #008000;">#</span><span style="color: #008000;"> 执行sql,进行批量插入数据</span>
<span style="color: #000000;">        cursor.executemany(sql, sql_data)        
        
        </span><span style="color: #008000;">#</span><span style="color: #008000;"> 提交至数据库</span>
<span style="color: #000000;">        conn.commit()        
        
        </span><span style="color: #008000;">#</span><span style="color: #008000;"> 查询5条数据</span>
        sql = <span style="color: #800000;">"</span><span style="color: #800000;">SELECT * FROM `users` LIMIT 5</span><span style="color: #800000;">"</span>
        <span style="color: #008000;">#</span><span style="color: #008000;"> 执行sql</span>
<span style="color: #000000;">        cursor.execute(sql)        
        
        </span><span style="color: #008000;">#</span><span style="color: #008000;"> 取查询到的所有数据</span>
        all_data =<span style="color: #000000;"> cursor.fetchall()        
        </span><span style="color: #008000;">#</span><span style="color: #008000;"> 遍历打印出来</span>
        <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;">)        
        </span><span style="color: #0000ff;">for</span> data <span style="color: #0000ff;">in</span><span style="color: #000000;"> all_data:            
            </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;"> 
                (data[0], data[</span>1], data[2<span style="color: #000000;">]))        
            
        </span><span style="color: #008000;">#</span><span style="color: #008000;"> 取1条数据</span>
        <span style="color: #008000;">#</span><span style="color: #008000;"> cursor.execute(sql)</span>
        one_data =<span style="color: #000000;"> cursor.fetchone()        
        </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;">)        
        </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;"> 
            (one_data[0], one_data[</span>1], one_data[2<span style="color: #000000;">]))
    </span><span style="color: #0000ff;">finally</span><span style="color: #000000;">:        
        </span><span style="color: #008000;">#</span><span style="color: #008000;"> 最后把数据库连接关闭</span>
        conn.close()

小结

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

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

• 多练习最重要

[Python3]PyMySQL库

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