使用 Python 2.7 从 Excel 导入 MySQL 表 [英] Importing from Excel to MySQL Table Using Python 2.7

问题描述

我正在尝试将这个 Excel 表中的数据插入到 MySQL 表中:https://www.dropbox.com/s/w7m282386t08xk3/GA.xlsx?dl=0

I'm trying to insert into a MySQL table from data in this Excel sheet: https://www.dropbox.com/s/w7m282386t08xk3/GA.xlsx?dl=0

脚本应该从第 16 行的第二个工作表每日指标"开始.MySQL 表已经有名为日期、活动、用户和会话的字段.

The script should start from the second sheet "Daily Metrics" at row 16. The MySQL table already has the fields called date, campaign, users, and sessions.

使用 Python 2.7，我已经创建了 MySQL 连接并打开了工作表，但我不确定如何遍历这些行并插入到数据库中.

Using Python 2.7, I've already created the MySQL connection and opened the sheet, but I'm not sure how to loop over those rows and insert into the database.

import MySQLdb as db
from openpyxl import load_workbook

wb = load_workbook('GA.xlsx')
sheetranges = wb['Daily Metrics']
print(sheetranges['A16'].value)

conn = db.connect('serverhost','username','password','database')

cursor = conn.cursor()

cursor.execute('insert into test_table ...')

conn.close()

感谢您的帮助！

推荐答案

只有MySQL 和 Openpyxl，可以自己看教程解决.

Only basic knowledge of MySQL and Openpyxl is needed, you can solve it by reading tutorials on your own.

在执行脚本之前，您需要创建数据库和表.假设你已经做到了.

Before executing the script, you need to create database and table. Assuming you've done it.

import openpyxl
import MySQLdb

wb = openpyxl.load_workbook('/path/to/GA.xlsx')
ws = wb['Daily Metrics']

# map is a convenient way to construct a list. you can get a 2x2 tuple by slicing 
# openpyxl.worksheet.worksheet.Worksheet instance and last row of worksheet 
# from openpyxl.worksheet.worksheet.Worksheet.max_row
data = map(lambda x: {'date': x[0].value, 
                      'campaign': x[1].value, 
                      'users': x[2].value, 
                      'sessions': x[3].value}, 
           ws[16: ws.max_row])

# filter is another builtin function. Filter blank cells out if needed
data = filter(lambda x: None not in x.values(), data)

db = MySQLdb.connect('host', 'user', 'password', 'database')
cursor = db.cursor()
for row in data:
    # execute raw MySQL syntax by using execute function
    cursor.execute('insert into table (date, campaign, users, sessions)'
                   'values ("{date}", "{campaign}", {users}, {sessions});'
                   .format(**row))  # construct MySQL syntax through format function
db.commit()

这篇关于使用 Python 2.7 从 Excel 导入 MySQL 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！