python3 mysql增删改查

示例表 test

 

插入操作

import pymysql                              #导入pymysql

db=pymysql.connect(host="localhost",
                   user="root",
                   password="root",
                   db="test",
                   port=3306
                   )                        #创建数据库对象

cur = db.cursor()

sql = "INSERT INTO `test` (`id`, `name`, `password`) VALUES ('3', 'test1', 'test1')"

try:
  cur.execute(sql)                     #执行sql
  db.commit()                          #提交

except Exception as e:
  db.rollback()                       #异常回滚

finally:
  db.close() 

查询操作

import pymysql                              #导入pymysql

db=pymysql.connect(host="localhost",
                   user="root",
                   password="root",
                   db="test",
                   port=3306
                   )                        #创建数据库对象

cur = db.cursor()                           #获取游标

sql = "select * from test"

try:
    cur.execute(sql)
    res = cur.fetchall()                    #查询多条数据，使用循环进行输出
    #res = cur.fetchone()                   #查询一条 直接输出
    print("id","name","password")
    #print(res)
    for row in res:
        id = row[0]
        name = row[1]
        password = row[2]
        print(id,name,password)

except Exception as e:
    raise e

finally:
    db.close()

 

更新操作

import pymysql                              #导入pymysql

db=pymysql.connect(host="localhost",
                   user="root",
                   password="root",
                   db="test",
                   port=3306
                   )                        #创建数据库对象

cur = db.cursor()   

sql = "UPDATE test set name = '%s',password = '%s' where id = %d"

try:
  cur.execute(sql% ("ddd","ddd",2))
  db.commit()

except Exception as e:
  db.rollback()

finally:
  db.close()

删除操作

import pymysql                              #导入pymysql

db=pymysql.connect(host="localhost",
                   user="root",
                   password="root",
                   db="test",
                   port=3306
                   )                        #创建数据库对象

cur = db.cursor()     

sql = "DELETE from test where id = %d"

try:
  cur.execute(sql% (2))
  db.commit()

except Exception as e:
  db.rollback()

finally:
  db.close()