Assuming the given table: Product. Write the python code for the following:

Q. Assuming the given table: Product. Write the python code for the following:

Item	Company	Rupees	USD
TV	LG	12000	700
TV	VIDEOCON	10000	650
TV	LG	15000	800
AC	SONY	14000	750

a) To create the data frame for the above table.
b) To add the new rows in the data frame.
c) To display the maximum price of LG TV.
d) To display the Sum of all products.
e) To display the median of the USD of Sony products.
f) To sort the data according to the Rupees and transfer the data to MySQL.
g) To transfer the new dataframe into the MySQL with new values.

Answer :-

(a)

import mysql.connector as a

db=a.connect(user='root',passwd='0000000000' ,host='localhost',database='hindustan')
import pandas as pd
df=pd.read_sql(f'select * from product',db)

print(df)

(b)

df.loc[4] = ['corona_vaccine','hindustan',74,1]

(c)


(d)

df.Rupees.sum()

(e)

df[df['Company']=='Sony']['USD'].median()

(f)

print(df.sort_values(by=['rupees']))

from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://root:0000000000@localhost/hindustan')
conn=engine.connect()
df.to_sql('Product_table',conn,index=False)   #data transferred to sql

(g)

d={'item':['tv'],'Company':['lg'],'rupees':[17000]}
df2=pd.DataFrame(d)
df.append(df2)  #  now df have new values

from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://root:0000000000@localhost/hindustan')
conn=engine.connect()
df.to_sql('new_Product_table',conn,index=False)   #data transferred to sql