20190311 MySQL导出到csv文本,EXCEL表格

1. 导出到csv
   https://www.mysqltutorial.org/mysql-export-table-to-csv/

   SELECT
   orderNumber, orderDate, IFNULL(shippedDate, 'N/A')
   FROM  orders 
   INTO OUTFILE 'C:/tmp/orders2.csv'
   FIELDS ENCLOSED BY '"'
   TERMINATED BY ';'
   ESCAPED BY '"' LINES
   TERMINATED BY '\r\n';
   

   带表头的写法：
   https://stackoverflow.com/questions/5941809/include-headers-when-using-select-into-outfile，如下：

   SELECT 'ColName1', 'ColName2', 'ColName3'
   UNION ALL
   SELECT ColName1, ColName2, ColName3
   FROM YourTable
   INTO OUTFILE '/path/outfile'
   

2. 导出到EXCEL
   当使用MySQL直接查询并且用到INTO OUTFILE，时间格式问题解决办法(未验证)
   https://blog.csdn.net/flysky2015/article/details/83028648
   偷懒办法：使用Navicat查询后，复制结果和列名，粘贴到一个新建的表格，可以解决日期是数字的问题
   成熟办法，使用python:
   https://blog.csdn.net/tingzuhuitou/article/details/78749185

    import xlwt
    import MySQLdb
    conn = MySQLdb.connect(host='127.0.0.1', port=3306, user='root', passwd='youpassword', db=dbname', charset='utf8')
    cursor = conn.cursor()
    sql = 'your sql;'
    count = cursor.execute(sql)
    print('has %s record' % count)
   # 重置游标位置
   cursor.scroll(0, mode='absolute')
   # 搜取所有结果
   results = cursor.fetchall()
   # 测试代码，print results
   # 获取MYSQL里的数据字段
   fields = cursor.description
   # 将字段写入到EXCEL新表的第一行
   wbk = xlwt.Workbook()
   sheet = wbk.add_sheet('all', cell_overwrite_ok=True)
   for ifs in range(0, len(fields)):
      sheet.write(0, ifs, fields[ifs][0])
   ics = 1
   jcs = 0
   for ics in range(1, len(results) + 1):
      for jcs in range(0, len(fields)):
          sheet.write(ics, jcs, results[ics - 1][jcs])
   wbk.save('yourExcelFileName.xls')
   

   但是这种方法写入xlsx文件的日期格式是数字，比如原本的2018-12-30变成43434(模糊值，具体忘了)，怎么会这样呢？
   职业直觉，用这个整数除以365再减去2019（懒得用2019做被减数），果然个得到-1900，显然是一个特殊的年份；然后一路搜索，知道了MicroSoft家族的数据类起始都是1900年，比如Excel中的数值60，你把它转换成日期，就是1900/2/29，奇怪吧，1900年哪里是闰年哦。这里有一种：说是为了兼容 Lotus 123电子表格鼻祖中1900年是闰年这个bug 参见:https://social.msdn.microsoft.com/Forums/office/en-US/f1eef5fe-ef5e-4ab6-9d92-0998d3fa6e14/what-is-story-behind-december-30-1899-as-base-date https://stackoverflow.com/questions/3963617/why-is-1899-12-30-the-zero-date-in-access-sql-server-instead-of-12-31 Lotus 123为啥要把1900年处理成闰年，因为那时候存储宝贵，只要年份二进制的最后两位是零(可以被4整除)那它就是闰年，忽略了整百年要被400整除，你是不是还要问下为啥整百年要被400整除，我不告诉你。显然Lotus 123没活过2100年，不然要差2天了。
   原来python中，cursor取回来的数据是有约定格式的，输出日期那一列的值的类型，是datetime.date，应该是由MySQLdb对取回的值做类型转换；显然是Excel把日期类型转换成它自己可识别的天数了，所以在写日期的时候，要经过strftime('%Y/%m/%d')转换，写成Excel的日期格式字符串，即可。
   只需在代码中标识出哪些列是日期，再做转化即可；也可以对字段使用一个dict进行翻译

    sheet = wbk.add_sheet('all', cell_overwrite_ok=True)
   for ifs in range(0, len(fields)):
       try:
           #field_trans是字段翻译字典
           field_name = field_trans[fields[ifs][0]]
       except KeyError as e:
           print('not found translation:' + fields[ifs][0])
           field_name = fields[ifs][0]
       sheet.write(0, ifs, field_name)
   ics = 1
   jcs = 0
   for ics in range(1, len(results) + 1):
       for jcs in range(0, len(fields)):
           value = results[ics - 1][jcs]
           #date_field用来保存是日期的字段序号（从0开始）
           if jcs in date_field:
               value = value.strftime('%Y/%m/%d')
           sheet.write(ics, jcs, value)
   wbk.save('test6.xls')
   

3. 所以如果要把Excel中的天数转换成可读的日期应该怎么做？ 除了上面用到的xlwt模块，还有xlrd模块
   安装之，并使用xldate_as_tuple() 参见：https://stackoverflow.com/questions/1108428/how-do-i-read-a-date-in-excel-format-in-python

4. mysql 5.7.7之前的版本中，创建VIEW时不能在from中使用查询子句 参见 https://stackoverflow.com/questions/8428641/views-select-contains-a-subquery-in-the-from-clause

有点鸡肋啊！逐渐少花时间在这些事情上面。
我觉得这点是比较重要的：来自这个人，又是这个人的博客https://www.joelonsoftware.com/2006/06/16/my-first-billg-review/ （好吧他是Stack Overflow的CEO，又懂技术又能管理，真大佬啊），他提到，所有语言处理日期都有各自的epoch. 比如Excel的日期元年是1900年1月1日，C是1970年1月1日，所以就可以通过各自的参照时间点来做不同的时间格式转化。

In most modern programming environments, dates are stored as real numbers. The integer part of the number is the number of days since some agreed-upon date in the past, called the epoch. In Excel, today’s date, June 16, 2006, is stored as 38884, counting days where January 1st, 1900 is 1.

而后BillG问他：

“I don’t know, you guys,” Bill said, “Is anyone really looking into all the details of how to do this? Like, all those date and time functions. Excel has so many date and time functions. Is Basic going to have the same functions? Will they all work the same way?”

他在之前已经注意到：

I started working through the various date and time functions in Basic and the date and time functions in Excel, trying things out, when I noticed something strange in the Visual Basic documentation: Basic uses December 31, 1899 as the epoch instead of January 1, 1900, but for some reason, today’s date was the same in Excel as it was in Basic.

他还提到BillG在评审他的时候，大佬到底在评审什么？大佬在考察对于你所掌管的一切，是否尽在控制之中！Under Control

Later I had it explained to me. “Bill doesn’t really want to review your spec, he just wants to make sure you’ve got it under control. His standard M.O. is to ask harder and harder questions until you admit that you don’t know, and then he can yell at you for being unprepared. Nobody was really sure what happens if you answer the hardest question he can come up with because it’s never happened before.”