How to connect to mysql using python and import the csv file into mysql and create a table?

This recipe helps you connect to mysql using python and import the csv file into mysql and create a table.

Recipe Objective: How to connect to MySQL using Python and import the CSV file into MySQL and create a table?

In big data scenarios, we sometimes use MySQL as a relational database to store transactional data and Sqoop the data into HDFS-based environments for further analysis to gain business insights out of the data. We interact with the MySQL database to store and retrieve data; sometimes, we create tables through CSV files. 

This recipe shows you how to use a Python script to read CSV file and insert into database/table.

System Requirements For Python Import CSV File TO MySQL

  • Install the pydrive python module as follows :

  • pip install mysql-connector-python

  • pip install pandas

  • MySQL Workbench with mysql

  • Link to install mysql workbench database : click here

  • The below codes can be run in Jupyter notebook , or any python console

Get Closer To Your Dream of Becoming a Data Scientist with 70+ Solved End-to-End ML Projects

Step 1: Prepare the CSV File

Prepare the CSV file you would like to import to MySQL. For example, I prepared a simple CSV file with the following data:

Note: the above employee csv data is taken from the below link employee_data

Step 2: Import the CSV to Database Using Python.

Next, import the CSV to MySQL using Python pandas library. 

You can use the code below to import the CSV file and create the DataFrame. You must change the path name to reflect the location where the CSV file is stored on your computer.

import pandas as pd empdata = pd.read_csv('C:\\Users\\XXXXX\\empdata.csv', index_col=False, delimiter = ',') empdata.head()

Output of the above code:

Step 3: Connect to MySQL using Python and Create a Database

Create a connection object to MySQL. The connect() constructor creates a connection to MySQL and returns a MySQLConnection object.

import mysql.connector as msql from mysql.connector import Error try: conn = msql.connect(host='localhost', user='root', password='root@123')#give ur username, password if conn.is_connected(): cursor = conn.cursor() cursor.execute("CREATE DATABASE employee") print("Database is created") except Error as e: print("Error while connecting to MySQL", e)

Note: If you don't connect, please install the mysql-connector-python package. Type the following command: pip install mysql-connector-python

Output of the above code: After running the above the code will create an employee database in mysql as shown in below.

Step 4: MySQL Create table From CSV File and Import the CSV data into the MySQL table

This step shows how to create table from CSV file in MySQL.

We will create an employee_data table under the employee database and insert the records in MySQL with below python code.

Build Professional SQL Projects for Data Analysis with ProjectPro

import mysql.connector as msql from mysql.connector import Error try: conn = mysql.connect(host='localhost', database='employee', user='root', password='root@123') if conn.is_connected(): cursor = conn.cursor() cursor.execute("select database();") record = cursor.fetchone() print("You're connected to database: ", record) cursor.execute('DROP TABLE IF EXISTS employee_data;') print('Creating table....') # in the below line please pass the create table statement which you want #to create cursor.execute("CREATE TABLE employee_data(first_name varchar(255),last_name varchar(255),company_name varchar(255),address varchar(255),city varchar(255),county varchar(255),state varchar(255),zip int,phone1 varchar(255),phone2 varchar(255),email varchar(255),web varchar(255))") print("Table is created....") #loop through the data frame for i,row in empdata.iterrows(): #here %S means string values sql = "INSERT INTO employee.employee_data VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" cursor.execute(sql, tuple(row)) print("Record inserted") # the connection is not auto committed by default, so we must commit to save our changes conn.commit() except Error as e: print("Error while connecting to MySQL", e)

Output of the above code:

Output from workbench:

 

Explore More Data Science and Machine Learning Projects for Practice. Fast-Track Your Career Transition with ProjectPro

Step 5: Query the Table

Query the table to make sure that our inserted data has been saved correctly.

# Execute query sql = "SELECT * FROM employee.employee_data" cursor.execute(sql) # Fetch all the records result = cursor.fetchall() for i in result: print(i)

Output of the Above code:

Output from workbench:

 

How to Extract Data from MySql to CSV in Python?

The following code shows how you can extract data from MySQL into a CSV file using Python-

 

import pymysql

import csv

 

# Connect to MySQL

conn = pymysql.connect(host='localhost',

                       user='root',

                       password='password',

                       db='my_database')

 

# Create a cursor

cur = conn.cursor()

 

# Get the data from the table

cur.execute('SELECT * FROM my_table')

 

# Get the results as a list of tuples

results = cur.fetchall()

 

# Close the cursor

cur.close()

 

# Open a CSV file for writing

with open('my_data.csv', 'w') as csvfile:

  # Create a CSV writer object

  writer = csv.writer(csvfile)

 

  # Write the header row

  writer.writerow(['column1', 'column2', 'column3'])

 

  # Write the data rows

  for row in results:

    writer.writerow(row)

 

# Close the CSV file

csvfile.close()

 

We first connect to MySQL using the pymysql library. Then, we create a cursor and use it to execute a query to get the data from the table. We then get the results as a list of tuples. Next, we close the cursor. Then, we open a CSV file for writing and create a CSV writer object. We then write the header row to the CSV file. Finally, we write the data rows to the CSV file and close the CSV file.

 

What Users are saying..

profile image

Abhinav Agarwal

Graduate Student at Northwestern University
linkedin profile url

I come from Northwestern University, which is ranked 9th in the US. Although the high-quality academics at school taught me all the basics I needed, obtaining practical experience was a challenge.... Read More

Relevant Projects

Build a Graph Based Recommendation System in Python-Part 2
In this Graph Based Recommender System Project, you will build a recommender system project for eCommerce platforms and learn to use FAISS for efficient similarity search.

Loan Eligibility Prediction Project using Machine learning on GCP
Loan Eligibility Prediction Project - Use SQL and Python to build a predictive model on GCP to determine whether an application requesting loan is eligible or not.

Learn to Build an End-to-End Machine Learning Pipeline - Part 2
In this Machine Learning Project, you will learn how to build an end-to-end machine learning pipeline for predicting truck delays, incorporating Hopsworks' feature store and Weights and Biases for model experimentation.

Hands-On Approach to Master PyTorch Tensors with Examples
In this deep learning project, you will learn how to perform various operations on the building block of PyTorch : Tensors.

Llama2 Project for MetaData Generation using FAISS and RAGs
In this LLM Llama2 Project, you will automate metadata generation using Llama2, RAGs, and AWS to reduce manual efforts.

Build Regression (Linear,Ridge,Lasso) Models in NumPy Python
In this machine learning regression project, you will learn to build NumPy Regression Models (Linear Regression, Ridge Regression, Lasso Regression) from Scratch.

Skip Gram Model Python Implementation for Word Embeddings
Skip-Gram Model word2vec Example -Learn how to implement the skip gram algorithm in NLP for word embeddings on a set of documents.

Build a Face Recognition System in Python using FaceNet
In this deep learning project, you will build your own face recognition system in Python using OpenCV and FaceNet by extracting features from an image of a person's face.

LLM Project to Build and Fine Tune a Large Language Model
In this LLM project for beginners, you will learn to build a knowledge-grounded chatbot using LLM's and learn how to fine tune it.

Deploy Transformer-BART Model on Paperspace Cloud
In this MLOps Project you will learn how to deploy a Tranaformer BART Model for Abstractive Text Summarization on Paperspace Private Cloud