python|游标的简单使用及案例【笔记二】 python|django|mysql

import pymysql def get_connection(): # 连接数据库 connect = pymysql.Connect( host='ip', port=3306, user='username', passwd='password', db='databasename', charset='utf8' ) # 获取游标(指定获取的数据格式，这里设定返回dict格式) return connect, connect.cursor(cursor=pymysql.cursors.DictCursor) def select_all(sql, args=None): #查询全部 conn, cursor = get_connection() cursor.execute(sql, args) results = cursor.fetchall() cursor.close() conn.close() #返回查询结果 return results def select_one(sql, args): #查询单个 conn, cursor = get_connection() cursor.execute(sql, args) result = cursor.fetchone() cursor.close() conn.close() #返回查询结果 return result

execute(sql, args)参数说明:
1)sql:要执行的sql语句
2)args是传入sql语句的参数，args一般是list或tuple格式，如果只有一个参数可直接传入
这里举个在工作中的实际案例：

def select_info_from_table(conn, cursor, sql_statement, condition_parameter=[]): """ 从相关表中查询数据 """ result = [] error_info = '' try: if condition_parameter: cursor.execute(sql_statement, condition_parameter) else: cursor.execute(sql_statement) rs = cursor.fetchall() if rs: result = list(rs) except: #print cursor._last_executed error_info = str(sys.exc_info()) logging.exception('traceback error info') finally: return result, error_info# 这个sql语句可以忽略，主要搞懂参数的传递，主要传递的参数是branch_name，start_date，end_date，以这三个参数为条件传入 #到sql语句中进行查询，如果有bug_ids或者cq_ids参数在增加相应的条件 class Manifest(View): def post(self,request): data = https://www.it610.com/article/json.loads(request.body.decode()) branch_name = data.get('manifest','') start_date = data.get('starttime','') end_date = data.get('endtime','') bug_ids = data.get('bugid','') cq_ids = data.get('cqid','')sql = """ SELECT T3.path as repoFullPath, T6.name as repoName, T7.name as branch, T5.rev, T5.author, T5.author_mail, T5.committer, T5.committer_mail, DATE_FORMAT(T5.committed_timestamp,'%%Y-%%m-%%d %%H:%%i:%%S') as committed_timestamp, T10.GerritID, T5.summary, T5.message, DATE_FORMAT(T4.submitted_time,'%%Y-%%m-%%d %%H:%%i:%%S') as submitted_time, T10.GerritURL, T9.bug_id as bug_ids, T11.cus_bug_id as cq_ids FROM branch T1 JOIN branches T2 ON T1.branch_id = T2.id JOIN projects T3 ON T3.manifest_branch_id = T1.id and T3.reference_type = 'b' JOIN revision T4 ON T3.reference_id = T4.branch_id JOIN branches T7 ON T4.branch_id = T7.id JOIN revisions T5 ON T4.revision_id = T5.id JOIN repos T6 ON T5.repo_id = T6.id JOIN ervices T8 ON T6.service_id = T8.id LEFT OUTER JOIN cm_git_commit_new T10 ON T10.RevNo = T5.rev AND T10.BranchName = T7.`name` AND T10.RepoName = T6.`name` LEFT OUTER JOIN cm_revision_bugs T9 ON T9.reference_type = T3.reference_type AND T9.reference_id = T3.reference_id AND T9.revision_id = T5.id LEFT OUTER JOIN bugs_cusbugid T11 ON T11.bug_id = T9.bug_id WHERE T2.`name` = %s AND T4.submitted_time >= %s AND T4.submitted_time < %s """ parameter_list = [branch_name, start_date, end_date] #这里要注意的是前端传的是以逗号分隔的多个参数，如果是单个参数直接传入就好 if bug_ids: sql += ' AND T9.bug_id in %s ' parameter_list.append(tuple(bug_ids.split(','))) if cq_ids: sql += ' AND T11.cus_bug_id in %s ' parameter_list.append(tuple(cq_ids.split(','))) sql += """ GROUP BY T5.rev ORDER BY T4.submit_timestamp_micros DESC; """ conn, cursor = get_connection()all_res, error_info = select_info_from_table(conn, cursor, sql, parameter_list)#由于这里获取的数据是[('zhangsan',20,'男'),('lisi','20','男')] 这种类型的不便于操作 title = [title[0] for title in cursor.description] data = https://www.it610.com/article/[] for item in all_res: data.append(dict(list(zip(title, item)))) # 转换成 [('name':'zhangsan','age':20,'sex':'男'),('name':'lisi','age':'20','sex':'男')]

注意点:
方式一：

bug_ids = “1234” sql = “select id,bug_ids from table where id = '%s'” % bug_ids cur.execute(sql)#这种方式会存在sql注入的风险

方式二：

bug_ids = “1234” sql = “select bug_ids,namefrom test_table where bug_ids= %sbug_ids and name = %s" cur.execute(sql)#用python内置的方法可以对sql语句中传入的参数进行校验，在一定程度上屏蔽掉sql注入，增加了sql的安全性

补充:
python sys.exc_info()方法，获取异常信息
在实际调试程序的过程中，有时只获得异常的类型是远远不够的，还需要借助更详细的异常信息才能解决问题。
模块 sys 中，有两个方法可以返回异常的全部信息，分别是 exc_info() 和 last_traceback()，这两个函数有相同的功能和用法
【python|游标的简单使用及案例【笔记二】】exc_info() 方法会将当前的异常信息以元组的形式返回，该元组中包含 3 个元素，分别为 type、value 和 traceback，它们的含义分别是：
1)type：异常类型的名称，它是 BaseException 的子类
2)value：捕获到的异常实例。
3)traceback：是一个 traceback 对象。