时间:2021-07-01 10:21:17 帮助过:26人阅读
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 退出