尝试删除表中的数据时，Python支持的SQL数据库返回psycopg2.ProgrammingError：关系不存在错误？ [英] Python backed SQL Database returning a psycopg2.ProgrammingError: relation does not exist error when attempting to delete the data within a table?

问题描述

简介：我一直在使用Vagrant为Udacity的关系数据库课程运行Ubuntu VM来构建瑞士风格比赛的Python支持的PostgreSQL数据库。

问题：为什么当我尝试删除现有表中的信息时该表不存在？

我进行了常规搜索以进行研究

  psycopg2.ProgrammingError 
  

，但是该信息主要针对其他情况，因此我无法进行逻辑连接我解决这个问题。我还发现了另一个与同一问题接近的堆栈溢出线程此处其他线程，但是该问题也从未得到解决。因此，我创建了这个问题，试图找到能够理解我的难题并提供某种提示或线索的人，以使我朝正确的方向工作。

将锦标赛数据库SQL文件导入Vagrant VM。这将删除两个表（玩家和比赛）和数据库（锦标赛），然后重新创建该数据库和这些表。所以我知道表（匹配项）存在。但是，这就是我得到的...

无业游民：（当前无业游民的错误）

  vagrant @ vagrant：/ vagrant / tournament $ python tour_test.py 
回溯（最近一次调用）：
文件 tournament_test.py，第151行，在< module>中; 
 testCount（）
文件 tournament_test.py，第17行，在testCount 
 deleteMatches（）
文件 /vagrant/tournament/tournament.py，第18行，在deleteMatches 
 cursor.execute（ DELETE FROM matchs;）
 psycopg2.ProgrammingError：关系 matches不存在
 LINE 1：删除FROM匹配项； 
 ^ 
  

SQL ：（全部来自Tournament.sql）

 -锦标赛项目的表定义。 
-
-将SQL创建表语句放入此文件中；也可以创建视图 
 －如果您选择使用该语句。 
-
-您可以在文件中以两个破折号（
像
）开头来注释，这些行在此处。 
 DROP TABLE匹配项； 
 DROP TABLE玩家； 
 DROP DATABASE锦标赛； 
 CREATE DATABASE比赛； 
 
 
创建表的玩家（player_id SERIAL UNIQUE PRIMARY KEY，名称为
 VARCHAR（40））； 
 CREATE TABLE比赛（match_id串行唯一主键，
赢家INTEGER REFERENCES玩家（player_id），
输家INTEGER REFERENCES玩家（player_id））； 
  

Python ：（从tour.py中最小）

 ＃！/ usr / bin / env python 
＃
＃tour.py-瑞士系统锦标赛的实现
＃
 
 import psycopg2 
 
 
 def connect（）：
连接到PostgreSQL数据库。返回数据库
连接。 
 return psycopg2.connect（ dbname = tournament）
 
 
 def deleteMatches（）：
从数据库中删除所有匹配记录。  
 db = connect（）
游标= db.cursor（）
 cursor.execute（ TRUNCATE匹配CASCADE;）
 db.commit（）
 db .close（）
  

Python ：（从tour_test.py中最小）这是验证锦标赛的文件.py函数正在使用Tournament.sql数据库。

 ＃！/ usr / bin / env python 
＃ 
＃锦标赛的测试用例。py
 
从锦标赛导入* 
 
 def testCount（）：
  
测试初始玩家数，注册1和2个玩家后，
玩家数，删除玩家后的
玩家数。 
 
 deleteMatches（）
 deletePlayers（）
c = countPlayers（）
如果c =='0'：
引发TypeError（
 countPlayers应该返回数字零，而不是字符串'0'。）
如果c！= 0：
提高ValueError（删除后，countPlayers应该返回零。）
打印 1。在最初执行deletePlayers（）
之后，countPlayers（）返回0。 
 registerPlayer（ Chandra Nalaar）
c =如果c！= 1，则$ Played（）
 = 1：
提高ValueError（
一个玩家注册后，countPlayers（）应该为1。得到
 {c}。format（c = c））
打印 2。注册一个玩家后，countPlayers（）返回1。 
 registerPlayer（ Jace Beleren）
c = countPlayers（）
如果c！= 2：
提高ValueError（
两个玩家注册后，countPlayers（）应该为2。得到
 {c}。format（c = c））
打印 3。两个玩家注册后，countPlayers（）返回2。 
 deletePlayers（）
c = countPlayers（）
如果c！= 0：
引发ValueError（
之后删除，countPlayers应该返回零。）
打印 4。删除注册玩家
后，countPlayers（）返回零。n5。玩家记录已成功删除。 
 
 testCount（）
  

Vagrant（确认表'matches'存在于数据库中）：

  vagrant => \dt 
关系列表
架构|名称|类型|所有者
 -------- + --------- + ------- + --------- 
公共|比赛|表|无业游民
公共|球员|表|无业游民
（2行）
  

Update2：澄清评论

我在python文件的开头连接到数据库。

  def connect（）
连接到PostgreSQL数据库。返回数据库
连接。 
返回psycopg2.connect（ dbname = tournament）
  

解决方案

这是用户错误。我以错误的方式连接到无业游民和锦标赛数据库。

错误：

在无业游民中，我以无业游民身份进入psql并导入了文件。

  \i Tournament.sql 
  

然后我连接到数据库。

  \c比赛
  

然后我要退出psql来运行文件并

我需要再做一步。

修复：

p>

连接并登录数据库锦标赛后，我需要再次导入Tournament.sql文件。

日实际数据库中的关系，而不仅仅是流浪汉或以前在任何地方创建的关系。

因此在命令Vagrant ssh
之后是从Vagrant获得的＃b $ b cd / vagrant / tournament /

  psql 
 
 \i锦标赛.sql 
 
 \c锦标赛
 
 \i锦标赛
 
＃最后一次检查以确认您的关系已建立
 \dt 
 \d（表或视图）
  

这就是我所做的。该项目的其余部分很容易。我希望这可以帮助任何人在此处搜索答案，并且仅找到更多未回答但遭到批评的问题。感谢所有看到我的初学者问题并给我否定观点或停止批评我的问题形式的专家。我仍在学习，如果你们中的任何一个确实帮助我了解或解决了这个问题，那么我将无法在3天的时间内独自完成所有工作。

Intro: I have been working on building a Python backed PostgreSQL database of a Swiss style tournament using Vagrant running a Ubuntu VM for my Relational Database course in Udacity.

Question: Why does it say the table does not exist when I attempt to delete the information within the existing table?

I did a generic search to research

psycopg2.ProgrammingError

, but the information is mostly specific to other circumstances, and I was unable to make logical connections that would allow me fix this problem. I also found another Stack Overflow thread that is close to the same issue other thread here, however that issue was also never resolved. So I have created this question attempting to find someone capable of understanding my conundrum and provide some kind of hint or clue to get me working in the right direction.

I have imported the tournament database SQL file into the Vagrant VM. This deleted the two tables (players and matches) and the database (tournament) and then recreates this database and these tables. So I know the table (matches) exists. However, this is what I am getting...

Vagrant: (current vagrant error)

vagrant@vagrant:/vagrant/tournament$ python tournament_test.py
Traceback (most recent call last):
  File "tournament_test.py", line 151, in <module>
    testCount()
  File "tournament_test.py", line 17, in testCount
    deleteMatches()
  File "/vagrant/tournament/tournament.py", line 18, in deleteMatches
    cursor.execute("DELETE FROM matches;")
psycopg2.ProgrammingError: relation "matches" does not exist
LINE 1: DELETE FROM matches;
                    ^

SQL: (All from tournament.sql)

-- Table definitions for the tournament project.
--
-- Put your SQL 'create table' statements in this file; also 'create view'
-- statements if you choose to use it.
--
-- You can write comments in this file by starting them with two dashes, 
like
-- these lines here.
DROP TABLE matches;
DROP TABLE players;
DROP DATABASE tournament;
CREATE DATABASE tournament;


CREATE TABLE players (player_id SERIAL UNIQUE PRIMARY KEY, name 
VARCHAR(40));
CREATE TABLE matches (match_id SERIAL UNIQUE PRIMARY KEY,
                      winner INTEGER REFERENCES players(player_id),
                      loser INTEGER REFERENCES players (player_id));

Python: (Minimal from tournament.py)

#!/usr/bin/env python
#
# tournament.py -- implementation of a Swiss-system tournament
#

import psycopg2


def connect():
    """Connect to the PostgreSQL database.  Returns a database 
connection."""
    return psycopg2.connect("dbname=tournament")


def deleteMatches():
    """Remove all the match records from the database."""
    db = connect()
    cursor = db.cursor()
    cursor.execute("TRUNCATE matches CASCADE;")
    db.commit()
    db.close()

Python: (minimal from tournament_test.py) this is the file that verifies the tournament.py functions are working with the tournament.sql database.

#!/usr/bin/env python
#
# Test cases for tournament.py

from tournament import *

def testCount():
    """
    Test for initial player count,
             player count after 1 and 2 players registered,
             player count after players deleted.
    """
    deleteMatches()
    deletePlayers()
    c = countPlayers()
    if c == '0':
        raise TypeError(
            "countPlayers should return numeric zero, not string '0'.")
    if c != 0:
        raise ValueError("After deletion, countPlayers should return zero.")
    print "1. countPlayers() returns 0 after initial deletePlayers() 
execution."
    registerPlayer("Chandra Nalaar")
    c = countPlayers()
    if c != 1:
        raise ValueError(
            "After one player registers, countPlayers() should be 1. Got 
{c}".format(c=c))
    print "2. countPlayers() returns 1 after one player is registered."
    registerPlayer("Jace Beleren")
    c = countPlayers()
    if c != 2:
        raise ValueError(
            "After two players register, countPlayers() should be 2. Got 
{c}".format(c=c))
    print "3. countPlayers() returns 2 after two players are registered."
    deletePlayers()
    c = countPlayers()
    if c != 0:
        raise ValueError(
            "After deletion, countPlayers should return zero.")
    print "4. countPlayers() returns zero after registered players are 
deleted.\n5. Player records successfully deleted."

testCount()

Vagrant (Confirming that the table 'matches' exists within the database):

vagrant=> \dt
         List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+---------
 public | matches | table | vagrant
 public | players | table | vagrant
(2 rows)

Update2: Clarification to comment

I connect to the db at the beginning of my python file.

def connect()
    """Connect to the PostgreSQL database.  Returns a database 
    connection."""
    return psycopg2.connect("dbname=tournament")

解决方案

This was user error. I was connecting to vagrant and the tournament database in the wrong way.

After logging into vagrant I was in the right folder accessing the right database but in the wrong method.

Error:

Once in vagrant I went to psql as user vagrant and imported the file.

\i tournament.sql

Then I connected to the database.

\c tournament

Then I was exiting psql to run the file and getting the relation does not exist error.

I needed to do one more step.

FIX:

Once connected and logged into the database tournament. I needed to import the tournament.sql file again.

That created the relations within the actual database and not just vagrant or wherever I was creating them before.

so from Vagrant after the command Vagrant ssh # run these commands separately cd /vagrant/tournament/

psql

\i tournament.sql

\c tournament

\i tournament

#last check to verify your relations were created
\dt
\d (table or view)

That is what did it for me. The rest of the project was easy. I hope this helps anyone searching for the answer on here and only finding more unanswered but heavily criticized questions. Thanks to all the experts who saw my beginner question and either gave me negative points or stopped to criticize the format of my question. I am still learning and if any of you had actually helped me understand or solve this I wouldn't have been able to do it all by myself over the course of 3 days.

这篇关于尝试删除表中的数据时，Python支持的SQL数据库返回psycopg2.ProgrammingError：关系不存在错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！