远程服务器Python脚本调用pandas_gbq连接BigQuery认证失败

Hey there, let's tackle that frustrating TransportError you're hitting when running your pandas_gbq script on a remote server. This almost always boils down to network restrictions, credential issues, or environment mismatches between your local machine and the server. Here are the most common fixes to try:

1. Check if the server can reach Google's authentication endpoint #

The error says it can't connect to accounts.google.com, so first verify your server has network access to this domain. Run these commands in your server's terminal:

# Test HTTPS connectivity
curl https://accounts.google.com
# Or check if the port is open
telnet accounts.google.com 443

If either command fails, you'll need to work with your server admin to:

• Whitelist accounts.google.com in the firewall
• Open outbound port 443
• Configure any required corporate/proxy settings (more on that later)

2. Verify your private key file path is correct #

Local file paths don't always translate to remote servers. Don't rely on relative paths—use an absolute path to your JSON key file. For example:

# Instead of ./key.json
key_path = "/home/your_username/your_project/service_account_key.json"
df = pd.read_gbq(query, project=your_project_id, private_key=key_path)

You can also use os.path to confirm the file exists before running the query:

import os
if not os.path.exists(key_path):
    raise FileNotFoundError(f"Private key file not found at {key_path}")

3. Fix private key file permissions #

Google rejects service account keys that have overly open permissions (since they're sensitive). Set the file permissions to read/write only for the owner:

chmod 600 /path/to/your/service_account_key.json

This prevents other users on the server from accessing the key, which is required for proper authentication.

4. Use environment variables instead of passing the key directly #

A more reliable approach is to set the GOOGLE_APPLICATION_CREDENTIALS environment variable on your server. This lets pandas_gbq automatically pick up the key without you needing to pass it in code:

Option 1: Set it in the terminal before running your script #

export GOOGLE_APPLICATION_CREDENTIALS="/path/to/your/service_account_key.json"
python your_script.py

Option 2: Set it inside your Python script #

import os
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "/path/to/your/service_account_key.json"

# Now you can omit the private_key parameter
df = pd.read_gbq(query, project=your_project_id)

5. Upgrade your Google auth/pandas_gbq libraries #

Outdated versions of these libraries can have bugs that cause connectivity issues. Run this to upgrade all related packages:

pip install --upgrade pandas-gbq google-auth google-auth-oauthlib google-auth-httplib2

6. Configure proxy settings (if your server uses one) #

If your server requires a proxy to access external websites, you'll need to configure proxy settings for your Python script:

Option 1: Set proxy environment variables #

export HTTP_PROXY=http://your-proxy-server:port
export HTTPS_PROXY=http://your-proxy-server:port
python your_script.py

Option 2: Pass a proxied session to pandas_gbq #

import requests
from google.auth.transport.requests import Request
import pandas as pd

# Configure proxy
proxy_config = {
    "http": "http://your-proxy-server:port",
    "https": "http://your-proxy-server:port"
}
proxied_session = requests.Session()
proxied_session.proxies = proxy_config

# Create a request object with the proxied session
request = Request(session=proxied_session)

# Pass the request to read_gbq
df = pd.read_gbq(
    query,
    project=your_project_id,
    private_key="/path/to/key.json",
    request=request
)

Start with the first two checks (network access and file path/permissions)—those are the most frequent culprits. If those don't work, move through the other options one by one.

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