使用movie_loader.py向PostgreSQL导入数据时遇编码错误求助

问题场景 #

使用movie_loader.py生成SQL语句并通过管道导入PostgreSQL，执行命令：

python3 movie_loader.py | psql postgresql://localhost/postgres

触发以下错误：

stdin is not a tty
Traceback (most recent call last):
  File "C:\Users\dhuan\relational\movie_loader.py", line 28, in <module>
    print(f'INSERT INTO movie VALUES({id}, {year}, \'{title}\');')
OSError: [Errno 22] Invalid argument
Exception ignored in: <_io.TextIOWrapper name='<stdout>' mode='w' encoding='cp1252'>
OSError: [Errno 22] Invalid argument

涉及的脚本内容：

import csv

"""
This program generates direct SQL statements from the source Netflix Prize files in order
to populate a relational database with those files’ data.

By taking the approach of emitting SQL statements directly, we bypass the need to import
some kind of database library for the loading process, instead passing the statements
directly into a database command line utility such as `psql`.
"""

# The INSERT approach is best used with a transaction. An introductory definition:
# instead of “saving” (committing) after every statement, a transaction waits on a
# commit until we issue the `COMMIT` command.
print('BEGIN;')

# For simplicity, we assume that the program runs where the files are located.
MOVIE_SOURCE = 'movie_titles.csv'
with open(MOVIE_SOURCE, 'r+', encoding='iso-8859-1') as f:
    reader = csv.reader(f)
    for row in reader:
        id = row[0]
        year = 'null' if row[1] == 'NULL' else int(row[1])
        title = ', '.join(row[2:])

        # Watch out---titles might have apostrophes!
        title = title.replace("'", "''")
        print(f'INSERT INTO movie VALUES({id}, {year}, \'{title}\');')
        sys.stdout.reconfigure(encoding='UTF08')

# We wrap up by emitting an SQL statement that will update the database’s movie ID
# counter based on the largest one that has been loaded so far.
print('SELECT setval(\'movie_id_seq\', (SELECT MAX(id) from movie));')

# _Now_ we can commit our transation.
print('COMMIT;')

解决思路 #

1. 修正stdout编码配置的错误 #

• 脚本未导入sys模块，需在开头添加import sys。
• sys.stdout.reconfigure(encoding='UTF08')存在拼写错误（应为UTF-8），且不应放在循环内频繁调用——每次print后修改编码会导致输出流状态混乱，应在脚本开头一次性设置：

import csv
import sys

# 提前设置标准输出编码为UTF-8
sys.stdout.reconfigure(encoding='utf-8')

print('BEGIN;')

2. 解决Windows+Git Bash的编码冲突 #

Git Bash在Windows环境下的管道输出编码与Python默认stdout编码不兼容，是触发Invalid argument的核心原因之一，可通过两种方式规避：

• 执行命令前设置Python输出编码环境变量：

  PYTHONIOENCODING=utf-8 python3 movie_loader.py | psql postgresql://localhost/postgres
  

• 或在脚本开头强制重定向stdout，禁用缓冲：

  import sys
  sys.stdout = open(sys.stdout.fileno(), mode='w', encoding='utf-8', buffering=1)
  

3. 改用PostgreSQL COPY命令替代逐行INSERT #

逐行生成INSERT语句效率低且易触发编码/特殊字符问题，改用COPY是更优方案：

• 直接通过psql命令导入CSV，无需Python脚本中转：

  psql postgresql://localhost/postgres -c "BEGIN; \copy movie(id, year, title) FROM 'movie_titles.csv' WITH (FORMAT csv, ENCODING 'iso-8859-1', NULL 'NULL'); SELECT setval('movie_id_seq', (SELECT MAX(id) from movie)); COMMIT;"
  

• 若需处理标题中的逗号（CSV默认分隔符），可提前修改CSV的分隔符，或在Python脚本中用csv.writer生成符合COPY要求的标准CSV输出。

4. 定位出错的具体数据行 #

临时添加错误捕获，在stderr中输出触发错误的行信息，方便排查特殊字符问题：

try:
    print(f'INSERT INTO movie VALUES({id}, {year}, \'{title}\');')
except OSError as e:
    print(f'Error on row {id}: {title}', file=sys.stderr)
    raise

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