Psycopg2 errors undefinedtable ошибка отношение не существует

I’m trying to figure out why I can’t access a particular table in a PostgreSQL database using psycopg2. I am running PostgreSQL 11.5

If I do this, I can connect to the database in question and read all the tables in it:

import psycopg2

try:
connection = psycopg2.connect(user = "postgres",                #psycopg2.connect() creates connection to PostgreSQL database instance
                              password = "battlebot",
                              host = "127.0.0.1",
                              port = "5432",
                              database = "BRE_2019")

cursor = connection.cursor()                                #creates a cursor object which allows us to execute PostgreSQL commands through python source

#Print PostgreSQL version
cursor.execute("""SELECT table_name FROM information_schema.tables
   WHERE table_schema = 'public'""")

for table in cursor.fetchall():
    print(table)

The results look like this :

('geography_columns',)
('geometry_columns',)
('spatial_ref_sys',)
('raster_columns',)
('raster_overviews',)
('nc_avery_parcels_poly',)
('Zone5e',)
('AllResidential2019',)
#....etc....

The table I am interested in is the last one, ‘AllResidential2019’

So I try to connect to it and print the contents by doing the following:

try:
    connection = psycopg2.connect(user = "postgres",                
    #psycopg2.connect() creates connection to PostgreSQL database instance
                              password = "battlebot",
                              host = "127.0.0.1",
                              port = "5432",
                              database = "BRE_2019")

    cursor = connection.cursor()                                #creates a cursor object which allows us to execute PostgreSQL commands through python source

    cursor.execute("SELECT * FROM AllResidential2019;")           #Executes a database operation or query. Execute method takes SQL query as a parameter. Returns list of result
    record = cursor.fetchall()

    print(record)


except (Exception, psycopg2.Error) as error:
    print("Error while connecting to PostgreSQL: ", error)

And I get the following error:

Error while connecting to PostgreSQL:  relation "allresidential2019" does not exist
LINE 1: SELECT * FROM AllResidential2019;

However, I can successfully connect and get results when attempting to connect to another table in another database I have (this works! and the results are the data in this table):

try:
    connection = psycopg2.connect(user = "postgres",                #psycopg2.connect() creates connection to PostgreSQL database instance
                              password = "battlebot",
                              host = "127.0.0.1",
                              port = "5432",
                              database = "ClimbingWeatherApp") .  #different database name

    cursor = connection.cursor()                                


    cursor.execute("SELECT * FROM climbing_area_info ;")          
    record = cursor.fetchall()

    print(record)


except (Exception, psycopg2.Error) as error:
    print("Error while connecting to PostgreSQL: ", error)

I can’t figure out why I can retrieve information from one table but not another, using exactly the same code (except names are changes). And I am also not sure how to troubleshoot this. Can anyone offer suggestions?

Comments

* Update Makefile with Dev1 commands from ECR repo
* Create Dev1 build GitHub Action
* Add a PR template
* update the .gitignore to ignore .DS_Store files

This also includes a fix for a problem introduced by a newer version of
the psycopg2-binary package. There was a change introduced after 2.8.6
that impacted how this app loaded data into tables in the PostGIS
database. For now, instead of trying to fix the code, I just restricted
the version of the psycopg2-binary to 2.8.6 or earlier.

 See
* psycopg/psycopg2#1294
and
* psycopg/psycopg2#1383
for more details.

* feat: try to upgrade to airflow 2.4
* fix: install package by pip because python-poetry/poetry#1214
* refactor: update docker images
* refactor: update aws connections
* feat: add XCOM_WORK_KEY_PREFIX constant
* fix: copy_from don't work with schema.table for psycopg > 2.9 psycopg/psycopg2#1294
* refactor: use DagRunState class instead of str
* fix: use right TaskInstance
* feat: use new Xcom logic
* refactor: use schedule instead of schedule_interval
* refactor: remove create dagrun from fixture
* feat: add create_dag_run to dag_generator tests
* feat: add create_dag_run to operators tests
* feat: updata pandas to 1.5.0
* fix: size of empty DataFrame changed
* fix: ports in docker-compose after review
* fix: down version to 2.1.0

Co-authored-by: Ivan Kanashov <i.kanashov@tinkoff.ru>

Issue

I tried to start using Postgresql instead of sqlite in my Django project.
I installed postgreqL ON MY Windows, creatred a new database, user and password.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'database_name',
        'USER': 'admin',
        'PASSWORD': 'admin',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

But when I try to migrate or makemigrations, I got this:

File
«C:Userss…venvlibsite-packagesdjangodbbackendsutils.py»,
line 85, in _execute
return self.cursor.execute(sql, params) psycopg2.errors.UndefinedTable: relation «authentication_author» does
not exist LINE 1: …hentication_author».»is_doctor» FROM
«authentic…

here is my model:

class Author(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE, null=True, related_name="author")
    slug = models.CharField(max_length=50, null=True, blank=True,)
    is_doctor = models.BooleanField(default=False)

And yes, I deleted the sqlite3 database, all the migrations folders and I created new ones with the init.py inside of them.

But still get the same problem.

Updated
Traceback screenshots:

Solution

It happens with Django. Sometimes you can invoke some code that relies on a new DB schema at the time you’re trying to makemigrations.

All you need in this situation is to temporarily comment out all the code that connects makemigrations with your new model’s schema. As it was in this question, you can trace related blocks of code just using full traceback.

Answered By — Yevgeniy Kosmak

To get it to work I reworked the query as:

UPDATED. Added WHERE clause.

UPDATE
    mytable
SET
    mycolumn = a.mycolumn::boolean
FROM
    mytable AS t
    INNER JOIN (
        VALUES (28625, '1'),
            (56614, '1'),
            (86517, '1')) AS a (id, mycolumn) ON a.id = t.id
    WHERE
        a.id = mytable.id
;

When I tried your original query I got:

ERROR: table name "t" specified more than once

When I tried my comment suggestion I got:

ERROR: column reference "id" is ambiguous

The docs from here UPDATE are somewhat confusing:

alias

A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given UPDATE foo AS f, the remainder of the UPDATE statement must refer to this table as f not foo.

from_item

A table expression allowing columns from other tables to appear in the WHERE condition and update expressions. This uses the same syntax as the FROM clause of a SELECT statement; for example, an alias for the table name can be specified. Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).

But given the error messages I figured the UPDATE portion needed the actual table name and the FROM needed the aliased name.