Bright Tutorials Bright Tutorials
CBSE Class 12 Informatics Practices Question 16 of 16

Importing/Exporting Data between CSV Files/MySQL and Pandas — Question 4

Back to all questions
4
Question

Question 4

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.

Solution
import pandas as pd
import mysql.connector as sqltor
mycon = sqltor.connect(host = "localhost",
                       user = "root",
                       passwd = "mypass",
                       database = "school")
mdf = pd.read_sql("SELECT * FROM student;", mycon)
ndf = pd.read_sql("SELECT * FROM subject;", mycon)
ldf = pd.read_sql("SELECT * FROM teacher;", mycon)
print("Student dataframe:\n")
print(mdf)
print("Subject dataframe:\n")
print(ndf)
print("Teacher dataframe:\n")
print(ldf)
Output
Student dataframe:

   NO     NAME  AGE DEPARTMENT  FEE SEX
0   1   PANKAJ   24   COMPUTER  120   M
1   2  SHALINI   21    HISTORY  200   F
2   3   SANJAY   22      HINDI  300   M
3   4    SUDHA   25    HISTORY  400   F
4   5   ANANYA   23   COMPUTER  450   F

Subject dataframe:

   Subject_id     name  Teacher_id
0         101     math         201
1         102  science         202
2         103  english         203
3         104    hindi         204

Teacher dataframe:

   No      Name  Age Department  Salary Sex  Dateofjoin
0   1     Jugal   34   Computer   12000   M  1997-01-10
1   2  Sharmila   31    History   20000   F  1998-03-24
2   3   Sandeep   32      Maths   30000   M  1996-12-12
3   4  Sangeeta   35    History   40000   F  1999-07-01
4   5    Rakesh   42      Maths   25000   M  1997-09-05
5   6     Shyam   50    History   30000   M  1998-06-27
6   7   Shiv Om   44   Computer   21000   M  1997-02-25
7   8  Shalakha   33      Maths   20000   F  1997-07-31
Answer

import
pandas
as
pd
import
mysql
.
connector
as
sqltor
mycon
=
sqltor
.
connect
(
host
=
"localhost"
,
user
=
"root"
,
passwd
=
"mypass"
,
database
=
"school"
)
mdf
=
pd
.
read_sql
(
"SELECT * FROM student;"
,
mycon
)
ndf
=
pd
.
read_sql
(
"SELECT * FROM subject;"
,
mycon
)
ldf
=
pd
.
read_sql
(
"SELECT * FROM teacher;"
,
mycon
)
print
(
"Student dataframe:
\n
"
)
print
(
mdf
)
print
(
"Subject dataframe:
\n
"
)
print
(
ndf
)
print
(
"Teacher dataframe:
\n
"
)
print
(
ldf
)
Output
Student dataframe:

NO NAME AGE DEPARTMENT FEE SEX
0 1 PANKAJ 24 COMPUTER 120 M
1 2 SHALINI 21 HISTORY 200 F
2 3 SANJAY 22 HINDI 300 M
3 4 SUDHA 25 HISTORY 400 F
4 5 ANANYA 23 COMPUTER 450 F

Subject dataframe:

Subject_id name Teacher_id
0 101 math 201
1 102 science 202
2 103 english 203
3 104 hindi 204

Teacher dataframe:

No Name Age Department Salary Sex Dateofjoin
0 1 Jugal 34 Computer 12000 M 1997-01-10
1 2 Sharmila 31 History 20000 F 1998-03-24
2 3 Sandeep 32 Maths 30000 M 1996-12-12
3 4 Sangeeta 35 History 40000 F 1999-07-01
4 5 Rakesh 42 Maths 25000 M 1997-09-05
5 6 Shyam 50 History 30000 M 1998-06-27
6 7 Shiv Om 44 Computer 21000 M 1997-02-25
7 8 Shalakha 33 Maths 20000 F 1997-07-31