关于数据库与xlsx文件的操作

#将excel中的数据 存入mysql============================================================
# import pymysql
# import xlrd
# import xlwt
#
# def get_conn():
#     conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='???????', charset='utf8')
#     return conn

# def insert(cur, sql, args):
#     cur.execute(sql, args)
#
# def read_xlsx_to_mysql(filename):
#     excel = xlrd.open_workbook(filename)  # 打开xlsx文件,返回一个对象
#     sheet = excel.sheet_by_index(0)  # 获取第一个sheet表格
#     conn = get_conn()
#     cur = conn.cursor()
#     sql = 'insert into ??????????? values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
#     print(sheet.nrows)
#     for row in range(sheet.nrows):
#         print(row)
#         args = sheet.row_values(row)
#         print(args)
#         print(type(args))
#         if row == 0:
#             continue
#         if args[1] is None or args[1] == '':
#             continue
#         insert(cur, sql, args=args)
#     conn.commit()
#     cur.close()
#     conn.close()
#
# if __name__ == '__main__':
#     read_xlsx_to_mysql('1.xlsx')
# =======================================================================================================

# 将mysql 中的数据导出成excel ======================================================================
# from django.db import connections, connection, transaction
# import pymysql
# import xlwt
# def get_conn():
#     conn = pymysql.connect(host='127.0.0.1', port=3308, user='？？？？', passwd='？？？', db='？？', charset='utf8')
#     return conn

# def query_all(cur, sql, args):
#     cur.execute(sql, args)
#     return cur.fetchall()
#
# def read_mysql_to_xlsx(filename):
#     list_table_head = ['id', 'username', 'password', 'type','real_name','last_login','date_joined','status', 'phone',
#                        'platform_id','grade_id','sid','dept_id']
#     workbook = xlwt.Workbook()
#     sheet = workbook.add_sheet('data', cell_overwrite_ok=True)
#     for i in range(len(list_table_head)):
#         sheet.write(0, i, list_table_head[i])
#
#     conn = get_conn()
#     cur = conn.cursor()
#     sql = 'select * from auth_user'
#     results = query_all(cur, sql, None)
#     conn.commit()
#     cur.close()
#     conn.close()
#     row = 1
#     for result in results[0:100]:
#         col = 0
#         print(type(result))
#         print(result)
#         for item in result:
#             print(item)
#             sheet.write(row, col, item)
#             col += 1
#         row += 1
#     workbook.save(filename)
#
#
# if __name__ == '__main__':
#     read_mysql_to_xlsx('2.xls')

# 将两个文件合并成一个文件==========================================================
# import xlrd
# import time
# import xlwt
# import sys
# reload(sys)
# sys.setdefaultencoding('utf8')
# def merge(name1, name2, first, second):
#     data1 = xlrd.open_workbook(name1)
#     table1 = data1.sheets()[0]
#     row1 = table1.nrows
#     data2 = xlrd.open_workbook(name2)
#     table2 = data2.sheets()[0]
#     row2 = table2.nrows
#     start = time.time()
#     list_table_head = table1.row_values(0)
#     head2 = table2.row_values(0)
#     head2.pop(second-1)
#     list_table_head.extend(head2)
#     workbook = xlwt.Workbook(encoding='utf8')
#     sheet = workbook.add_sheet('data', cell_overwrite_ok=True)
#     for i in range(len(list_table_head)):
#         sheet.write(0, i, list_table_head[i])
#     row = 1
#     for i in xrange(1, row1):
#         col1 = table1.row_values(i)
#         for j in xrange(1, row2):
#             col2 = table2.row_values(j)
#             if col1[first-1] == col2[second-1]:
#                 col1.extend(col2[0:second-1])
#                 col1.extend(col2[second:])
#                 print col1
#                 ll = 0
#                 for item in col1:
#                     print(item)
#                     sheet.write(row, ll, item)
#                     ll += 1
#                 row += 1
#     workbook.save("result3.xls")
#     end = time.time()
#     t = end - start
#     print t
#
# if __name__ == '__main__':
#     # 要合并文件的名字， 要把第几列和第几列可并  列数从 1 开始
#     merge("test.xlsx","test2.xlsx", 5, 2)
# ===============================================================================================================

def get_conn_maidian():
    conn_maidian = pymysql.connect(host='xxxx', port=xxx, user='xxx', passwd='xxxx', db='xxx', charset='utf8')
    return conn_maidian


def query_all_test(sql, args):
    conn = get_conn_test()
    cur = conn.cursor()
    cur.execute(sql, args)
    r = cur.fetchone() #cur.fetchall()
    cur.close()
    conn.close()
    return r