时间戳

首页 > 解决方案 > 将 Pandas DataFrame 写入现有的 MySQL 数据库表

python - 将 Pandas DataFrame 写入现有的 MySQL 数据库表

问题描述

我使用名为test的phpmyadmin创建了一个数据库，其中包含一个名为client_info的表。该数据库中的表是空的（如附图所示）

在此处输入图像描述

另一方面，我在 python 中编写了一个代码，它读取几个 CSV 文件，然后将特定列提取到名为Client_Table1的数据帧中。此数据框包含几行和 3 列

到目前为止，我已经写了这段代码：

import pandas as pd
import glob
path = r'D:\SWAM\ERP_Data'  # Path of Data

all_files = glob.glob(path + "/*.csv")
li = []
 for filename in all_files:
 df = pd.read_csv(filename,sep=';', index_col=None, header=0,encoding='latin-1')
 #df = pd.read_csv(filename, sep='\t', index_col=None, header=0)
 li.append(df)
 ERP_Data = pd.concat(li, axis=0, ignore_index=True)

 # rename the columns name
 ERP_Data.columns = ['Client_ID', 'Client_Name', 'FORME_JURIDIQUE_CLIENT', 'CODE_ACTIVITE_CLIENT', 'LIB_ACTIVITE_CLIENT', 'NACE', 
            'Company_Type', 'Number_of_Collected_Bins', 'STATUT_TI', 'TYPE_TI', 'HEURE_PASSAGE_SOUHAITE', 'FAMILLE_AFFAIRE',
            'CODE_AFFAIRE_MOUVEMENT', 'TYPE_MOUVEMENT_PIECE', 'Freq_Collection', 'Waste_Type', 'CDNO', 'CDQTE', 
            'BLNO', 'Collection_Date', 'Weight_Ton', 'Bin_Capacity', 'REF_SS_REF_CONTENANT_BL', 'REF_DECHET_PREVU_TI', 
            'Site_ID', 'Site_Name', 'Street', 'ADRCPL1_SITE', 'ADRCPL2_SITE', 'Post_Code',
            'City', 'Country','ZONE_POLYGONE_SITE' ,'OBSERVATION_SITE', 'OBSERVATION1_SITE', 'HEURE_DEBUT_INTER_MATIN_SITE', 
            'HEURE_FIN_INTER_MATIN_SITE', 'HEURE_DEBUT_INTER_APREM_SITE', 'HEURE_DEBUT_INTER_APREM_SITE', 'JOUR_PASSAGE_INTERDIT', 'PERIODE_PASSAGE_INTERDIT', 'JOUR_PASSAGE_IMPERATIF',
            'PERIODE_PASSAGE_IMPERATIF']
# extracting specific columns
Client_Table=ERP_Data[['Client_ID','Client_Name','NACE']].copy()
# removing duplicate rows
Client_Table1=Client_Table.drop_duplicates(subset=[ "Client_ID","Client_Name" , "NACE"])

Client_Table1 数据示例

我想将 Pandas DataFrame（即Client_Table1）写入现有的 MySQL 数据库（即test），具体在表client_info中。

the expected output in MySQL Database (i.e., **test**), would be

 writing the **Client_ID** column (i.e., values of **Client_ID** column)  into MySQL Database column **code**
 writing the **Client_Name** column into MySQL Database column **name**
 writing the **NACE** column into MySQL Database column **nac**

标签： pythonmysqlpandas

解决方案

我今天早上为另一个用户写了这个答案，并认为它也可能对你有所帮助。

pandas此代码从 CSV 文件读取并使用和写入 MySQL sqlalchemy。

如果您需要任何调整以更具体地帮助您，请告诉我。

回答：

下面的代码执行以下操作：

  • MySQL 数据库engine（连接）已创建。
  • 从 CSV 文件中读取的地址数据（编号、地址）。
  • 从源数据中替换了非字段分隔逗号，并删除了额外的空格。
  • 编辑后的数据输入DataFrame
  • DataFrame用于将数据存储到 MySQL 中。
    import csv
    import pandas as pd
    from sqlalchemy import create_engine

    # Set database credentials.
    creds = {'usr': 'admin',
             'pwd': '1tsaSecr3t',
             'hst': '127.0.0.1',
             'prt': 3306,
             'dbn': 'playground'}
    # MySQL conection string.
    connstr = 'mysql+mysqlconnector://{usr}:{pwd}@{hst}:{prt}/{dbn}'
    # Create sqlalchemy engine for MySQL connection.
    engine = create_engine(connstr.format(**creds))

    # Read addresses from mCSV file.
    text = list(csv.reader(open('comma_test.csv'), skipinitialspace=True))

    # Replace all commas which are not used as field separators.
    # Remove additional whitespace.
    for idx, row in enumerate(text):
        text[idx] = [i.strip().replace(',', '') for i in row]

    # Store data into a DataFrame.
    df = pd.DataFrame(data=text, columns=['number', 'address'])
    # Write DataFrame to MySQL using the engine (connection) created above.
    df.to_sql(name='commatest', con=engine, if_exists='append', index=False)

源文件 ( comma_test.csv):

"12345" , "123 abc street, Unit 345"
"10101" , "111 abc street, Unit 111"
"20202" , "222 abc street, Unit 222"
"30303" , "333 abc street, Unit 333"
"40404" , "444 abc street, Unit 444"
"50505" , "abc DR, UNIT# 123 UNIT 123"

未经编辑的数据：

['12345 ', '123 abc street, Unit 345']
['10101 ', '111 abc street, Unit 111']
['20202 ', '222 abc street, Unit 222']
['30303 ', '333 abc street, Unit 333']
['40404 ', '444 abc street, Unit 444']
['50505 ', 'abc DR, UNIT# 123 UNIT 123']

编辑数据：

['12345', '123 abc street Unit 345']
['10101', '111 abc street Unit 111']
['20202', '222 abc street Unit 222']
['30303', '333 abc street Unit 333']
['40404', '444 abc street Unit 444']
['50505', 'abc DR UNIT# 123 UNIT 123']

从 MySQL 查询：

number  address
12345   123 abc street Unit 345
10101   111 abc street Unit 111
20202   222 abc street Unit 222
30303   333 abc street Unit 333
40404   444 abc street Unit 444
50505   abc DR UNIT# 123 UNIT 123

致谢：

这是一个冗长的方法。但是，每个步骤都被有意分解，以清楚地显示所涉及的步骤。

推荐阅读