postgresql 连接后psycopg2找不到任何表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27434633/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
psycopg2 cannot find any tables after connection
提问by user21398
I can connect to my database, but psycopg2 fails to find any of my tables. The following will error trying to get my users:
我可以连接到我的数据库，但 psycopg2 无法找到我的任何表。尝试获取我的用户时会出现以下错误：
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 """)
If I do:
如果我做：
cur.execute(""" SELECT * from pg_database """)
# Outputs
template1
template0
postgres
pdb
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 显示了一堆表，其中一个是Users，所以我不确定为什么 psycopg2 找不到它。
Here's a printout from psql for pdb:
这是 psql 的打印输出pdb：
List of relations
Schema | Name | Type | Owner
--------+--------------------+-------+----------
public | Companies | table | postgres
public | Users | table | postgres
(2 rows)
回答by Timusan
Your table names Usersand Companiesboth start with a capitalized letter. PostgreSQL will convert all identifiers to lower case (by default), as you can see from the error message:
您的表名Users和Companies都以大写字母开头。PostgreSQL 会将所有标识符转换为小写（默认情况下），正如您从错误消息中看到的：
psycopg2.ProgrammingError: relation "users" does not exist
Where usersis 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:
whereusers全小写。如果您希望严格遵循 SQL 标准（正如 PostgreSQL 所著名的那样），则需要这样做。您可以通过两种方式解决此问题：
Solve it in your database:
在您的数据库中解决它：
Adhere to a common convention and rename your tablesto be all lowercase.
遵守通用约定并将您的表重命名为全部小写。
Solve it in your code:
在您的代码中解决它：
Quote your identifiers (your table name in this case) so PostgreSQL will leave them untouched:
引用您的标识符（在这种情况下是您的表名），以便 PostgreSQL 将保持它们不变：
cur.execute(""" SELECT * from "Users" """)
回答by Mohd Shoaib Sayyed
This issue also usually happens when you are connected to wrong database. In my case i was connected to wrong database Below is my code to connect using psycopg2 library
当您连接到错误的数据库时，通常也会发生此问题。在我的情况下，我连接到错误的数据库下面是我使用 psycopg2 库连接的代码
import psycopg2
try:
src_conn = psycopg2.connect("dbname={} user={} host=localhost password={}".format(self.src_db, self.src_db, self.src_url ,self.src_pwd))
if src_conn:
src_curr = src_conn.cursor()
print("Cursor found",src_curr)
src_curr.execute("select * from product_template")
recs = src_curr.fetchall()
print(recs)
else:
print("Cursor not found")
except Exception as ex:
print(ex)
