連接數(shù)據(jù)庫(kù)查詢并輸出Excel
每周開(kāi)會(huì)前都需要整理數(shù)據(jù)菠净，可是小編并不想花費(fèi)時(shí)間去做復(fù)制粘貼的活。
正好呢公司數(shù)據(jù)庫(kù)里面開(kāi)會(huì)的數(shù)據(jù)都有彪杉，于是就用Python寫(xiě)了一個(gè)小程序毅往，自動(dòng)導(dǎo)出數(shù)據(jù)庫(kù)的數(shù)據(jù)。話不多說(shuō)派近，代碼如下：
import pymysql
import pandas as pd
class Mysql:
def __init__(self):
# 連接數(shù)據(jù)庫(kù)的參數(shù)
self.content = pymysql.Connect(
host='0.0.0.0', # mysql的主機(jī)ip
port=3306, # 端口
user='name', # 用戶名
passwd='password', # 數(shù)據(jù)庫(kù)密碼
db='database', # 數(shù)據(jù)庫(kù)名
charset='utf8', # 字符集
)
# 獲得一個(gè)連接對(duì)象
self.cursor = self.content.cursor()
def query(self):
sql = '''
SELECT
`name` AS '工程名稱',
qlfs AS '資質(zhì)要求',
xfl AS '下浮率',
bid_time AS '開(kāi)標(biāo)日期',
town AS '鎮(zhèn)區(qū)',
number AS '投標(biāo)人數(shù)',
p AS '去最高最低平均價(jià)(P)',
method AS '投標(biāo)方法',
bid_place AS '開(kāi)標(biāo)地點(diǎn)',
k AS 'K值',
control_price AS '招標(biāo)控制價(jià)',
p_xfl AS 'P值下浮率',
yc_xfl AS '預(yù)測(cè)下浮率',
type_tender AS '類型'
FROM
bid_project
WHERE
bid_time BETWEEN '20210326'
AND '20210411'
ORDER BY
bid_time;
'''
# execute(self, query, args):執(zhí)行單條sql語(yǔ)句,接收的參數(shù)為sql語(yǔ)句本身和使用的參數(shù)列表,返回值為受影響的行數(shù)
self.cursor.execute(sql)
# fetchall(self):接收全部的返回結(jié)果行.返回一個(gè)二維tuple
# 正好將這個(gè)二維tuple轉(zhuǎn)換為dataframe類型的數(shù)據(jù)
df = pd.DataFrame(self.cursor.fetchall(),columns=['工程名稱','資質(zhì)要求','下浮率','開(kāi)標(biāo)日期','鎮(zhèn)區(qū)','投標(biāo)人數(shù)','去最高最低平均價(jià)(P)','投標(biāo)方法','開(kāi)標(biāo)地點(diǎn)','K值','招標(biāo)控制價(jià)','P值下浮率','預(yù)測(cè)下浮率','類型'])
# 將數(shù)據(jù)輸出到Excel中攀唯，調(diào)格式就不贅述了
df.to_excel('data.xlsx',index=None)
# 中斷連接
def end(self):
self.cursor.close()
self.content.close()
print('over')
if __name__ == '__main__':
mysql = Mysql()
mysql.query()
mysql.end()
