Importing/Exporting Data between CSV Files/MySQL and Pandas — Question 3
Back to all questionsWrite 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.
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)First table:
+---------+------+
| Roll no | Name |
+---------+------+
| 1 | ABC |
| 2 | DEF |
+---------+------+
Second table:
+---------+--------+--------+--------+
| Roll no | Marks1 | Marks2 | Marks3 |
+---------+--------+--------+--------+
| 1 | 70 | 80 | 75 |
| 2 | 60 | 65 | 70 |
+---------+--------+--------+--------+
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 |
+---------+--------+--------+--------+