将 Pandas DataFrame 写入现有的 MySQL 数据库表 [英] Write Pandas DataFrame into a existing MySQL Database Table

问题描述

我使用 phpmyadmin 创建了一个名为 test 的数据库，其中有一个名为 client_info 的表.该数据库中的表为空(如附图所示)

I have created a database using phpmyadmin called test that has one table called client_info. The table in that database is empty (as shown in the attached image)

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

On the other side, I have written a code in python that read several CSV files and then extract specfic columns into dataframe called Client_Table1. This dataframe contains several rows and 3 columns

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

so far I have written this code :

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"])

我想将 Pandas DataFrame(即 Client_Table1)写入现有的 MySQL 数据库(即 test)中 client_info>.

I would like to Write Pandas DataFrame (i.e., Client_Table1) into the existed MySQL Database (i.e., test) specfically in the table 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**

推荐答案

在您需要的任何数据库操作的理想情况下:

In Ideal situation for any database operation you need:

• 数据库引擎
• 联系
• 从连接创建游标
• 创建插入 SQL 语句
• 逐行或全部读取csv数据并插入表格

这只是一个概念.

import pymysql

# Connect to the database
connection = pymysql.connect(host='localhost',
                         user='<user>',
                         password='<pass>',
                         db='<db_name>')


# create cursor
cursor=connection.cursor()

# Insert DataFrame recrds one by one.
sql = "INSERT INTO client_info(code,name, nac) VALUES(%s,%s,%s)"
for i,row in Client_Table1.iterrows():
    cursor.execute(sql, tuple(row))

    # the connection is not autocommitted by default, so we must commit to save our changes
    connection.commit()

connection.close()

这只是一个概念.我无法测试我编写的代码.可能有一些错误.您可能需要调试它.例如，数据类型不匹配，因为我将所有行视为带有 %s 的字符串.请在此处详细阅读.

That is just a concept. I can not test the code I have written. There might be some error. You might need to debug it. For example data type miss match as I am considering all row as string with %s. Please read more in detail here.

您可以使用sql语句为每个表创建单独的方法，然后在最后运行它们.同样，这只是一个概念，可以更广泛地推广.

You can create separate methods for each table with a sql statement and then run them at the end. Again that is just a concept and can be generalised more.

def insert_into_client_info():
    # create cursor
    cursor = connection.cursor()

    # Insert DataFrame recrds one by one.
    sql = "INSERT INTO client_info(code,name, nac) VALUES(%s,%s,%s)"
    for i, row in Client_Table1.iterrows():
        cursor.execute(sql, tuple(row))

        # the connection is not autocommitted by default, so we must commit to save our changes
        connection.commit()
    cursor.close()

def insert_into_any_table():
    "a_cursor"
    "a_sql"
    "a_for_loop"
        connection.commit()
    cursor.close()

## Pile all the funciton one after another
insert_into_client_info()
insert_into_any_table()

# close the connection at the end
connection.close()

这篇关于将 Pandas DataFrame 写入现有的 MySQL 数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！