The Dataframe SDF stores the sales records of 100 salesmen. Write a program to store this as a table in database namely company on MySQL.

Q. The Dataframe SDF stores the sales records of 100 salesmen. Write a program to store this as a table in database namely "company" on MySQL.

Answer :-

import pandas as pd
import pymysql
from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://root:0000000000@localhost/company')
conn=engine.connect()

d1={'sales_record':[1,2,3,4,5,6,7,8,9,10,11, 12, 13, 14, 15, 16, 17, 18, 19, \
               20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, \
               33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, \
               46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, \
               62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, \
               79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, \
               93, 94, 95, 96, 97, 98, 99, 100]}
SDF=pd.DataFrame(d1)

SDF.to_sql('sales_record',conn)

Explanation :-

1. It imports the necessary libraries:

pandas as pd: A powerful data manipulation library.
pymysql: A library to connect to MySQL databases using Python.
create_engine from sqlalchemy: A function to create a database connection engine.

2. It creates a database connection engine:

The create_engine function is used to create a connection engine to a MySQL database named "company" hosted on the localhost.

The connection URL specifies the database type (mysql), username (root), password (0000000000), host (localhost), and the database name (company).

The engine object is created to manage the database connection.

3. It establishes a connection to the database:

The engine.connect() method is used to establish a connection to the MySQL database.

4. It creates a dictionary d1:

The dictionary d1 contains a single key-value pair. The key is 'sales_record' and the value is a list of integers from 1 to 100.

5. It creates a DataFrame SDF (Sales DataFrame):

The pd.DataFrame() constructor is used to create a DataFrame called SDF using the data from the dictionary d1.

6. It saves the DataFrame to the MySQL database:

The to_sql() method of the DataFrame SDF is used to save the contents of the DataFrame to a table named 'sales_record' in the MySQL database specified by the conn connection.

This will create a new table in the "company" database with the name "sales_record" and populate it with the data from the DataFrame SDF.