資料庫-python操作mysql(pymsql)

標籤：scroll   dict   預設   ast   pre   類型   安裝   rom   串連   

pymsql是Python中操作MySQL的模組，其使用方法和MySQLdb幾乎相同

一：安裝pymysql

pip3 install pymysql

二：使用pytmysql

 

# -*- 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()#擷取最新自增IDprint(cursor.lastrowid)cursor.execute("select * from student")# 擷取第一行資料print(cursor.fetchone())#擷取前n行資料print(cursor.fetchmany(3))# 擷取所有資料print(cursor.fetchall())conn.commit()cursor.close()conn.close()

49(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)