MySQL to Google sheets and vice versa


Google sheets to MySQL and vice versa
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)
google sheet data from MySQL table

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()

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2022 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer