phpmyadmin Question posted in
The official documentation can be found here.

Write Pandas DataFrame into a existing MySQL Database Table – Phpmyadmin

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)

enter image description here

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

an example of the Client_Table1 data

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**
Tags:

3

Answers


  1. 0

    try this doc
    you have to create a connection, and then write data to your database.

    Login or Signup to reply.
  2. 0

    In Ideal situation for any database operation you need:

    • A database engine
    • A Connection
    • Create a cursor from connection
    • Create a Insert SQL statement
    • Read the csv data row by row or all together and insert into the table

    That is just a concept.

    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()

    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.

    Edit Based on Comment:

    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()
    Login or Signup to reply.
  3. 0

    I wrote this answer for another user this morning and thought it might help you as well.

    This code reads from a CSV file and writes to MySQL using pandas and sqlalchemy.

    Let me know if you need any tweaks to help you more specifically.

    Answer:

    The code below performs the following actions:

    • MySQL database engine (connection) created.
    • Address data (number, address) read from CSV file.
    • Non-field separating commas replaced from source data, and extra whitespace removed.
    • Edited data fed into a DataFrame
    • DataFrame used to store data into 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)

    Source File (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"

    Unedited Data:

    ['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']

    Edited Data:

    ['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']

    Queried from 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

    Acknowledgement:

    This is a long-winded approach. However, each step has been broken down intentionally to clearly show the steps involved.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search