使用psycopg2创建PostgreSQL表失败：无报错但无表生成

Hey there! Let's walk through why your table isn't showing up in your new PostgreSQL database—even though you’ve followed basics like committing changes. I spot a couple of key issues in your code that are almost certainly causing this:

1. SQL语法错误导致变更未提交（你没看到报错是因为没捕获异常） #

Look at your insert statement:

cursor.execute('INSERT INTO todo(id, description) VALUES (1, buy 2L milk);')

The value buy 2L milk is a string, so it needs to be wrapped in single quotes. Without them, PostgreSQL will treat buy as a column name, triggering a syntax error.

Here’s the fix:

# 用转义单引号或者外层用双引号
cursor.execute('INSERT INTO todo(id, description) VALUES (1, \'buy 2L milk\');')
# 或者更简洁的写法
cursor.execute("INSERT INTO todo(id, description) VALUES (1, 'buy 2L milk');")

The big problem here? If your code hits this syntax error without exception handling, it crashes before reaching connection.commit(). That means the CREATE TABLE change never gets saved to the database—explaining why pgAdmin shows no tables!

2. Double-check you’re connecting to the right new database #

Your connection string is psycopg2.connect('dbname=todoapp'), but make sure this todoapp is exactly the new database you’re checking in pgAdmin. Sometimes, if you have multiple PostgreSQL instances or different user/port settings, you might accidentally connect to a default database (like postgres) instead of your new one.

To confirm, add a quick check to your code:

cursor.execute('SELECT current_database();')
print(f"Connected to: {cursor.fetchone()[0]}")

If the output isn’t your target database, update your connection string with explicit parameters to avoid confusion:

connection = psycopg2.connect(
    dbname='todoapp',
    user='your_username',
    password='your_password',
    host='localhost',
    port='5432'
)

3. Fix cursor/connection closing order (minor but important for consistency) #

Your code closes the connection before the cursor—reverse that order to follow best practices:

cursor.close()
connection.close()

This isn’t the direct cause of your missing table, but it prevents weird edge cases down the line.

Quick fixed full code (with error handling) #

Here’s your code updated with all fixes plus exception handling, so you’ll always see what’s going wrong:

import psycopg2

try:
    connection = psycopg2.connect('dbname=todoapp')
    cursor = connection.cursor()
    
    # Verify connected database
    cursor.execute('SELECT current_database();')
    print(f"Connected to database: {cursor.fetchone()[0]}")
    
    # Drop existing table (if needed)
    cursor.execute('DROP TABLE IF EXISTS todo;')
    
    # Create table
    cursor.execute('''
    CREATE TABLE todo(
     id serial PRIMARY KEY,
     description varchar NOT NULL
    );
    ''')
    print("Table created successfully")
    
    # Insert data (fixed quotes)
    cursor.execute("INSERT INTO todo(id, description) VALUES (1, 'buy 2L milk');")
    print("Data inserted successfully")
    
    connection.commit()
    print("Changes saved to database")
    
except Exception as e:
    print(f"Oops, error occurred: {e}")
    # Roll back changes if something goes wrong
    if connection:
        connection.rollback()
finally:
    # Clean up resources properly
    if cursor:
        cursor.close()
    if connection:
        connection.close()
    print("Connection closed")

Extra troubleshooting steps #

• Open pgAdmin, navigate to your target database, use the Query Tool to run your CREATE TABLE and INSERT statements manually. If this works, the issue is definitely in your code.
• Make sure your PostgreSQL 12.2 service is running (check Windows Services for postgresql-x64-12).

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