In this tutorial, we are going to learn how to import data to Microsoft SQL Server from Google Sheets using Python.
Buy Me a Coffee? Your support is much appreciated!
PayPal Me: https://www.paypal.me/jiejenn/5
Venmo: @Jie-Jenn
import pandas as pd # pip install pandas
import pypyodbc as odbc # pip install pypyodbc
from Google import Create_Service
"""
Getting Dataset from Google Sheets
"""
CLIENT_SECRET_FILE = 'client_secret.json'
API_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
service = Create_Service(CLIENT_SECRET_FILE, API_NAME, API_VERSION, SCOPES)
google_sheets_id = '<Google sheets id'
response = service.spreadsheets().values().get(
spreadsheetId=google_sheets_id,
majorDimension='ROWS',
range='Data'
).execute()
rows = response['values'][1:]
"""
Push Dataset to SQL Server (database system)
"""
DRIVER_NAME = 'SQL SERVER'
SERVER_NAME = '<Server name>'
DATABASE_NAME = '<Database name>'
def connection_stirng(driver_name, server_name, database_name):
# uid=<username>;
# pwd=<password>;
conn_string = f"""
DRIVER={{{driver_name}}};
SERVER={server_name};
DATABASE={database_name};
Trust_Connection=yes;
"""
return conn_string
# connection object creation
try:
conn = odbc.connect(connection_stirng(DRIVER_NAME,SERVER_NAME,DATABASE_NAME))
print('Connection created')
except odbc.DatabaseError as e:
print('Database Error:')
print(str(e.value[1]))
except odbc.Error as e:
print('Connection Error')
print(str(e.value[1]))
else:
cursor = conn.cursor()
sql_insert = """
INSERT INTO StockData
VALUES(?, ?, ?, ?, ?, ?, ?)
"""
try:
cursor.executemany(sql_insert, rows)
cursor.commit()
print('Data import complete')
except Exception as e:
print(str(e.value[1]))
cursor.rollback()
finally:
cursor.close()
conn.close()
Dear Lie,
Thank you for this awesome tutorial. However, I need further assistance if you don’t mind helping.
Below is the error message I have been encountering and I cant seem to find a way around.
“Client_secret.json-GSDataPush2SQL-v4-[‘https://www.googleapis.com/auth/spreadsheets’]
[‘https://www.googleapis.com/auth/spreadsheets’]
Unable to connect.
name: GSDataPush2SQL version: v4
response = service.spreadsheets().values().get(
AttributeError: ‘NoneType’ object has no attribute ‘spreadsheets”
Please, what could cause this error message to be coming up.
Thanks again
Dear Jie,
Thank you for this awesome tutorial. However, I need further assistance if you don’t mind helping.
Below is the error message I have been encountering and I cant seem to find a way around.
“Client_secret.json-GSDataPush2SQL-v4-[‘https://www.googleapis.com/auth/spreadsheets’]
[‘https://www.googleapis.com/auth/spreadsheets’]
Unable to connect.
name: GSDataPush2SQL version: v4
response = service.spreadsheets().values().get(
AttributeError: ‘NoneType’ object has no attribute ‘spreadsheets”
Please, what could cause this error message to be coming up.
Thanks again
API name should be sheets.
Dear Jie,
Thank you very much. API name has been the problem.
Thanks again.
Dear Jie,
Trust you are well and doing great.
Can you please suggest to me what to do. Each time I run the python script, the entire record in the google sheet is pushed to the SQL server thereby causing duplicates.
How do I set up the script so that subsequent times when the script is ran, only update data is pushed to the SQL database.
Hearing from you soon.
Thanks