Import Data from Excel into MySQL using Python
In this blog i will explain how to import data from Excel file into MySQL. Let’s see the implementation in Python.
Python Module Used:
- pymysql : pyMySQL is a python library for connecting to a MySQL database server from Python.This module has lots of features like it mad CRUD operations simple.
To use this library you need to install. For installation use below command
If you want to know more pymysql then refer pyMySQL documentations using below link
How to turn excel workbook into Mysql database using python
Is there any way to turn my excel workbook into MySQL database. Say for example my Excel workbook name is copybook.xls then MySQL database name would be copybook. I am unable to do it. Your help would be really appreciated.
1 Answer 1
Here I give an outline and explanation of the process including links to the relevant documentation. As some more thorough details were missing in the original question, the approach needs to be tailored to particular needs.
The solution
There’s two steps in the process:
1) Import the Excel workbook as Pandas data frame
Here we use the standard method of using pandas.read_excel to get the data out from Excel file. If there is a specific sheet we want, it can be selected using sheet_name . If the file contains column labels, we can include them using parameter index_col .
df contains now the following imaginary data frame, which represents the data in the original Excel file
2) Write records stored in a DataFrame to a SQL database
Pandas has a neat method pandas.DataFrame.to_sql for interacting with SQL databases through SQLAlchemy library. The original question mentioned MySQL so here we assume we already have a running instance of MySQL. To connect the database, we use create_engine . Lastly, we write records stored in a data frame to the SQL table called characters .
We can check if the data has been stored
or better, use pandas.read_sql_table to read back the data directly as data frame
Learn more
No MySQL instance available?
You can test the approach by using an in-memory version of SQLite database. Just copy-paste the following code to play around:
Name already in use
Work fast with our official CLI. Learn more about the CLI.
Sign In Required
Please sign in to use Codespaces.
Launching GitHub Desktop
If nothing happens, download GitHub Desktop and try again.
Launching GitHub Desktop
If nothing happens, download GitHub Desktop and try again.
Launching Xcode
If nothing happens, download Xcode and try again.
Launching Visual Studio Code
Your codespace will open once ready.
There was a problem preparing your codespace, please try again.
Latest commit
Git stats
Files
Failed to load latest commit information.
README.md
The project imports an Excel file into MySQL using Python Pandas. It then Separates the Spreadsheet programmatically into 2 separate worksheets into Sheet1]: Identified as Authentication and the Sheet2]: Employee Records.
- You have to be familiar with Python pandas and writing SQL queries to execute the project.
Import libraries/create engne
- from sqlalchemy import create_engine
- import pymysql
- import pandas as pd
Import import mysql.connector and create a database
- mycursor = mydb.cursor()
- mycursor.execute(«CREATE DATABASE A_100recordsDB»)
create dataframe from excel file
- df= pd.read_excel(«C:\Users\user\Desktop\python-files\100 Records2.xlsx»)
Create SQLAlchemy engine to connect to MySQL Database
- engine = create_engine(«mysql+pymysql://
: @ / » * .format(host=hostname, db=dbname, user=uname, pw=pwd))
Convert dataframe to sql table
df.to_sql(‘the_100_records_tbl2’, engine, index=False)
df = pd.read_sql_query(«SELECT * FROM the_100_records_tbl2», engine)
Check the presence of your database and table in MySQL Workbench
Use sql queries to extract data from your MySQL DB and place in two different data frames and place in 2 separate dataframes
Use the function below to transfer data from pandas to 2 separate excel sheet
About
The project imports an Excel file into MySQL using Python Pandas. It then Separate the Spreadsheet programmatically into 2 separate worksheets. Sheet1]: Identified as Authentication and the Sheet2]: Employee Records.
How To Import Excel File Into Database Using Python
In this article, we will see how to import an excel file into a database using python. Here, we will learn how to read data from an excel sheet and insert it into a database table using python script. You can insert data into a database using python script. python script to read excel files and insert them into the database
So, let's see how to import data from an excel file into MySQL using python, import data into a database using python, import data from excel into MySQL using python xlrd, and how to install xlrd in python.
Prerequisite:
-
: xlrd is a library for reading data and formatting information from Excel files.
- PyMySQL: pyMySQL is a python library for connecting to a MySQL database server from Python.
In this step, we will install PyMySQL using the following command.
Now, we will install xlrd library using the following command.
In this step, we will create a python script to import excel file data into the database using python.