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?

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.

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

Answer by Jaxtyn Camacho

one advice
add this line at the top of your Procfile.,

Unpinning the accepted answer from the top of the list of answers

,Please be sure to answer the question. Provide details and share your research!,

Taking the bayonet to its logical conclusion. Or, the pike-rifle

one advice
add this line at the top of your Procfile.

release: python manage.py migrate

Answer by Raymond Macdonald

Common causes of this error in Heroku Postgres databases are the following:,If you have created a new credential for your database via the Heroku CLI, you’ll need to configure the appropriate permissions for this credential. The instructions to achieve this are described at Heroku PostgreSQL credentials: managing permissions.,
If you have created a new credential for your database via the Heroku CLI, you’ll need to configure the appropriate permissions for this credential. The instructions to achieve this are described at Heroku PostgreSQL credentials: managing permissions.
,Heroku Postgres Hobby tier databases have row limits enforced. When you are over your row limit and attempt to insert data you will see this error. Upgrade to a production tier database to remove this constraint or reduce the number of total rows.

Resolution

The permission denied for relation error is a general PostgreSQL error meaning that the user that’s connected to the database doesn’t have access to write or read a specific table.

permission denied for relation

Answer by Bowen Sheppard

Logs are a production-tier feature. They are not available on hobby-tier databases.,This occurs when you have de-provisioned a hobby tier database but are still trying to connect to it. To resolve:,

Java Database Operations
,PGError: relation “table-name” does not exist

$ heroku logs -p postgres -t
2012-11-01T17:41:42+00:00 app[postgres]: [15521-1]  [CHARCOAL] LOG:  checkpoint starting: time
2012-11-01T17:41:43+00:00 app[postgres]: [15522-1]  [CHARCOAL] LOG:  checkpoint complete: wrote 6 buffers (0.0%); 0 transaction log file(s) added, 0 rem...

LOG: duration: 3.565 s …

[12-1] u8akd9ajka [BRONZE] LOG:  duration: 3.847 s  statement: SELECT  "articles".* FROM "articles"...

LOG: checkpoint starting…

2012-11-01T17:41:42+00:00 app[postgres]: [15521-1]  [CHARCOAL] LOG:  checkpoint starting: time
2012-11-01T17:41:43+00:00 app[postgres]: [15522-1]  [CHARCOAL] LOG:  checkpoint complete: wrote 6 buffers (0.0%); 0 transaction log file(s) added, 0 rem...

LOG: unexpected EOF on client connection

app[postgres]: LOG:  could not receive data from client: Connection reset by peer
app[postgres]: LOG:  unexpected EOF on client connection
heroku[router]: at=error code=H13 desc="Connection closed without response" method=GET path=/crash host=pgeof.herokuapp.com dyno=web.1 connect=1ms service=10ms status=503 bytes=0
heroku[web.1]: Process exited with status 1
heroku[web.1]: State changed from up to crashed

FATAL: too many connections for role

FATAL:  too many connections for role "[role name]"

FATAL: could not receive data …

FATAL: could not receive data from WAL stream: SSL error: sslv3 alert unexpected message

You can find out the current number of commits a follower is behind by using heroku pg:info. Each follower has a “Behind By” entry that indicates how many commits the follower is behind its master.

$ heroku pg:info --app sushi
=== HEROKU_POSTGRESQL_WHITE
...
Following    HEROKU_POSTGRESQL_LAVENDER (DATABASE_URL)
Behind By    125 commits

FATAL: role “role-name»…

FATAL: role "u8akd9ajka" is not permitted to log in (PG::Error)

FATAL: terminating connection due to administrator command

FATAL: terminating connection due to administrator command

FATAL: remaining connection slots are reserved for non-replication superuser connections

FATAL: remaining connection slots are reserved for non-replication superuser connections

temporary file: path “file path”, size “file size”

temporary file: path "base/pgsql_tmp/pgsql_tmp23058.672", size 1073741824

PGError: permission denied for relation

PGError: ERROR:  permission denied for relation table-name

PGError: operator does not exist

PGError: ERROR:  operator does not exist: character varying = integer

PGError: relation “table-name” does not exist

PGError: ERROR: relation "documents" does not exist

PGError: column “column-name” cannot…

PGError: ERROR: column "verified_at" cannot be cast to type "date"

Prepared statements, in and of themselves, are not a bad mechanism to use when working with Postgres. The benefit of using them is that the database can execute the statement with extremely high efficiency. Unfortunately, many ORMs as part of popular web frameworks, namely ActiveRecord in the Ruby on Rails framework, do not construct prepared statements effectively. If a query that is constructed can contain any number of parameters, multiple prepared statements will be created for what is the same logical query. For example, let’s assume that our application allows its users to select products from a product table by id. If customer one selects two products, ActiveRecord would define a query and create a prepared statement:

SELECT * FROM products WHERE id in ($1, $2);

If customer two, in the same application, for the same query, decides to select four products instead of two, a new prepared statement would be created:

SELECT * FROM products WHERE id in ($1, $2, $3, $4);

To disable prepared statements, update your config/database.yml file to include the prepared_statements parameter:

production:
  adapter: postgresql
  prepared_statements: false

To adjust the size of the prepared statement cache, update your config/database.yml file to include the statement_limit parameter (defaults to 1000).

production:
  adapter: postgresql
  statement_limit: 200

Disabling prepared statements in Rails 3 requires the use of initializers. Create a file called config/initializers/disable_prepared_statements.rb. Within the file, disable prepared statements by updating the connection options:

ActiveRecord::Base.establish_connection(
  ActiveRecord::Base.remove_connection.merge(
    :prepared_statements => false
  )
)

Answer by Belle Moore

Hello, I wanted to ask you for your help. First of all I would like to say that I googled this error and I run both «migrate» and «makemigrations» locally. I also made sure that my «migrations» folder is on heroku server and that it is not empty.,I know that this errors are caused by a query that I have in my «forms.py»:,But here I don’t know how to fix it. It seems that migratewill always try to «trigger» forms.pybefore it has a chance to «build DB stuff» so it will throw this error.,My problem is this error:

I know that this errors are caused by a query that I have in my «forms.py»:

# This one DOESN'T trigger error:
makes = Car.objects.values('make').annotate(count=Count('make')).order_by('count').distinct().reverse()

# This one DOES:
for make in makes:
    make_choices.append((make["make"], f'{make["make"]} ({Car.objects.filter(make=make["make"]).count()})'))

Additional code around problematic part in forms.py:

from django import forms
from django.db.models import Count

from prices_tool.models import Car


class FreeSearchCarForm(forms.Form):
    make_choices = [('', '--- select make ---')]
    makes = Car.objects.values('make').annotate(count=Count('make')).order_by('count').distinct().reverse()

    for make in makes:
        make_choices.append((make["make"], f'{make["make"]} ({Car.objects.filter(make=make["make"]).count()})'))

    make = forms.ChoiceField(choices=make_choices)
    make.widget.attrs.update({'class': 'form-select'})
    state_choices = [('Used', 'Used'), ('New', 'New'), ('both', 'Both')]
    state = forms.ChoiceField(choices=state_choices, widget=forms.RadioSelect, initial='Used')
    state.widget.attrs.update({'class': 'form-horizontal', 'type': 'radio'})
    models = Car.objects.values('model').annotate(count=Count('model')).order_by('count').distinct().reverse()
    model_choices = [('', '<-- please select make <--')]

    for model in models:
        model_choices.append((model["model"], f'{model["model"]} ({Car.objects.filter(model=model["model"]).count()})'))

    model = forms.ChoiceField(choices=model_choices)
    model.widget.attrs.update({'class': 'form-select'})

Answer by Declan McLaughlin

You can use the below sample for the deployment.,Before we start, create a database in PostgreSQL, give the name, no further action needed.,The deployment might be various according to the local setting.,Create below 6 files that are needed for the application

Before we start, create a database in PostgreSQL, give the name, no further action needed.

create databse local_db_name;

1. models.py which defines the PostgreSQL database, table, and the configuration

import osfrom sqlalchemy import Column, String, Integer, create_enginefrom flask_sqlalchemy import SQLAlchemydb = SQLAlchemy()'''setup_db(app):    binds a flask application and a SQLAlchemy service'''def setup_db(app):    database_name ='local_db_name'    default_database_path= "postgres://{}:{}@{}/{}".format('postgres', 'password', 'localhost:5432', database_name)    database_path = os.getenv('DATABASE_URL', default_database_path)    app.config["SQLALCHEMY_DATABASE_URI"] = database_path    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False    db.app = app    db.init_app(app)'''    drops the database tables and starts fresh    can be used to initialize a clean database'''def db_drop_and_create_all():    db.drop_all()    db.create_all()class Movie(db.Model):    __tablename__ = 'movies'    id = Column(Integer, primary_key=True)    title = Column(String(80), unique=True)    release_date = Column(db.DateTime)    def __init__(self, title, release_date):        self.title = title        self.release_date = release_date    def details(self):        return {            'id': self.id,            'title': self.title,            'release_date': self.release_date,        }    def insert(self):        db.session.add(self)        db.session.commit()    def delete(self):        db.session.delete(self)        db.session.commit()    def update(self):        db.session.commit()

2. app.py: the main application

import osfrom flask import Flask, request, abort, jsonifyfrom flask_cors import CORSfrom models import setup_db, Movie, db_drop_and_create_alldef create_app(test_config=None):    # create and configure the app    app = Flask(__name__)    setup_db(app)    CORS(app)    """ uncomment at the first time running the app """    db_drop_and_create_all()    @app.route('/', methods=['GET'])    def home():        return jsonify({'message': 'Hello,hello, World!'})    @app.route("/movies")    def get_movies():        try:            movies = Movie.query.order_by(Movie.release_date).all()            movie=[]            movie=[mov.release_date for mov in movies]            return jsonify(                {                    "success": True,                    "movie name": movie                }            ), 200        except:            abort(500)    @app.errorhandler(500)    def server_error(error):        return jsonify({            "success": False,            "error": 500,            "message": "server error"        }), 500    return appapp = create_app()if __name__ == '__main__':    port = int(os.environ.get("PORT",5000))    app.run(host='127.0.0.1',port=port,debug=True)

3. manage.py: which can manage the database schema and changes that you make to it. With this, Heroku can run the migrations to the database hosted on the platform.

from flask_script import Managerfrom flask_migrate import Migrate, MigrateCommandfrom app import appfrom models import dbmigrate = Migrate(app, db)manager = Manager(app)manager.add_command('db', MigrateCommand)if __name__ == '__main__':    manager.run()

4. Procfile file: specifies the commands that are executed by the app on startup.

web: gunicorn app:app

5. requirements.txt file to include all of the dependencies.

angles==2.0certifi==2020.4.5.1chardet==3.0.4Flask==1.1.2Flask-Cors==3.0.8Flask-Migrate==2.5.3Flask-Script==2.0.6Flask-SQLAlchemy==2.4.1Jinja2==2.11.2jose==1.0.0Mako==1.1.2MarkupSafe==1.1.1psycopg2-binary==2.8.5pyasn1==0.4.8python-dateutil==2.8.1python-editor==1.0.4python-jose==3.1.0requests==2.23.0rsa==4.0six==1.14.0SQLAlchemy==1.3.16urllib3==1.25.9Werkzeug==1.0.1xacro==1.13.3gunicorn==20.0.4boto==2.49.0botocore==1.16.5

If the dependency not being installed yet, use the below to install locally. This is for the local run. For deploying in Heroku, Heroku will search remotely, not from your local end.

pip install -r requirements.txt

6. And we also need to create setup.sh file to save the environmental variable (DATABASE_URL), currently add one line and let it empty.

export DATABASE_URL = ''

For migration, run the below commands:

python3 manage.py db initpython3 manage.py db migratepython3 manage.py db upgrade

If the version is empty, there will have an error during running git push command

manage.run()......FileNotFoundError: [Errno 2] No such file or directory: '/app/migrations/versions'

1. start to run Heroku commands

heroku login

2. Create Heroku app with the name casting-agency-xw

heroku create casting-agency-xw

3. Create a PostgreSQL database in Heroku: the only parameter you need to give is the application name. The database will be generated automatically with this command.

heroku addons:create heroku-postgresql:hobby-dev --app casting-agency-xw

4. Check the config

heroku config --app casting-agency-xw

You will find the DATABASE_URL as below:

=== casting-agency-xw Config Vars                                                                                                                                           DATABASE_URL: postgres://boozofewcboexi:fXXXXXX

Come back to the application setup.sh file, copy the DATABASE_URL from the previous step into setup.sh file as below

export DATABASE_URL='postgres://boozofewcboexi:fXXXXXX'

You can come to the below page in Heroku-deploy, using Heroku Git, as below codes, which is a bit different from the process from the website.

git initheroku git:clone -a casting-agency-xwgit add . git commit -am "add new files"git push heroku master

Run below command:

heroku run python3 manage.py db upgrade --app casting-agency-xw

If try: psql version, the result is like below:

psql (PostgreSQL) 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)

but running psql return the error like below, it might be that the server hasn’t been installed.

psql: error: could not connect to server: No such file or directory          Is the server running locally and accepting                           connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Once the server is installed correctly, it should look like this:

Password:                                                         Welcome to Ubuntu 20.04.2 LTS (GNU/Linux 4.4.0-18362-Microsoft x86_64)postgres=#

Now connect to the Heroku database using the below command:

heroku pg:psql postgresql-clear-05212 --app casting-agency-xw

1. At first, deployment, uncomment db_drop_and_create_all() to generate the tables, but if you have made changes and want to git push the changes, do comment this command, as it will conflict with existing tables in Heroku.

""" uncomment at the first time running the app """    db_drop_and_create_all()

2. If the deployment is ok, the database also works, but the error code 503, one possibility is that it might have previous traffic conflicted. Use below to scale dyno

heroku ps:scale web=0

Wait for a while (maybe 30 seconds), then try below:

heroku ps:scale web=1

Answer by Alena Barr

python : Heroku PostgreSQL SQLalchemy構文エラー,Hobby PostgresバージョンとHerokuが提供するHOROKU Djangoアプリを展開するためのヒントは誰でも持っていますか？私がしなかったならば、トレースバックの下のこのエラーは、私がローカルでテストしていたときに似ているようですPython Manage.py移行Djangoビルドプロセスで。,python : 停電後にPostgresサーバーを回復できません,PythonチュートリアルでHerokuで始めてください。

git push heroku master
heroku ps:scale web=1
heroku open

Webページを表示しようとすると、これはHerokuログを介したトレースです。

2021-04-28T15:42:39.226298+00:00 heroku[web.1]: State changed from crashed to starting
2021-04-28T15:42:46.842959+00:00 heroku[web.1]: Starting process with command `gunicorn mysite.wsgi --log-file -`
2021-04-28T15:42:50.000000+00:00 app[api]: Build succeeded
2021-04-28T15:42:50.613040+00:00 app[web.1]: [2021-04-28 15:42:50 +0000] [4] [INFO] Starting gunicorn 20.1.0
2021-04-28T15:42:50.614019+00:00 app[web.1]: [2021-04-28 15:42:50 +0000] [4] [INFO] Listening at: http://0.0.0.0:38735 (4)
2021-04-28T15:42:50.614239+00:00 app[web.1]: [2021-04-28 15:42:50 +0000] [4] [INFO] Using worker: sync
2021-04-28T15:42:50.624501+00:00 app[web.1]: [2021-04-28 15:42:50 +0000] [9] [INFO] Booting worker with pid: 9
2021-04-28T15:42:50.648659+00:00 app[web.1]: [2021-04-28 15:42:50 +0000] [10] [INFO] Booting worker with pid: 10
2021-04-28T15:42:51.320006+00:00 heroku[web.1]: State changed from starting to up
2021-04-28T15:43:08.733160+00:00 app[web.1]: Internal Server Error: /
2021-04-28T15:43:08.733171+00:00 app[web.1]: Traceback (most recent call last):
2021-04-28T15:43:08.733172+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
2021-04-28T15:43:08.733173+00:00 app[web.1]: return self.cursor.execute(sql, params)
2021-04-28T15:43:08.733174+00:00 app[web.1]: psycopg2.errors.UndefinedTable: relation "blog_post" does not exist
2021-04-28T15:43:08.733174+00:00 app[web.1]: LINE 1: SELECT COUNT(*) AS "__count" FROM "blog_post" WHERE "blog_po...
2021-04-28T15:43:08.733175+00:00 app[web.1]: ^

Answer by Nikolas Lester

Тесты выполняются локально с базой данных sqlite3 и в среде prod-like heroku с базой данных postgresql.
,Я использую Travis для CI/CD как часть моего приложения Django с базой данных postgresql. (Django 2.1.4)
,Я удалил базу
данных sqllite3 и побежал
,
Я работаю над проектами Django 1.9 и python 3.3, используя несколько баз данных (разные схемы в одной и той же базе данных postgresql). Когда я пытаюсь перенести проект в первый раз, я получаю эту…

.travis.yml

...
before script:
-psql -c 'create database travis_ci_test;' -U postgres
services:
-postgresql
script:
-yes | python3 manage.py makemigrations
-python3 manage.py migrate auth
-python3 manage.py migrate --run-syncdb
-python3 manage.py tests test/unit_tests

settings.py

...
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'travis_ci_test',
        'USER': 'postgres',
        'PASSWORD': '',
        'HOST': 'localhost',
        }
    }

...
INSTALLED_APPS = [...
        'django.contrib.auth',
        ]