python - Pandas:上传到 mysql 表

Question

我有以下数据框，称为ind_concat:

我正在尝试使用以下命令将其上传到 mysql 表:

ind_concat.to_sql('indicators_correspondances', engine, if_exists='replace', index=True, 
                  index_label='common_names')

上面的公式不起作用，因为索引是 blob 文本，并收到 mysql 错误 1170:没有 key 长度的 key 规范

如果我将索引转换为列并使用 index=False 上传，它确实有效:

concat_ind['index'] = ind_concat.index
ind_concat.to_sql('indicators_correspondances', engine, if_exists='replace', index=False)

但我不喜欢该列是最后一个列，并且由于多重索引而具有诸如 ('index', '') 之类的标题。

所以我宁愿使用dtype参数并给出索引文本的长度。我尝试过:

ind_concat.to_sql('indicators_correspondances', engine, if_exists='replace', index=True, 
                  index_label='common_names', dtype = {'common_names':'Text'})

我还尝试为 dtype 提供值 {'common_names':'Text(25)'} 或 {'common_names':'String(25)'} (我在页面上找到的: http://docs.sqlalchemy.org/en/latest/core/type_basics.html#generic-types )但它们也不起作用。

是否有人有使用 sqlalchemy 类型将具有长度的文本数据类型分配给 mysql 列的工作示例？

下面是重建数据框的数据:

dikt={('investingcom', 'original_name'): {'CPI Ex Food and Energy MoM': 'Core CPI (MoM)', 'PCE Core MoM': 'Core PCE Price Index (MoM)', 'Construction Spending MoM': nan, 'Personal Spending': 'Personal Spending (MoM)', 'Existing Home Sales MoM': 'Existing Home Sales (MoM)', 'ISM Non-Manufacturing PMI': 'ISM Non-Manufacturing PMI', 'Pending Home Sales MoM': 'Pending Home Sales (MoM)', 'FHFA House Price Index MoM': nan, 'Housing Starts': 'Housing Starts', 'S&P/CaseShiller 20-City Index NSA': 'S&P;/CS HPI Composite - 20 n.s.a. (YoY)', 'Employment Cost Index': 'Employment Cost Index (QoQ)', 'New Home Sales': 'New Home Sales', 'Business Inventories MoM': 'Business Inventories (MoM)', 'Initial Jobless Claims': 'Initial Jobless Claims', 'Empire Manufacturing': nan, 'Import Price Index MoM': 'Import Price Index (MoM)', 'ADP Employment Change': 'ADP Nonfarm Employment Change', 'Nonfarm Payrolls': 'Nonfarm Payrolls', 'Capacity Utilization': nan, 'Durable Goods Orders': 'Durable Goods Orders (MoM)', 'Markit Composite PMI': 'Markit Composite PMI', 'Factory Orders': 'Factory Orders (MoM)', 'Building Permits': 'Building Permits', 'Total Net TIC Flows': 'TIC Net Long-Term Transactions', 'Chicago PMI': 'Chicago PMI', 'FOMC Rate Decision': 'Fed Interest Rate Decision', 'Existing Home Sales': 'Existing Home Sales', 'GDP Price Index': 'GDP Price Index (QoQ)'}, ('ft', 'original_name'): {'CPI Ex Food and Energy MoM': 'CPI (ex Food & Energy) % m/m', 'PCE Core MoM': 'Core PCE Price Index % m/m', 'Construction Spending MoM': 'Construction Spending % m/m', 'Personal Spending': nan, 'Existing Home Sales MoM': nan, 'ISM Non-Manufacturing PMI': 'ISM Non-Manufacturing index', 'Pending Home Sales MoM': 'Pending Home Sales % m/m', 'FHFA House Price Index MoM': 'FHFA House Price Index % m/m', 'Housing Starts': nan, 'S&P/CaseShiller 20-City Index NSA': 'S&P Case-Shiller Home Price % y/y', 'Employment Cost Index': 'Employment cost index % q/q', 'New Home Sales': 'New Home Sales k', 'Business Inventories MoM': 'Business inventories % m/m', 'Initial Jobless Claims': 'Initial Claims k', 'Empire Manufacturing': 'Empire State Survey index', 'Import Price Index MoM': nan, 'ADP Employment Change': nan, 'Nonfarm Payrolls': 'Non-farm Payrolls k', 'Capacity Utilization': 'Capacity utilisation %', 'Durable Goods Orders': 'Durable goods orders % m/m', 'Markit Composite PMI': nan, 'Factory Orders': nan, 'Building Permits': 'Building Permits k', 'Total Net TIC Flows': nan, 'Chicago PMI': 'Chicago PMI index', 'FOMC Rate Decision': 'FOMC Rate Decision', 'Existing Home Sales': 'Existing home sales mn', 'GDP Price Index': nan}, ('investingcom', 'importance'): {'CPI Ex Food and Energy MoM': 3.0, 'PCE Core MoM': 2.0, 'Construction Spending MoM': nan, 'Personal Spending': 2.0, 'Existing Home Sales MoM': 2.0, 'ISM Non-Manufacturing PMI': 3.0, 'Pending Home Sales MoM': 3.0, 'FHFA House Price Index MoM': nan, 'Housing Starts': 2.0, 'S&P/CaseShiller 20-City Index NSA': 2.0, 'Employment Cost Index': 2.0, 'New Home Sales': 3.0, 'Business Inventories MoM': 2.0, 'Initial Jobless Claims': 2.0, 'Empire Manufacturing': nan, 'Import Price Index MoM': 2.0, 'ADP Employment Change': 3.0, 'Nonfarm Payrolls': 3.0, 'Capacity Utilization': nan, 'Durable Goods Orders': 2.0, 'Markit Composite PMI': 2.0, 'Factory Orders': 2.0, 'Building Permits': 3.0, 'Total Net TIC Flows': 2.0, 'Chicago PMI': 2.0, 'FOMC Rate Decision': 3.0, 'Existing Home Sales': 3.0, 'GDP Price Index': 2.0}, ('bloomberg', 'importance'): {'CPI Ex Food and Energy MoM': 76.0976, 'PCE Core MoM': 59.756100000000004, 'Construction Spending MoM': 79.674800000000005, 'Personal Spending': 85.365899999999996, 'Existing Home Sales MoM': 51.219499999999996, 'ISM Non-Manufacturing PMI': 76.422799999999995, 'Pending Home Sales MoM': 77.235799999999998, 'FHFA House Price Index MoM': 69.918700000000001, 'Housing Starts': 89.430899999999994, 'S&P/CaseShiller 20-City Index NSA': 52.845500000000001, 'Employment Cost Index': 74.796700000000001, 'New Home Sales': 90.243899999999996, 'Business Inventories MoM': nan, 'Initial Jobless Claims': 98.373999999999995, 'Empire Manufacturing': 82.113799999999998, 'Import Price Index MoM': 78.0488, 'ADP Employment Change': 82.9268, 'Nonfarm Payrolls': nan, 'Capacity Utilization': 62.032499999999999, 'Durable Goods Orders': 92.682900000000004, 'Markit Composite PMI': 70.0, 'Factory Orders': 86.991900000000001, 'Building Permits': 62.601599999999998, 'Total Net TIC Flows': 65.040700000000001, 'Chicago PMI': 81.300799999999995, 'FOMC Rate Decision': 97.561000000000007, 'Existing Home Sales': 87.804900000000004, 'GDP Price Index': 77.398399999999995}, ('bloomberg', 'original_name'): {'CPI Ex Food and Energy MoM': 'CPI Ex Food and Energy MoM', 'PCE Core MoM': 'PCE Core MoM', 'Construction Spending MoM': 'Construction Spending MoM', 'Personal Spending': 'Personal Spending', 'Existing Home Sales MoM': 'Existing Home Sales MoM', 'ISM Non-Manufacturing PMI': 'ISM Non-Manf. Composite', 'Pending Home Sales MoM': 'Pending Home Sales MoM', 'FHFA House Price Index MoM': 'FHFA House Price Index MoM', 'Housing Starts': 'Housing Starts', 'S&P/CaseShiller 20-City Index NSA': 'S&P/CaseShiller 20-City Index NSA', 'Employment Cost Index': 'Employment Cost Index', 'New Home Sales': 'New Home Sales', 'Business Inventories MoM': nan, 'Initial Jobless Claims': 'Initial Jobless Claims', 'Empire Manufacturing': 'Empire Manufacturing', 'Import Price Index MoM': 'Import Price Index MoM', 'ADP Employment Change': 'ADP Employment Change', 'Nonfarm Payrolls': nan, 'Capacity Utilization': 'Capacity Utilization', 'Durable Goods Orders': 'Durable Goods Orders', 'Markit Composite PMI': 'Markit US Composite PMI', 'Factory Orders': 'Factory Orders', 'Building Permits': 'Building Permits', 'Total Net TIC Flows': 'Total Net TIC Flows', 'Chicago PMI': 'Chicago Purchasing Manager', 'FOMC Rate Decision': 'FOMC Rate Decision (Upper Bound)', 'Existing Home Sales': 'Existing Home Sales', 'GDP Price Index': 'GDP Price Index'}, ('index', ''): {'CPI Ex Food and Energy MoM': 'CPI Ex Food and Energy MoM', 'PCE Core MoM': 'PCE Core MoM', 'Construction Spending MoM': 'Construction Spending MoM', 'Personal Spending': 'Personal Spending', 'Existing Home Sales MoM': 'Existing Home Sales MoM', 'ISM Non-Manufacturing PMI': 'ISM Non-Manufacturing PMI', 'Pending Home Sales MoM': 'Pending Home Sales MoM', 'FHFA House Price Index MoM': 'FHFA House Price Index MoM', 'Housing Starts': 'Housing Starts', 'S&P/CaseShiller 20-City Index NSA': 'S&P/CaseShiller 20-City Index NSA', 'Employment Cost Index': 'Employment Cost Index', 'New Home Sales': 'New Home Sales', 'Business Inventories MoM': 'Business Inventories MoM', 'Initial Jobless Claims': 'Initial Jobless Claims', 'Empire Manufacturing': 'Empire Manufacturing', 'Import Price Index MoM': 'Import Price Index MoM', 'ADP Employment Change': 'ADP Employment Change', 'Nonfarm Payrolls': 'Nonfarm Payrolls', 'Capacity Utilization': 'Capacity Utilization', 'Durable Goods Orders': 'Durable Goods Orders', 'Markit Composite PMI': 'Markit Composite PMI', 'Factory Orders': 'Factory Orders', 'Building Permits': 'Building Permits', 'Total Net TIC Flows': 'Total Net TIC Flows', 'Chicago PMI': 'Chicago PMI', 'FOMC Rate Decision': 'FOMC Rate Decision', 'Existing Home Sales': 'Existing Home Sales', 'GDP Price Index': 'GDP Price Index'}, ('ft', 'importance'): {'CPI Ex Food and Energy MoM': 'Medium', 'PCE Core MoM': 'Medium', 'Construction Spending MoM': 'Medium', 'Personal Spending': nan, 'Existing Home Sales MoM': nan, 'ISM Non-Manufacturing PMI': 'High', 'Pending Home Sales MoM': 'Medium', 'FHFA House Price Index MoM': 'Medium', 'Housing Starts': nan, 'S&P/CaseShiller 20-City Index NSA': 'Medium', 'Employment Cost Index': 'Medium', 'New Home Sales': 'Medium', 'Business Inventories MoM': 'Medium', 'Initial Jobless Claims': 'Medium', 'Empire Manufacturing': 'Medium', 'Import Price Index MoM': nan, 'ADP Employment Change': nan, 'Nonfarm Payrolls': 'High', 'Capacity Utilization': 'Medium', 'Durable Goods Orders': 'Medium', 'Markit Composite PMI': nan, 'Factory Orders': nan, 'Building Permits': 'Medium', 'Total Net TIC Flows': nan, 'Chicago PMI': 'Medium', 'FOMC Rate Decision': 'High', 'Existing Home Sales': 'Medium', 'GDP Price Index': nan}}
pd.DataFrame(dikt, columns=[('investingcom', 'importance'), ('investingcom', 'original_name'), ('bloomberg', 'importance'), ('bloomberg', 'original_name'), ('ft', 'importance'), ('ft', 'original_name'), ('index', '')])

Answer 1

下面的代码可以工作(使用 MySQL 5.1 进行测试)，但它会产生糟糕的列名:

from sqlalchemy import types, create_engine

df.rename_axis('common_names') \
  .to_sql('zzz', engine, index=True, index_label='common_names',
          if_exists='replace', dtype={'common_names':types.VARCHAR(50)})

MySQL数据库中的表结构(注意列名):

更新:我建议您在将此 DF 存储到 MySQL 数据库之前展平您的多列:

原始栏目:

In [266]: df.columns
Out[266]:
MultiIndex(levels=[['bloomberg', 'ft', 'index', 'investingcom'], ['', 'importance', 'original_name']],
           labels=[[3, 3, 0, 0, 1, 1, 2], [1, 2, 1, 2, 1, 2, 0]])

让我们压平它们:

In [267]: df.columns = ['{0[0]}__{0[1]}'.format(col) for col in df.columns]

In [268]: df.columns
Out[268]:
Index(['investingcom__importance', 'investingcom__original_name', 'bloomberg__importance', 'bloomberg__original_name', 'ft__importance',
       'ft__original_name', 'index__'],
      dtype='object')

In [272]: df.rename_axis('common_names') \
            .to_sql('zzz', engine, index=True, index_label='common_names',
                    if_exists='replace', dtype={'common_names':types.VARCHAR(50)})

它在 MySQL 数据库中的样子如何:

mysql> select common_names, investingcom__importance, investingcom__original_name from zzz;
+-----------------------------------+--------------------------+-----------------------------------------+
| common_names                      | investingcom__importance | investingcom__original_name             |
+-----------------------------------+--------------------------+-----------------------------------------+
| ADP Employment Change             |                        3 | ADP Nonfarm Employment Change           |
| Building Permits                  |                        3 | Building Permits                        |
| Business Inventories MoM          |                        2 | Business Inventories (MoM)              |
| CPI Ex Food and Energy MoM        |                        3 | Core CPI (MoM)                          |
| Capacity Utilization              |                     NULL | NULL                                    |
| Chicago PMI                       |                        2 | Chicago PMI                             |
| Construction Spending MoM         |                     NULL | NULL                                    |
| Durable Goods Orders              |                        2 | Durable Goods Orders (MoM)              |
| Empire Manufacturing              |                     NULL | NULL                                    |
| Employment Cost Index             |                        2 | Employment Cost Index (QoQ)             |
| Existing Home Sales               |                        3 | Existing Home Sales                     |
| Existing Home Sales MoM           |                        2 | Existing Home Sales (MoM)               |
| FHFA House Price Index MoM        |                     NULL | NULL                                    |
| FOMC Rate Decision                |                        3 | Fed Interest Rate Decision              |
| Factory Orders                    |                        2 | Factory Orders (MoM)                    |
| GDP Price Index                   |                        2 | GDP Price Index (QoQ)                   |
| Housing Starts                    |                        2 | Housing Starts                          |
| ISM Non-Manufacturing PMI         |                        3 | ISM Non-Manufacturing PMI               |
| Import Price Index MoM            |                        2 | Import Price Index (MoM)                |
| Initial Jobless Claims            |                        2 | Initial Jobless Claims                  |
| Markit Composite PMI              |                        2 | Markit Composite PMI                    |
| New Home Sales                    |                        3 | New Home Sales                          |
| Nonfarm Payrolls                  |                        3 | Nonfarm Payrolls                        |
| PCE Core MoM                      |                        2 | Core PCE Price Index (MoM)              |
| Pending Home Sales MoM            |                        3 | Pending Home Sales (MoM)                |
| Personal Spending                 |                        2 | Personal Spending (MoM)                 |
| S&P/CaseShiller 20-City Index NSA |                        2 | S&P;/CS HPI Composite - 20 n.s.a. (YoY) |
| Total Net TIC Flows               |                        2 | TIC Net Long-Term Transactions          |
+-----------------------------------+--------------------------+-----------------------------------------+
28 rows in set (0.00 sec)