数据库-python操作mysql(pymsql)

-*- coding:utf-8 -*- __author__ = ‘shisanjun‘ import pymysql #创建连接 conn=pymysql.connect(host="192.168.0.121",port=3306,user="admin",password="admin",db="test2") #创建游标 cursor=conn.cursor() #执行sql,并还回影响的行数 effct_row=cursor.execute("update student set name=‘shi‘ where name=‘shisanjun‘") print(effct_row) #执行sql，并返回影响行数，多条记录执行 effct_row=cursor.executemany("insert into student (name,age,sex) value (%s, %s, %s)",[("tianshi",23,"F"),("xiatian",24,"F")]) conn.commit() #获取最新自增ID print(cursor.lastrowid) cursor.execute("select * from student") # 获取第一行数据 print(cursor.fetchone()) #获取前n行数据 print(cursor.fetchmany(3)) # 获取所有数据 print(cursor.fetchall()) conn.commit() cursor.close() conn.close()

4
9
(1, ‘shi‘, 23, ‘M‘)
((2, ‘shisanjun2‘, 23, ‘M‘), (4, ‘shisanjun3‘, 24, ‘F‘), (5, ‘shisanjun3‘, 25, ‘F‘))
((6, ‘shi‘, 25, ‘F‘), (7, ‘shi‘, 26, ‘F‘), (8, ‘shi‘, 26, ‘F‘), (9, ‘tianshi‘, 23, ‘F‘), (10, ‘xiatian‘, 24, ‘F‘))

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

• cursor.scroll(1,mode=‘relative‘)  # 相对当前位置移动
• cursor.scroll(2,mode=‘absolute‘) # 相对绝对位置移动

三：fetch数据类型

关于默认获取的数据是元祖类型，如果想要或者字典类型的数据

# -*- coding:utf-8 -*-
__author__ = ‘shisanjun‘
import pymysql

#创建连接
conn=pymysql.connect(host="192.168.0.121",port=3306,user="admin",password="admin",db="test2")

#创建游标
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)

r=cursor.execute("select * from student")
result=cursor.fetchall()
print(r)
print(result)

conn.commit()
cursor.close()
conn.close()


9
[{‘stu_id‘: 1, ‘name‘: ‘shi‘, ‘age‘: 23, ‘sex‘: ‘M‘}, {‘stu_id‘: 2, ‘name‘: ‘shisanjun2‘, ‘age‘: 23, ‘sex‘: ‘M‘}, {‘stu_id‘: 4, ‘name‘: ‘shisanjun3‘, ‘age‘: 24, ‘sex‘: ‘F‘}, {‘stu_id‘: 5, ‘name‘: ‘shisanjun3‘, ‘age‘: 25, ‘sex‘: ‘F‘}, {‘stu_id‘: 6, ‘name‘: ‘shi‘, ‘age‘: 25, ‘sex‘: ‘F‘}, {‘stu_id‘: 7, ‘name‘: ‘shi‘, ‘age‘: 26, ‘sex‘: ‘F‘}, {‘stu_id‘: 8, ‘name‘: ‘shi‘, ‘age‘: 26, ‘sex‘: ‘F‘}, {‘stu_id‘: 9, ‘name‘: ‘tianshi‘, ‘age‘: 23, ‘sex‘: ‘F‘}, {‘stu_id‘: 10, ‘name‘: ‘xiatian‘, ‘age‘: 24, ‘sex‘: ‘F‘}]

 

数据库-python操作mysql(pymsql)

标签：scroll   dict   默认   ast   pre   类型   安装   rom   连接