Windows下如何通过Python调用psql执行PostgreSQL多行查询？

Let's break down why this error is happening and walk through a few practical solutions:

The Root Cause #

When you use tempfile.NamedTemporaryFile('w'), two key issues can trigger the "Permission denied" error:

1. File Locking: On systems like Windows, a file still open in Python can't be accessed by another process (like psql). Even on Unix-like systems, holding the file open might create unexpected permission blocks.
2. Strict Default Permissions: Temporary files created this way default to 0o600 permissions (only the creating user can read/write). While psql runs as the same user, the open file handle combined with these tight permissions can still block access.

Solution 1: Close the temp file before running psql #

Since NamedTemporaryFile auto-deletes files when closed by default, we'll disable that behavior and clean up manually:

import os
import tempfile

sql = 'select 1;' 
# Set delete=False so the file isn't deleted when we close it
with tempfile.NamedTemporaryFile('w', delete=False) as f:
    f.write(sql)
# Now the file is closed, so psql can access it without issues
cmd = f'psql --file "{f.name}"'
os.system(cmd)
# Manually delete the temp file to avoid leftover clutter
os.unlink(f.name)

Solution 2: Adjust file permissions while keeping it open #

If you need to keep the file open (e.g., to add more SQL later), flush the buffer and update permissions to allow read access:

import os
import tempfile
import stat

sql = 'select 1;' 
with tempfile.NamedTemporaryFile('w') as f:
    f.write(sql)
    # Flush to ensure all content is written to disk (not just Python's cache)
    f.flush()
    # Update permissions to let the same user read the file
    os.chmod(f.name, stat.S_IRUSR | stat.S_IWUSR | stat.S_IRGRP | stat.S_IROTH)
    cmd = f'psql --file "{f.name}"'
    os.system(cmd)

Solution 3: Skip the temp file entirely (recommended) #

The cleanest approach is to avoid temp files altogether by passing your SQL directly to psql via standard input. Use the subprocess module (more flexible and powerful than os.system):

import subprocess

# Works for single or multi-line SQL queries
sql = '''
select 1;
select 2;
'''

# Pass SQL directly to psql through stdin
result = subprocess.run(['psql'], input=sql, capture_output=True, text=True)

# Check output and errors for debugging
print("Query Output:", result.stdout)
print("Error Messages:", result.stderr)

For shorter queries, you can also use the -c flag:

result = subprocess.run(['psql', '-c', 'select 1;'], capture_output=True, text=True)

This method eliminates file permission issues entirely and lets you easily capture output or errors for troubleshooting.

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