pymysql

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