python - 将多个文件中的数据插入到多个表中

Question

我将数据存储在多个文件夹中的 CSV 文件中，我想在 Ubuntu 系统上使用 MySQL 将这些数据加载到多个 SQL 表中。每个表和文件都遵循此架构(文件没有 id 字段):

+ ------ + -------- + -------- + --------- + ---------- +
| SPO_Id | SPO_Name | SPO_Date | SPO_Price | SPO_Amount |
+ ------ + -------- + -------- + --------- + ---------- +

每个文件都包含一天的定价和销售数据。不幸的是，这些文件没有以它们的日期命名；它们存储在以日期命名的文件夹中。这是目录的示例图

      ------> 20170102 ------> prices.csv
     /
    /
Exmpl ------> 20170213 ------> prices.csv
    \
     \
      ------> 20170308 ------> prices.csv

这是我编写的查询，它从文件中提取数据并将其存储到表中:

use pricing ; # the database I want the tables in
drop table if exists SP_2017_01_02 ;

create table SP_2017_01_02 (
    SPO_Id int not null primary key auto_increment,
    SPO_Name varchar(32),
    SPO_Date date,
    SPO_Price float,
    SPO_Amount int
);

load data local infile '/Exmpl/20170102/prices.csv'
    into table SP_2017_01_02
    fields terminated by ','
    lines terminated by '\n'
    ignore 1 lines # First line contains field name information
    (SPO_Name, SPO_Date, SPO_Price, SPO_Amount) ;

select * from SP_2017_01_02 ;

show tables ;

此查询一次加载一个表时效果很好；但是，因为我有数百张表，所以我需要自动执行此过程。我环顾四周，发现了一些东西:

Here是一个类似我的问题，只有这个问题引用了SQL Server。答案给出了在没有任何实质内容的情况下该怎么做的建议。

This question也和我的很像，只是这是专门使用SSIS，我没有访问权限(而且这个问题没有得到回答)

This post建议使用控制文件引用，但这是针对 sql-loader 和 oracle 的。

Using python可能是要走的路，但我以前从未使用过它，而且我的问题似乎太复杂了，无法开始。

This one和 this one也使用 python，但它们只是用一个文件中的数据更新一个表。

我在 SQL Server 方面工作了很多，但我对 MySQL 还很陌生。非常感谢任何帮助!

更新

我曾尝试在 MySQL 中使用动态 SQL 来做到这一点。不幸的是，MySQL 需要使用存储过程来执行动态 SQL，但它不允许在存储过程中使用 load data 函数。作为@RandomSeed pointed out , this cannot be done只有 MySQL。我打算听取他的建议并尝试编写一个 shell/python 脚本来处理这个问题。

在我(或其他人)能够得出可靠的答案之前，我会保留这个问题。

Answer 1

所以一旦你有一个读取单个表的 sql 查询/函数/脚本，它看起来像你这样做(或者可以简单地在 python 中构建一个等效的表)，使用 python 遍历目录结构并获取文件名相当简单。如果您每次都可以通过某种方式向 infile '/Exmpl/20170102/prices.csv' 传递一个新的 csv 参数并从 python 中调用您的 sql 脚本，那么您应该很好。

我现在没有太多时间，但我想向您展示如何使用 Python 获取这些文件名字符串。

import os

prices_csvs = []
for root, dirs, files in os.walk(os.path.join('insert_path_here', 'Exmpl'):
    for f in files:
        if f == 'prices.csv':
            prices_csvs.append(os.path.join(root, f))
            break # optional, use if there only is one prices.csv in each subfolder

for csv_file in prices_csvs:
    # csv_file is a string of the path for each prices.csv
    # if you can insert it as the `infile` parameter and run the sql, you are done
    # admittedly, i don't know how to do this at the moment

os.walk 遍历每个子目录，将名称 root 指定为该文件夹的路径，将所有目录列为 dirs和文件作为 files 存储在那里。从那里可以简单地检查文件名是否与您要查找的内容匹配，如果匹配，则将其存储在列表中。遍历列表会生成包含 Exmpl 中每个 prices.csv 路径的字符串。

希望这对 python 如何提供帮助有所帮助