代码之家 › 专栏 › 技术社区 › Pedro Martins de Souza Regeesh Chandran

Pandas to_sql-添加数据帧时增加表的索引

sqlalchemy pandas mysql python

Pedro Martins de Souza Regeesh Chandran · 技术社区 · 7 年前

我一直在致力于开发一款产品,该产品以数据分析Python 3.7.0脚本的日常执行为中心。每天午夜,它将处理大量数据,然后将结果导出到两个MySQL表。第一个表将只包含与当前日期相关的数据,而另一个表将包含所有执行的串联数据。

为了举例说明我目前拥有的东西,请参见下面的代码,假设 df 将是数据分析生成的最终数据帧:

import pandas as pd
import sqlalchemy

engine = sqlalchemy.create_engine(r"mysql+pymysql://user:psswd@localhost/pathToMyDB")

df = pd.DataFrame({'Something':['a','b','c']})

df.to_sql('DReg', engine, index = True, if_exists='replace') #daily database
df.to_sql('AReg', engine, index = False, if_exists='append') #anual database

正如你在我的第二个 to_sql 函数,我不是在为anual数据库设置索引。然而,我的经理让我这样做,创建了一个以一个简单规则为中心的索引:它将是一个自动递增的数字索引,将自动为数据库中保存的与其位置对应的每一行赋予一个数字。

基本上,我第一次存钱 df ,数据库应该如下所示:

index   Something
0       a
1       b
2       c

在我的第二次处决中:

index   Something
0       a
1       b
2       c
3       a
4       b
5       c

然而,当我将索引设置为 True 第二 df.to_sql 命令(将其转换为 df.to_sql('AReg', engine, index = True, if_exists='append') ),在执行两次之后,我的数据库最终看起来像:

index   Something
0       a
1       b
2       c
0       a
1       b
2       c

我做了一些研究,但找不到一种方法来允许指数自动增加。我曾考虑在每次执行时读取anual数据库,然后调整数据帧的索引以适应它,但我的数据库很容易变得非常庞大,这将使其执行速度慢得离谱(同时也禁止我在两台计算机上同时执行同一数据分析,而不影响索引)。

那么,让这个索引起作用的最佳解决方案是什么呢?我错过了什么?

0 回复 | 直到 7 年前

tvgriek 6 年前

尽管Pandas有很多导出选项,但它的主要用途并不是用作数据库管理api。管理索引通常是数据库应该处理的事情。

我建议你 index=False, if_exists='append' 并创建具有自动增量索引的表:

CREATE TABLE AReg (
     id INT NOT NULL AUTO_INCREMENT,
     # your fields here
     PRIMARY KEY (id)
);

Yong Wang 6 年前

这是我的解决方案。SQL+Pythonã

使用SQL获取最大索引id,而不是读取整个表。它在DB和python上运行速度快、负载轻。

更新需要从数据库顺序读取的id,以确保多用户/会话情况下的唯一id。

最好使用自动增量id设计表。如果没有,则需要遵循以下案例。所有新id都应该从数据库的序列实例中获取。Sequence实例可以确保id是唯一的,即使是多用户/sesseion读取。

在mysql中,我们手动获取max id。在Oracle或postgresql中,我们可以通过高级sql命令获取最大序列id。

import pandas as pd
from pprint import pprint
from sqlalchemy import create_engine


db_name = 'temp'
table_name = 'tmp_table'
index_name = 'id'
mysql_url = f'mysql+mysqlconnector://root:Root123@192.168.100.200:13306/{db_name}'
engine=create_engine(mysql_url)

def to_sql_seq(df,table_name=table_name, engine=engine):

    get_seq_id_sql = f"""
                       select your_sequence.nextval as id
                        from dual 
                         connect by level < {df.shape[0]}
                     """

    # sql_get_max_id = f'select max({index_name}) as id from {table_name}'

    s_id = pd.read_sql(get_seq_id_sql , engine)

    df.index =s_id['id'].values
    df.index.name=index_name
    df.to_sql(table_name,engine,if_exists='append')
    return
#Check the current database record
current_table = pd.read_sql(f"select * from {table_name}",engine)
pprint(current_table)

#Simu the new data
new_data = [1,2,3,4]
new_table = pd.DataFrame(new_data,columns=['value'])
to_sql_seq(new_table)

#show the index auto_increment result
inserted_table = pd.read_sql(f'select * from {table_name}',engine)
pprint(inserted_table)

和输出

   id  value
0   1    123
1   2    234
2   3      1
3   4      2
4   5      3
5   6      4
   id  value
0   1    123
1   2    234
2   3      1
3   4      2
4   5      3
5   6      4
6   7      1
7   8      2
8   9      3
9  10      4