Data exchange between google sheets and MySQL database using Pandas Dataframe

We can transfer data from Google sheets to MySQL database table and from MySQL to Google sheets by using Pandas DataFrame.

Transfer of data from Google sheets to MySQL database table and vice versa using Pandas DataFrame

Pygsheets and google API authorization Pandas DataFrame

From MySQL to Google sheets

We will create a Pandas DataFrame by using data from MySQL student table. ( download the sql dump at the end of this page. )
Connection to MySQL database

from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")

We will use read_sql() to create the DataFrame by using the query.

import pandas as pd 
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial")
query="SELECT * FROM student WHERE class='four'"
df = pd.read_sql(query,my_conn,index_col='id')
print(df)

Using the Pandas DataFrame df, we will use set_dataframe() to add the dataframe to google sheets.
Full code is here

import pandas as pd 
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial")
query="SELECT * FROM student WHERE class='four'"
df = pd.read_sql(query,my_conn,index_col='id')
print(df)

import pygsheets
path='G:\\My drive\\testing\\google-sheet\\creds1.json'
gc=pygsheets.authorize(service_account_file=path)
sh=gc.open('my_gsheets1')
wk1=sh[0]
wk1.clear() # remove previous data if any 
wk1.set_dataframe(df,(1,1),copy_index=True, extend=True)

From Google sheets to MySQL table

In first part by using get_as_df() we will collect data from Google sheets and create the DataFrame.

In second part we will use to_sql() to create and store data from the Dataframe to MySql table ( student2 table ) .

import pygsheets
path='G:\\My drive\\testing\\google-sheet\\creds1.json'
gc=pygsheets.authorize(service_account_file=path)
sh=gc.open('my_gsheets1')
wk1=sh[0]
df = wk1.get_as_df(index_column=1)
print(df) 
# inserting dataframe to MySQL table:  Part 2 
import pandas as pd 
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")
df.to_sql(con=my_conn,name='student2',if_exists='append', index=False)

Pygsheets and google API authorization set_dataframe()

Pandas DataFrame Python- Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

MySQL to Google sheets and vice versa

From MySQL to Google sheets

From Google sheets to MySQL table

Subhendu Mohapatra