# 2、写代码实现，把我的数据库里面的stu表中的数据，导出到excel中
    #编号 名字 性别

# 需求分析：
#     1、连接好数据库，写好SQL，查到数据 [[1,'name1','男']，[1,'name1','男']]
#     2、写excel,xlwt

import pymysql,xlwt
def my_db(sql):
   import pymysql
   coon = pymysql.connect(
      host='118.xx.xx.xx', user='xxx', passwd='123456',
      port=3306, db='xxx', charset='utf8')
   cur = coon.cursor() #建立游标
   cur.execute(sql)#执行sql
   if sql.strip()[:6].upper()=='SELECT':
      res =  cur.fetchall()
   else:
      coon.commit()
      res = 'ok'
   cur.close()
   coon.close()
   return res

def export(excel_name):
   sql = 'select * from stu;'
   data = my_db(sql)
   book = xlwt.Workbook()
   sheet = book.add_sheet('sheet1')
   title = ['编号','姓名','性别']
   col = 0 #列
   for t in title:
      sheet.write(0,col,t)  #写表头
      col+=1
   #[[1, 'xxxx', '男'], [1, 'xxxx', '男']]
   row = 1 #行数
   for d in data:  #控制行数
      # [1, '牛寒阳', '男']
      col = 0
      for line in d:#控制列的
         sheet.write(row,col,line)
         col+=1
      row+=1
   book.save(excel_name)

# export('stu.xls')