pymysql的使用（笔记）

朴灿烈づ我的快乐病毒、 2022-09-16 05:50 140阅读 0赞

## pymysql的使用（笔记） ##

首先，连接数据库：

import pymysql
    
    def conn_db():  # 连接数据库函数
        conn = pymysql.connect(
            host='localhost',
            user='root',
            passwd='root',
            db='swjtu_z16',
            charset='utf8mb4')
        cur = conn.cursor()
        return conn,cur
    
    def exe_update(cur, sql):  # 更新语句，可执行update,insert语句
        sta = cur.execute(sql)
        return sta
    
    # def exe_delete(cur, ids): # 删除语句，可批量删除
    # for eachID in ids.split(' '):
    # sta = cur.execute('delete from cms where id =%d' % int(eachID))
    # return sta
    
    def exe_query(cur, sql):  # 查询语句
        cur.execute(sql)
        return cur
    
    def exe_commit(cur):
        cur.connection.commit()  # 执行commit操作，插入语句才能生效
    
    def conn_close(conn, cur):  # 关闭所有连接
        cur.close()
        conn.close()

## 查询 ##

# 调用连接数据库的函数
    conn, cur = condb.conn_db()
    sql_query = "select * from bridge_preprocessing_params"
    params = defaultdict(str)
    
    try:
        sta = condb.exe_query(cur,sql_query)
        results = sta.fetchall()
        for row in results:
            name = row[0]
            param1 = row[1]
            param2 = row[2]
            param3 = row[3]
            params[name] = (param1,param2,param3)
        condb.exe_commit(cur)
    except Exception as e:
    	print(e)
    finally:
        condb.conn_close(conn, cur)

另一种写法（无封装）：（参考）

import pymysql  #导入 pymysql
     
    #打开数据库连接
    db= pymysql.connect(host="localhost",user="root",
     	password="123456",db="test",port=3307)
     
    # 使用cursor()方法获取操作游标
    cur = db.cursor()
     
    #1.查询操作
    # 编写sql 查询语句 user 对应我的表名
    sql = "select * from user"
    try:
    	cur.execute(sql) 	#执行sql语句
     
    	results = cur.fetchall()	#获取查询的所有记录
    	print("id","name","password")
    	#遍历结果
    	for row in results :
    		id = row[0]
    		name = row[1]
    		password = row[2]
    		print(id,name,password)
    except Exception as e:
    	raise e
    finally:
    	db.close()	#关闭连接

## 插入（参考） ##

import pymysql
    #2.插入操作
    db= pymysql.connect(host="localhost",user="root",
     	password="123456",db="test",port=3307)
     
    # 使用cursor()方法获取操作游标
    cur = db.cursor()
     
    sql_insert ="insert into user(id,username,password) values(4,'liu','1234')"
     
    try:
    	cur.execute(sql_insert)
    	#提交
    	db.commit()
    except Exception as e:
    	#错误回滚
    	db.rollback() 
    finally:
    	db.close()

## 更新 ##

def updateFunctions(denosing,outliers,standard):
        sql_update = "update bridge_preprocessing set denosing = '%s',outliers = '%s',standard = '%s' where id = '%s'"
        # 调用连接数据库的函数
        conn, cur = condb.conn_db()
        sta = condb.exe_update(cur, sql_update % (denosing, outliers, standard, "1"))
        condb.exe_commit(cur)
        condb.conn_close(conn, cur)
        return sta

另一种写法（无封装）：（参考）

import pymysql
    #3.更新操作
    db= pymysql.connect(host="localhost",user="root",
     	password="123456",db="test",port=3307)
     
    # 使用cursor()方法获取操作游标
    cur = db.cursor()
     
    sql_update ="update user set username = '%s' where id = %d"
     
    try:
    	cur.execute(sql_update % ("xiongda",3))  #像sql语句传递参数
    	#提交
    	db.commit()
    except Exception as e:
    	#错误回滚
    	db.rollback() 
    finally:
    	db.close()

## 删除 ##

import pymysql
    #4.删除操作
    db= pymysql.connect(host="localhost",user="root",
     	password="123456",db="test",port=3307)
     
    # 使用cursor()方法获取操作游标
    cur = db.cursor()
     
    sql_delete ="delete from user where id = %d"
     
    try:
    	cur.execute(sql_delete % (3))  #像sql语句传递参数
    	#提交
    	db.commit()
    except Exception as e:
    	#错误回滚
    	db.rollback() 
    finally:
    	db.close()

【参考】https://blog.csdn.net/qq\_37176126/article/details/72824106