Importing CSV into mysql

 

conda install -c anaconda sqlalchemy

Pip install pandas

 Way 1:

from sqlalchemy import create_engine as ce

mysqlengine= ce("mysql+mysqldb://root:root@127.0.0.1:3306/cancer_data")

import pandas as pd

df = pd.read_csv("D:/DownloadsD/new/health_statistics.csv", header=None)

df=df.iloc[1:]

#print(df.columns)

df.to_sql('health_statistics', mysqlengine, if_exists='append', index= False)

Way 2: 

import MySQLdb as m

# Connect to the MySQL database

mysql_connection = m.connect(

    user="root",

    password="root",

    database="cancer_data"

)

# Create a cursor object to execute SQL queries

cursor = mysql_connection.cursor()

# Path to your CSV file

csv_file = 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/health_statistics.csv'

# Name of the table in the database

table_name = 'health_statistics'

# SQL query to load data from CSV into the table

load_query = f"""

    LOAD DATA INFILE '{csv_file}'

    INTO TABLE {table_name}

    FIELDS TERMINATED BY ','

    ENCLOSED BY '"'

    LINES TERMINATED BY '\n'

    IGNORE 1 ROWS

"""

# Execute the query

cursor.execute(load_query)

# Commit the changes

mysql_connection.commit()

# Close the cursor and database connection

cursor.close()

mysql_connection.close()

Way 3: 

1 . Put the data in the uploads folder of the mysql programdata file

2. Create the table

3. use the following code:

    LOAD DATA INFILE '{csv_file_path}'

    INTO TABLE {table_name}

    FIELDS TERMINATED BY ','

    ENCLOSED BY '"'

    LINES TERMINATED BY '\n'

    IGNORE 1 ROWS

Way 4: (When Previous use cases fail)

This is similar to the first one. But, one thing to remember is, this step needs to be added to the first Way if the CSV file column names are different. There are multiple ways of dealing with it. We can go to the CSV file and change the columns one by one. If it is not possible to do it everyday, then we will be using the following piece of code. 

"""

column_mapping = {

    0: 'column1_name',

    1: 'column2_name',

    # Add more mappings as needed for the specific columns

}

# Rename the columns in the DataFrame using the mapping dictionary

df = df.rename(columns=column_mapping)

""" 

Happy Coding.