pymysql是python提供的mysql客户端
查数据
import pymysql
conn = pymysql.connect( #先建立连接
host = "127.0.0.1",
port = 3306,
user = "root",
database="test",
password="123",
charset="utf8"
)
cursor = conn.cursor() #创建游标
sql = "select * from dep;"
ret = cursor.execute(sql) #执行sql,ret是受影响行数
print(ret)
print(cursor.fetchall()) #以默认游标执行sql得到的是((),())形式的数据
修改取出数据格式和逐条取出
import pymysql
conn = pymysql.connect( #先建立连接
host = "127.0.0.1",
port = 3306,
user = "root",
database="test",
password="123",
charset="utf8"
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
#创建游标,将取出的数据以字典形式显示
sql = "select * from dep;"
ret = cursor.execute(sql)
print(ret)
print(cursor.fetchone())
print(cursor.fetchmany(3))
cursor.scroll(2,"absolute")#移动光标至第二条数据末尾 absolute代表绝对位置
print(cursor.fetchone())#会打印第3条数据
cursor.scroll(2,"relative")#将光标从当前位置往下移动两条
print(cursor.fetchone())#会打印第6条数据
增删改
注意增删改都需要做commit操作,mysql自带服务器不用提交是因为他自动提交,pymysql需要手动提交
import pymysql
conn = pymysql.connect( #先建立连接
host = "127.0.0.1",
port = 3306,
user = "root",
database="test",
password="123",
charset="utf8"
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
#创建游标,将取出的数据以字典形式显示
sql = "insert into emp(name,post,salary,pid) values('GAGAGA','tec',12000,2);"
ret = cursor.execute(sql)
print(ret)
conn.commit()
import pymysql
conn = pymysql.connect( #先建立连接
host = "127.0.0.1",
port = 3306,
user = "root",
database="test",
password="123",
charset="utf8"
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
#创建游标,将取出的数据以字典形式显示
sql = "delete from emp where id = 7;"
ret = cursor.execute(sql)
print(ret)
conn.commit()
import pymysql
conn = pymysql.connect( #先建立连接
host = "127.0.0.1",
port = 3306,
user = "root",
database="test",
password="123",
charset="utf8"
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
#创建游标,将取出的数据以字典形式显示
sql = "update emp set name = "GAGAga" where id = 6;"
ret = cursor.execute(sql)
print(ret)
conn.commit()