查询实例
- 安装pymysql 依赖
C:\Users\wu-chao>pip install pymysql
Collecting pymysql
Downloading PyMySQL-0.8.0-py2.py3-none-any.whl (83kB)
100% |████████████████████████████████| 92kB 150kB/s
Installing collected packages: pymysql
Successfully installed pymysql-0.8.0
C:\Users\wu-chao>
02 代码导入pymysql
# coding:utf-8
import pymysql.cursors;
03 连接mysql
connection=pymysql.connect(host="localhost",port=3306,user="root",password="root",db="test");
04 打开游标
# 通过cursor创建默认游标
cursor = connection.cursor();
# pymysql.cursor 默认返回的数据行是元祖类型，若要返回key-map，需在打开游标的时候设置数据行的数据类型为字典类型：
# 游标设置为字典类型
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor);
05 游标执行sql 语句
# sql字符串拼接查询
sql="select empno,ename,job from emp";
# 返回数据行数
count=cursor.execute(sql);
05(2) 参数化查询
sql="select empno,ename,job,deptno from emp where deptno=%s and job=%s";
count=cursor.execute(sql,("20","ANALYST"));
06(1) 提取数据：for 循环
print "一共提取到%d条数据"%count
datas=cursor.fetchall(); #得到一个二维元祖
for row in datas:
print row #得到元祖
一共提取到14条数据
(7369, 'SMITH', 'CLERK')
……
(7902, 'FORD', 'ANALYST')
(7934, 'MILLER', 'CLERK')
06(2) 提取数据：while 逐行（fetchone）提取：方法一
while True:
row=cursor.fetchone();
if row is None:
break
print(row)
一共提取到14条数据
(7369, 'SMITH', 'CLERK')
……
(7902, 'FORD', 'ANALYST')
(7934, 'MILLER', 'CLERK')
06(3) 提取数据：while 逐行（fetchone）提取：方法二
row=cursor.fetchone();
while row is not None:
if row!= None: print row;
row=cursor.fetchone();
- 关闭资源：cursor connection
cursor.close();
connection.close();
完整代码
# coding:utf-8
import pymysql.cursors;
connection=pymysql.connect(host="localhost",port=3306,user="root",password="root",db="test");
# 通过cursor创建默认游标
cursor = connection.cursor();
# pymysql.cursor 默认返回的数据行是元祖类型，若要返回key-map，需在打开游标的时候设置数据行的数据类型为字典类型：
# 游标设置为字典类型
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor);
# 游标设置为字典类型
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)
sql="select empno,ename,job,deptno from emp where deptno=%s and job=%s";
count=cursor.execute(sql,("20","ANALYST"));
print "一共提取到%d条数据"%count
# 提取数据：for 循环
# datas=cursor.fetchall();
# for row in datas:
# print row
# 提取数据：while 逐行（fetchone）提取：方法一
# while True:
# row=cursor.fetchone();
# if row is None:
# break
# print(row)
# 提取数据：while 逐行（fetchone）提取：方法二
row=cursor.fetchone();
while row is not None:
if row!= None: print row;
row=cursor.fetchone();
# 关闭资源：cursor connection
cursor.close();
connection.close();
