In this tutorial, we are going to learn how to export data to Google Sheets from Microsoft SQL Server using Python.
Check out Kite (free AI Coding Assistant) → Link
Buy Me a Coffee? Your support is much appreciated!
PayPal Me: https://www.paypal.me/jiejenn/5
Venmo: @Jie-Jenn
from Google import Create_Service # link to source code is in the description import pypyodbc as odbc # pip install pypyodbc import pandas as pd # pip install pandas """ Step 1.1 Connect to MS SQL Server Database System """ DRIVER_NAME = 'SQL Server' SERVER_NAME = '<server_name>' DATABASE_NAME = '<database_name>' def connection_string(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 try: conn = odbc.connect(connection_string(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: # sql_query = """ # SELECT TOP 2000 Traffic_Report_Id, Issue_Reported, Published_Date, Issue_Reported # FROM Austin_Traffic_Incident # """ sql_query = """ SELECT Issue_Reported, COUNT(Issue_Reported) as Incident_Count FROM Austin_Traffic_Incident # WHERE YEAR(Published_DATE) IN (?) GROUP BY Issue_Reported """ cursor = conn.cursor() # cursor.execute(sql_query) cursor.execute(sql_query, [2020]) """ Step 1.2 Retrieve Dataset from SQL Server """ recordset = cursor.fetchall() columns = [col[0] for col in cursor.description] df = pd.DataFrame(recordset, columns=columns) if 'published_date' in df.columns: df['published_date'] = df['published_date'].dt.strftime('%Y-%m-%d %H:%M:%S') recordset = df.values.tolist() """ Step 2. Export Dataset to Google Spreadsheets """ gs_sheet_id = '<google sheets id>' tab_id = '<tab id> INT' 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) # create spreadsheets reference object mySpreadsheets = service.spreadsheets().get( spreadsheetId=gs_sheet_id ).execute() recordset tab_name = [sheet['properties']['title'] for sheet in mySpreadsheets['sheets'] if sheet['properties']['sheetId'] == tab_id][0] """ Clear workshete content """ service.spreadsheets().values().clear( spreadsheetId=gs_sheet_id, range=tab_name ).execute() """ Insert dataset """ def construct_request_body(value_array, dimension: str='ROWS') -> dict: try: request_body = { 'majorDimension': dimension, 'values': value_array } return request_body except Exception as e: print(e) return {} """ Insert column names """ request_body_columns = construct_request_body([columns]) service.spreadsheets().values().update( spreadsheetId=gs_sheet_id, valueInputOption='USER_ENTERED', range=f'{tab_name}!A1', body=request_body_columns ).execute() """ Insert rows """ request_body_values = construct_request_body(recordset) service.spreadsheets().values().update( spreadsheetId=gs_sheet_id, valueInputOption='USER_ENTERED', range=f'{tab_name}!A2', body=request_body_values ).execute() print('Task is complete') cursor.close() conn.close()