[大数据]游标的简单使用及案例【笔记二】

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 = 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 = []
      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,name  from test_table where bug_ids= %s  bug_ids and name = %s"
cur.execute(sql)

#用python内置的方法可以对sql语句中传入的参数进行校验，在一定程度上屏蔽掉sql注入，增加了sql的安全性

补充:
python sys.exc_info()方法，获取异常信息
在实际调试程序的过程中，有时只获得异常的类型是远远不够的，还需要借助更详细的异常信息才能解决问题。
模块 sys 中，有两个方法可以返回异常的全部信息，分别是 exc_info() 和 last_traceback()，这两个函数有相同的功能和用法

exc_info() 方法会将当前的异常信息以元组的形式返回，该元组中包含 3 个元素，分别为 type、value 和 traceback，它们的含义分别是：
1)type：异常类型的名称，它是 BaseException 的子类
2)value：捕获到的异常实例。
3)traceback：是一个 traceback 对象。