如何用Python查询MySQL并自动更新带表头的Google Sheets（每日清空）

前提条件 #

• 已完成Google Cloud控制台配置，可正常访问Google Sheets
• 已成功连接MySQL数据库
• 从MySQL查询获取的样本数据：

['2023-01-18', '5231', 'ABC', 6164368.0, 234712.0, 108554.0]
['2023-01-17', '5231', 'ABC', 6434784.0, 231475.0, 94775.0]
['2023-01-16', '5232', 'ABC', 6463684.0, 232513.0, 100168.0]

需求 #

• a) 将查询数据更新至Google Sheets的Sheet1中
• b) 为表格添加固定表头：Date、ID、Name、OrderValue、PurchaseValue、Clicks
• c) 每次执行代码前清空Sheet1所有内容，支持每日自动刷新数据

现有代码片段 #

cursor.execute(SELECT_QUERY)
result = cursor.fetchall()
df_columns = [x[0] for x in cursor.description]
i = 0
data = {i: list(r[0:]) for i, r in enumerate(result)}
for i in data:
    print(data[i])

完整实现代码 #

1. 安装依赖 #

先安装所需Python库：

pip install mysql-connector-python gspread pandas google-auth

2. 完整实现代码 #

import mysql.connector
import gspread
from google.oauth2.service_account import Credentials
import pandas as pd

# ---------------------- MySQL 连接与数据获取 ----------------------
# 替换为你的MySQL连接信息
MYSQL_CONFIG = {
    'host': '你的MySQL主机地址',
    'user': '用户名',
    'password': '密码',
    'database': '数据库名'
}

SELECT_QUERY = "你的查询SQL语句"

# 连接MySQL并获取数据
conn = mysql.connector.connect(**MYSQL_CONFIG)
cursor = conn.cursor()
cursor.execute(SELECT_QUERY)
result = cursor.fetchall()

# 将查询结果转为DataFrame，绑定自定义表头
custom_headers = ['Date', 'ID', 'Name', 'OrderValue', 'PurchaseValue', 'Clicks']
df = pd.DataFrame(result, columns=custom_headers)

# 关闭MySQL连接
cursor.close()
conn.close()

# ---------------------- Google Sheets 操作 ----------------------
# 替换为你的服务账号密钥文件路径（从Google Cloud控制台下载）
SERVICE_ACCOUNT_FILE = 'path/to/your/service-account-key.json'

# 定义权限范围
SCOPES = ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']

# 认证并连接Google Sheets
creds = Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
client = gspread.authorize(creds)

# 替换为你的Google Sheets文档ID（从文档URL中获取）
SPREADSHEET_ID = '你的Google Sheets文档ID'
sheet = client.open_by_key(SPREADSHEET_ID).worksheet('Sheet1')

# 清空Sheet1所有内容
sheet.clear()

# 写入表头和数据
sheet.append_row(custom_headers)
sheet.append_rows(df.values.tolist())

print("数据同步完成！")

关键说明 #

• MySQL数据处理：用Pandas将查询结果转为DataFrame，直接绑定自定义表头，简化数据格式处理
• 清空表格：sheet.clear()会彻底清除Sheet1的所有内容（含格式），确保每次写入的是全新数据
• Google Sheets权限：需确保服务账号已被添加为目标Google Sheets文档的编辑者
• 每日刷新：可将代码配置为定时任务（如Linux crontab、Windows任务计划程序），实现自动每日执行

内容的提问来源于stack exchange，提问作者King