Informatics Practices
Write a program to get following data in two DataFrames :
df 1:
| Roll no | Name |
|---|---|
| 1 | ABC |
| 2 | DEF |
df2:
| Roll no | Marks 1 | Marks 2 | Marks 3 |
|---|---|---|---|
| 1 | 70 | 80 | 75 |
| 2 | 60 | 65 | 70 |
Store these DataFrames as two separate tables in the same database.
Answer
import pandas as pd
from sqlalchemy import create_engine
import pymysql
data1 = {'Roll no': [1, 2], 'Name': ['ABC', 'DEF']}
data2 = {'Roll no': [1, 2], 'Marks1' : [70, 60], 'Marks2': [80, 65],'Marks3' : [75, 70]}
df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)
engine = create_engine('mysql+pymysql://root:Mypass@localhost/database')
mycon = engine.connect()
df1.to_sql('first', mycon, if_exists = 'replace', index = False)
df2.to_sql('second', mycon, if_exists = 'replace', index = False)Output
First table:
+---------+------+
| Roll no | Name |
+---------+------+
| 1 | ABC |
| 2 | DEF |
+---------+------+
Second table:
+---------+--------+--------+--------+
| Roll no | Marks1 | Marks2 | Marks3 |
+---------+--------+--------+--------+
| 1 | 70 | 80 | 75 |
| 2 | 60 | 65 | 70 |
+---------+--------+--------+--------+
Related Questions
Write a program to read details such as Item, Sales made in a DataFrame and then store this data in a CSV file.
Write a program to read data from a CSV file where separator character is '@'. Make sure that :
- the top row is used as data, not as column headers.
- only 20 rows are read into DataFrame.
You have a database on MySQL namely school having three tables in it — Student, Subject, Teacher. Write a program to store these tables in three DataFrames.
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.