连接后psycopg2找不到任何表 [英] psycopg2 cannot find any tables after connection

问题描述

我可以连接到我的数据库，但是 psycopg2 无法找到我的任何表.尝试获取我的用户时会出现以下错误:

I can connect to my database, but psycopg2 fails to find any of my tables. The following will error trying to get my users:

import psycopg2

try:
    conn = psycopg2.connect("dbname='pdb' user='postgres' host='localhost' password='password'")
except:
    print 'failed to connect'

cur = conn.cursor()
cur.execute(""" SELECT * from Users """)
rows = cur.fetchall()
for row in rows:
    print row[0]

#Error:
psycopg2.ProgrammingError: relation "users" does not exist
LINE 1: SELECT * from Users 

# This also fails
cur.execute("""SELECT * from pdb.Users """)

如果我这样做:

cur.execute(""" SELECT * from pg_database """)

# Outputs
template1
template0
postgres
pdb

在我的管理面板中，pdb 显示了一堆表，其中一个是 Users，所以我不确定为什么 psycopg2 找不到它.

In my admin panel, pdb shows a bunch of tables, one of them being Users, so I'm not sure why psycopg2 can't find it.

这是 pdb 的 psql 打印输出:

Here's a printout from psql for pdb:

               List of relations
 Schema |        Name        | Type  |  Owner   
--------+--------------------+-------+----------
 public | Companies          | table | postgres
 public | Users              | table | postgres
(2 rows)

推荐答案

您的表名 Users 和 Companies 都以大写字母开头.PostgreSQL 会将所有标识符转换为小写(默认情况下)，正如您从错误消息中看到的:

Your table names Users and Companies both start with a capitalized letter. PostgreSQL will convert all identifiers to lower case (by default), as you can see from the error message:

psycopg2.ProgrammingError: relation "users" does not exist

users 全部小写.如果您希望严格遵循 SQL 标准(正如 PostgreSQL 所著名的那样)，则需要这样做.您可以通过两种方式解决此问题:

Where users is written in all lower case. This is needed if you wish to strictly follow the SQL standard (as PostgreSQL is renowned for). You can solve this in two ways:

在您的数据库中解决它:

Solve it in your database:

遵守通用约定并将表格重命名为全部小写.

Adhere to a common convention and rename your tables to be all lowercase.

在您的代码中解决它:

引用您的标识符(在本例中为您的表名)，以便 PostgreSQL 将它们保持不变:

Quote your identifiers (your table name in this case) so PostgreSQL will leave them untouched:

cur.execute(""" SELECT * from "Users" """)

这篇关于连接后psycopg2找不到任何表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！