Win10下调度Python脚本导入BigQuery及免重复授权方法咨询

Hey there! Let's tackle your two questions one by one—first scheduling your Python script to load data into BigQuery on Windows 10, then fixing that annoying repeated authentication prompt when using Task Scheduler.

Before diving into scheduling, make sure your script runs flawlessly manually first—confirm it can connect to BigQuery and load data without errors. That way, you’ll know any scheduling issues aren’t due to the script itself.

Here’s how to set up the task:

• Open Windows Task Scheduler: Search for "Task Scheduler" in the Windows search bar and launch it.
• Create a basic task: Click "Create Basic Task..." on the right-hand sidebar. Give it a clear name (like "BigQuery Daily Data Loader") and a brief description, then click Next.
• Choose a trigger: Pick how often you want the script to run—daily, weekly, on system startup, or on demand. Configure the timing details and click Next.
• Set the action: Select "Start a program" as the action. In the "Program/script" field, paste the full path to your Python interpreter (e.g., C:\Python310\python.exe; if using a virtual environment, use the Python executable inside your venv folder). In "Add arguments (optional)", paste the full path to your script, enclosed in quotes if the path has spaces (e.g., "C:\Projects\data_loader\load_to_bigquery.py"). Click Next.
• Finalize and tweak settings: Before clicking Finish, check the box that says "Open the Properties dialog for this task when I click Finish". In the properties window:
  • Go to the Actions tab to double-check your program path and script arguments are correct.
  • Switch to the Settings tab: Check "Run whether user is logged on or not" (so the task runs in the background even if you’re not signed in) and "Run with highest privileges" to avoid permission blocks.
  • Fill in the "Start in (optional)" field with the folder path where your script is located—this ensures the script can find any local files it depends on.

The prompt happens because your script is using interactive user authentication, which requires a browser login. For background tasks like those run by Task Scheduler, you need to use a service account instead—this lets your script authenticate automatically without user input.

Here’s how to set this up:

• Create a service account in Google Cloud Console:
  1. Navigate to your Google Cloud project, then go to IAM & Admin > Service Accounts.
  2. Click "Create Service Account", give it a name (e.g., "bigquery-scheduler-service") and description, then click Create and Continue.
  3. Assign the necessary permissions to the service account—for loading data into BigQuery, you’ll likely need BigQuery Data Editor and BigQuery Job User (adjust based on your script’s specific actions). Click Continue, then Done.
  4. Find your new service account in the list, click it, then go to the Keys tab. Click "Add Key > Create new key", select JSON as the key type, then click Create. Save the downloaded JSON key file to a secure, accessible location on your Windows machine (don’t share this file publicly!).
• Update your Python script to use the service account:
  If you’re using the official google-cloud-bigquery library, you have two easy options:
  Option 1: Hardcode the key path directly in your script (good for fixed environments):

  from google.cloud import bigquery
  
  # Replace with your actual key file path
  client = bigquery.Client.from_service_account_json(r'C:\Secure\Path\to\your-service-account-key.json')
  

  Option 2: Set an environment variable (more flexible for different setups):

  import os
  from google.cloud import bigquery
  
  # Set the environment variable to point to your key file
  os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = r'C:\Secure\Path\to\your-service-account-key.json'
  # Now the client will automatically use the service account
  client = bigquery.Client()
  

• Verify permissions: Make sure the user account running the Task Scheduler task has read access to the service account key file. Avoid storing the key in a folder that requires admin privileges unless the task is set to run as an admin.

• If the task fails, check the History tab in Task Scheduler—it logs detailed error messages (e.g., permission issues, missing dependencies, incorrect paths).
• Add logging to your script to track its execution:

  import logging
  logging.basicConfig(
      filename=r'C:\Logs\bigquery_loader.log',
      level=logging.INFO,
      format='%(asctime)s - %(levelname)s - %(message)s'
  )
  # Use logging.info() or logging.error() to record events in the script
  

• Ensure all required Python packages (like google-cloud-bigquery) are installed in the Python environment you’re using for the task—if using a venv, double-check the Python path in Task Scheduler points to the venv’s executable.

内容的提问来源于stack exchange，提问作者rdk77