consulta sql para google sheets

yuryrogens
(usa Debian)

Enviado em 23/04/2022 - 15:42h

boa tarde, gostaria de saber como importar minha consulta sql para o google sheets em uma celula em branco, se estiver preenchida vai para próxima linha isso por dia..

yuryrogens
(usa Debian)

Enviado em 23/04/2022 - 15:43h

# pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
# pip install pandas
# pip install gspread
# pip install pyodbc

import gspread
from tkinter.simpledialog import askfloat
from googleapiclient.discovery import build
from google.oauth2 import service_account
import pandas as pd
import pypyodbc as odbc # pip install pypyodbc
import os
#from Google import Create_Service

# The ID and range of a sample spreadsheet.
SERVICE_ACCOUNT_FILE = 'riocenteractive.json'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

creds = None
creds = service_account.Credentials.from_service_account_file(
SERVICE_ACCOUNT_FILE, scopes=SCOPES
)

SAMPLE_SPREADSHEET_ID = '1IJ6FBp51hlXfdRf15DZgOjzqJf0CP7a1UQTILwfydQc'

service = build('sheets', 'v4', credentials=creds)

###########################################################################################

server = ''
database = ''
username = ''
password = ''
cnxn = odbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

server = ''
database = ''
username = ''
password = ''
cnxn = odbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

cursor.execute("SELECT TOP 1 "
"DATACOMPRA = A.L1_DTLIM, HORACOMPRA = A.L1_HORA, "
"NOME = B.A1_NOME, CPF = B.A1_CGC, "
"'E-MAIL' = CASE WHEN LEN(ISNULL(CC.CLIEMAIL, '')) > 5 THEN CC.CLIEMAIL "
"WHEN LEN(ISNULL(DD.CLIEXTEMAIL2, '')) > 5 THEN DD.CLIEXTEMAIL2 "
"ELSE B.A1_EMAIL COLLATE Latin1_General_BIN END, "
"CELULAR = CASE WHEN LEN(ISNULL(CC.CLICELULAR, '')) > 5 THEN CC.CLICELULAR "
"WHEN LEN(ISNULL(DD.CLIEXTCELULAR2, '')) > 5 THEN DD.CLIEXTCELULAR2 "
"ELSE DD.CLIEXTCELULAR2 END, "
"DATACADASTRO = CONVERT(VARCHAR,DATEADD(DAY,((ASCII(SUBSTRING(A1_USERLGI,12,1))-50)*100+(ASCII(SUBSTRING(A1_USERLGI,16,1))-50)),'19960101'),112), "
"ANIVERSARIO = CASE WHEN LEN(B.A1_DTNASC) > 5 THEN B.A1_DTNASC "
"ELSE CONVERT(VARCHAR(10), CC.CLIDTANASCIMENTO, 112) END, "
"ENDERECO = B.A1_END, "
"DOCUMENTO = A.L1_DOC, "
"CODIGOPRODUTO = E.L2_PRODUTO, "
"QUANTIDADE = E.L2_QUANT, "
"VALORUNITARIO = E.L2_VRUNIT, "
"VALORPEDIDO = E.L2_VLRITEM, "
"DATAPEDIDO = A.L1_DTLIM, "
"LOJA = A.L1_FILIAL, "
"CODVENDEDOR = A.L1_VEND, VENDEDOR = D.A3_NOME, "
"PDV = A.L1_PDV "
"FROM "
"[192.168.102.6].DBTOTVS12.dbo.SL1010 A, [192.168.102.6].DBTOTVS12.dbo.SA3010 D, "
"[192.168.102.6].DBTOTVS12.dbo.SL2010 E, "
"[192.168.102.6].DBTOTVS12.dbo.SA1010 B LEFT OUTER JOIN CLIENTES CC ON CONVERT(DECIMAL(14, 0), B.A1_CGC) = CC.CLICPFCNPJ "
"LEFT OUTER JOIN CLIENTESEXTENSAO DD ON CC.CLICODIGO = DD.CLICODIGO "
"WHERE "
"A.L1_CLIENTE = B.A1_COD "
"AND A.L1_CLIENTE <> '000000001' "
"AND A.L1_DTLIM = '20220422' "
"AND A.L1_SITUA = 'OK' "
"AND A.L1_FILIAL = E.L2_FILIAL "
"AND A.L1_NUM = E.L2_NUM "
"AND A.L1_PDV = E.L2_PDV "
"AND A.L1_DOC = E.L2_DOC "
"AND E.L2_VEND = D.A3_COD "
"AND E.L2_FILIAL = D.A3_FILIAL "
"AND A.D_E_L_E_T_ = '' "
"AND B.D_E_L_E_T_ = '' "
"AND D.D_E_L_E_T_ = '' "
"AND E.D_E_L_E_T_ = '' ")

row = cursor.fetchone()
#while row:
# print(row[0])
# row = cursor.fetchone()
print (row)

###########################################################################################


# Call the Sheets API
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
range="Integração!A1:P1000").execute()
values = result.get('values', [])

print (values)



request = sheet.values().update(spreadsheetId=SAMPLE_SPREADSHEET_ID,
range="A2", valueInputOption="USER_ENTERED", body={"values"}).execute
print (request)