Python3.7将API返回Timestamp插入BigQuery时解析报错求助

I see the issue here—you're overcomplicating the datetime conversion by going through JSON serialization first, which leads to formatting inconsistencies that BigQuery rejects. Let's fix this by directly constructing a UTC datetime object from the seconds and nanos fields, then formatting it perfectly for BigQuery's Timestamp column.

Step-by-Step Fix #

1. Extract raw timestamp values from your snapshotresponse.create_time object.
2. Build a UTC datetime object: Start with the seconds value, then add the nanoseconds converted to microseconds (Python's datetime only supports microsecond precision, which aligns with BigQuery's Timestamp capabilities).
3. Format to BigQuery's required string format: Generate a string that exactly matches YYYY-MM-DD HH:MM:SS.ffffff (the format BigQuery expects for Timestamp columns).

Modified Code Example #

First, import the necessary datetime modules:

from datetime import datetime, timedelta

Then replace your existing timestamp handling code with this cleaner, more reliable version:

row = {}
rows = []

# Pull seconds and nanos from the API response object
seconds = snapshotresponse.create_time.seconds
nanos = snapshotresponse.create_time.nanos

# Create a UTC datetime: base it on the seconds, then add microseconds (nanos // 1000 converts nanos to micros)
created_datetime = datetime.utcfromtimestamp(seconds) + timedelta(microseconds=nanos // 1000)

# Format to match BigQuery's expected Timestamp string format
createdtime = created_datetime.strftime("%Y-%m-%d %H:%M:%S.%f")

row["createdTime"] = createdtime
json_msg = { "json": row }
rows.append(json_msg)
body = { "kind": "bigquery#tableDataInsertAllRequest", "skipInvalidRows": "false", "rows": rows }

Why This Works #

• Direct datetime construction: Avoids messy JSON serialization and string replacement steps that can introduce unintended formatting errors.
• UTC alignment: datetime.utcfromtimestamp() ensures we're working in UTC, which matches how BigQuery stores Timestamps (all Timestamps are stored as UTC under the hood).
• Precision matching: Converting nanoseconds to microseconds (nanos // 1000) aligns with both Python's datetime precision and BigQuery's support for up to 6 decimal places (microseconds) in Timestamp values.
• Exact format match: The strftime call produces a string that perfectly fits BigQuery's documented Timestamp format requirements.

Alternative Formatting Option #

If you prefer using ISO 8601 format (which BigQuery also accepts), you can replace the strftime line with:

# Convert to ISO format, replace 'T' with space to match BigQuery's preferred structure
createdtime = created_datetime.isoformat().replace('T', ' ')

This will generate a string like 2020-11-05 07:48:59.418758, which is equally valid for BigQuery's Timestamp column.

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