import pymysql
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='fuzj', passwd='123123', db='fuzj')
# 创建游标
cursor = conn.cursor()
cursor.execute("select * from user")
# 获取第一行数据
row_1 = cursor.fetchone()
print(row_1)
# 获取前n行数据
row_2 = cursor.fetchmany(3)
print(row_2)
# 获取所有数据
row_3 = cursor.fetchall()
print(row_3)
conn.commit() # 连接提交
cursor.close() # 关闭游标
conn.close() # 关闭连接
打印结果是元组:
(1, 'fuzj')
((2, 'jeck'), (3, '323'), (4, '123'))
((5, '456'), (6, '789'), (7, '0'), (8, '1'), (9, '2'))
2、 第二种连接方式: 输出结果为字典,键为字段,值为数据
2、
import pymysql
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='fuzj', passwd='123123', db='fuzj',charset="utf-8")
# 创建游标
#cursor = conn.cursor()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 这个打印的结果是列表里面包含的是字典
cursor.execute("select * from user")
row_1 = cursor.fetchone()
print(row_1)
# 获取前n行数据
row_2 = cursor.fetchmany(3)
print(row_2)
# 获取所有数据
row_3 = cursor.fetchall()
print(row_3)
conn.commit()
cursor.close()
conn.close()
打印结果是:
列表里面包含的是字典
{'id': 1, 'name': 'fuzj'}
[{'id': 2, 'name': 'jeck'}, {'id': 3, 'name': '323'}, {'id': 4, 'name': '123'}]
[{'id': 5, 'name': '456'}, {'id': 6, 'name': '789'}, {'id': 7, 'name': '0'}]
cursor.fetchone() # 获取1条数据
cursor.fetchmany(n) # 获取n条数据
curson.fetchall() # 获取所有数据
3、pymysql 的多条插入
import pymysql
#创建链接
connection = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='ccc',charset = "utf8")
#创建游标
cursor = connection.cursor()
data = [
("qidehan"),
("liushuai"),
("sunkeqiang")
]
#插入多条语句
cursor.executemany("insert into first(name) values(%s)", data)
connection.commit()
#关闭游标
cursor.close()
#关闭连接
connection.close()