pymysql
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) 返回字典格式
lastrowid 获取自增id
import pymysql conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="root", db="pymysql") cursor = conn.cursor() cursor.execute("insert into user(name,age ) values('vincen',22)") print(cursor.lastrowid) 执行结果: 2
查询操作:
import pymysql
#数据库连接 conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="root", db="mydb")
#使用cursor()方法创建一个游标对象,并配置获取数据返回的类型 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#执行sql语句 cursor.execute("select * from department")
#获取查询结果 print(cursor.fetchall()) cursor.close() conn.close()
更新操作:
import pymysql # 打开数据库连接 db = pymysql.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 更新语句 sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M') 不能使用SQL字符串拼接,否则会造成SQL注入 try: # 执行SQL语句 cursor.execute(sql) # 提交到数据库执行 db.commit() except: # 发生错误时回滚 db.rollback() # 关闭数据库连接 db.close()
删除操作
import pymysql # 打开数据库连接 db = pymysql.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 删除语句 sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20) try: # 执行SQL语句 cursor.execute(sql) # 提交修改 db.commit() except: # 发生错误时回滚 db.rollback() # 关闭连接 db.close()