pandasを使用してmysqlからexcelファイルに読み込み、書き込む
945 ワード
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pandas
import pandas as pd
import MySQLdb
import MySQLdb.cursors
import os
import datetime
# SQL
def retsql(sql):
db_user = MySQLdb.connect(‘IP‘,‘ ‘,‘ ‘,‘j ( )‘,cursorclass=MySQLdb.cursors.DictCursor( ))
cursor = db_user.cursor()
cursor.execute("SET NAMES utf8;"( utf-8, , utf-8))
cursor.execute(sql)
ret = cursor.fetchall()
db_user.close()
return ret
# xls
def retxls(ret,dt):
file_name = datetime.datetime.now().strftime("/path/to/store/%Y-%m-%d-%H:%M") + dt + ".sql.xlsx"
dret = pd.DataFrame.from_records(ret)
dret.to_excel(filename,"Sheet1",engine="openpyxl")###z openpyxl xls ,pip install openpyxls==1.8.6, pandas , 1.8.6
print "Ok!!! the file in",file_name
return filename