1. #coding=utf-8


2. import sae.const


3. import MySQLdb


4.  


5. #获取数据库连接


6. def get_conn():


7.   conn=MySQLdb.connect(host=sae.const.MYSQL_HOST,user=sae.const.MYSQL_USER,passwd=sae.const.MYSQL_PASS,db=sae.const.MYSQL_DB,port=int(sae.const.MYSQL_PORT),charset='utf8')


8.   return conn


9.  


10. #执行SQL查询，结果以list(dict...)的形式返回


11. def query_for_list(query,*param):


12.   try:


13.     conn=get_conn()


14.     try:


15.       cur=conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)


16.       cur.execute(query, param)


17.       result = cur.fetchall()


18.       return result


19.     finally:


20.       cur.close()


21.       conn.close()


22.   except MySQLdb.Error as e:


23.     print( "Mysql Error Occured! %s" % (e))


24.     return None


25.  


26. #获取单行型查询结果


27. def query_for_one(query,*param):


28.   try:


29.     conn=get_conn()


30.     try:


31.       cur=conn.cursor()


32.       cur.execute(query, param)


33.       result = cur.fetchone()


34.       return result


35.     finally:


36.       cur.close()


37.       conn.close()


38.   except MySQLdb.Error as e:


39.     print( "Mysql Error Occured! %s" % (e))


40.     return None


41.  


42. #执行批量查询


43. def execute_many(query,param_list):


44.   conn=get_conn()


45.   try:


46.     cur=conn.cursor()


47.     res = cur.executemany(query, param_list)


48.     conn.commit()


49.     return res


50.   except Exception as e:


51.     conn.rollback()


52.     raise e


53.   finally:


54.     cur.close()


55.     conn.close()


56.   return -1


57.  


58. #执行SQL更新操作（如update,insert,delete等）


59. def execute_sql(query,*param):


60.   conn=get_conn()


61.   try:


62.     cur=conn.cursor()


63.     cnt = cur.execute(query, param)


64.     conn.commit()


65.     return cnt


66.   except Exception as e:


67.     conn.rollback()


68.     raise e


69.   finally:


70.     cur.close()


71.     conn.close()


72.  


73. #执行insert，同时返回新增数据的ID，适合使用auto_increment字段的数据表


74. def execute_insert_get_id(query,*param):


75.   try:


76.     conn=get_conn()


77.     try:


78.       cur=conn.cursor()


79.       cur.execute(query, param)


80.       insert_id = conn.insert_id()


81.       conn.commit()


82.       return insert_id;


83.     except Exception as e:


84.       conn.rollback()


85.       raise e


86.     finally:


87.       cur.close()


88.       conn.close()


89.   except MySQLdb.Error as e:


90.     print( "Mysql Error Occured! %s" % (e))


91.     return -1


92.  


93. #以dict的形式执行数据插入操作,table_name为表名，dict为{字段:值}字典


94. def insert_by_dict(table_name,dict):


95.   keys = ''


96.   values = list()


97.   perc_sign = ''


98.   cnt = 0


99.   for key in dict:


100.     if cnt > 0:


101.       perc_sign += ','


102.       keys += ','


103.     cnt += 1


104.     perc_sign += '%s'


105.     keys += key


106.     values.append(dict[key])


107.   sql = 'insert into ' + table_name + '(' + keys + ') values(' + perc_sign + ')'


108.   return execute_sql(sql,*values)

本文链接：http://bookshadow.com/weblog/2014/02/05/sae-python-dbhandler/
请尊重作者的劳动成果，转载请注明出处！书影博客保留对文章的所有权利。

如果您喜欢这篇博文，欢迎您捐赠书影博客： ，查看支付宝二维码