Почему через psycopg2 не могу получить список таблиц в postgres?

Question

[lisi4ka]

Пишу запрос в DBeaver

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_type = 'BASE TABLE';

и получаю список таблиц, а в python скрипте пустой массив.
Думала, что может в докере не к той базе подключила. Сделала проверку
SELECT current_database();
DBeaver и python скрипте вывели корректное имя базы данных

Обрабатываю результат запроса так:

# Подключение к базе данных
    conn = psycopg2.connect(
        dbname=os.getenv("DB_NAME"),
        user=os.getenv("DB_USERNAME"),
        password=os.getenv("DB_PASSWORD"),
        host=os.getenv("DB_HOST"),
        port=os.getenv("DB_PORT"),
    )
   sql = """
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'public'
        AND table_type = 'BASE TABLE';
    """
    with conn:
        with conn.cursor() as cursor:
            cursor.execute(sql)
            # Получение результата и распечатка
            result = cursor.fetchall()
            print(len(result))

Answer 1

[Vindicar]

Ну всё правильно. Запрос выполнен, но ты не получаешь строки ответа. Ты же не ожидаешь, что либа для БД будет сама по себе спамить строки в консоль при каждом запросе? =)

with conn:
    with conn.cursor() as cursor:
        for row in cursor.execute(sql):
            print(row)

Comments

[lisi4ka]

Забыла добавить как я принтую результат. Думала, что это не важно. Ваш код выдает

TypeError: 'NoneType' object is not iterable

т.к. cursor.execute(sql) возвращает None

[Vindicar]

lisi4ka, а. Ну это уже другой вопрос.
А у скрипта есть права на чтение information_schema? Может, там пользователя нужно настроить, или ещё что.

[lisi4ka]

Если через cursor.execute выполнить:

SELECT table_name
FROM information_schema.tables

, то я получаю массив таблиц

('pg_statistic',), ('pg_type',), ('pg_foreign_table',), ('pg_authid',) ....

Это значит, что у скрипта есть права на чтение information_schema?

[shurshur]

lisi4ka, оно и должно возвращать None. Дальше надо делать for row in cursor или более сложно так:

while True:
    row = cursor.fetchone()
    if not row: break
    ...

[shurshur]

lisi4ka, ещё замечание: при SELECT не надо делать COMMIT, ведь изменений нет. Ну кроме случаев, когда в SELECT вызывается функция, которая что-то изменяет в базе.

Тем более на каждой итерации даже при изменении данных COMMIT смысла не надо делать, обычно делают весь цикл изменений или всю серию или часть изменений и затем COMMIT скопом. Как пример, когда делается массовая вставка в таблицу, то можно каждые 10000 записей делать COMMIT, но не на каждую запись - это лишь замедлит операцию.

[lisi4ka]

shurshur, Не понимаю как итерация по пустому массиву поможет мне получить данные в массиве. И да, я знаю, что

def execute(self, query: str | bytes | Composable, vars: _Vars = None) -> None

[lisi4ka]

shurshur, COMMIT у меня остался от основного кода, где я INSERT INTO public.my_blog_user.... делаю. И получаю сообщение, что нет такой таблицы. В качестве теста решила вывести список таблиц, но их тоже нет. Вот и думаю как с этим жить и не стоило ли пойти в проститутки, пока не поздно.

[vreitech]

lisi4ka, user - зарезервированное слово в postgres. если вы действительно создали в схеме public таблицу user, то не забывайте теперь брать её имя в кавычки при каждом обращении к ней: public."user".
на будущее старайтесь приводить ваши запросы именно в том виде, в котором их используете, а не подменять значимые их части на что-то вроде table_name. я понимаю, что это не всегда возможно, но не вижу, что вам помешало в данном конкретном случае.

[lisi4ka]

vreitech, Ок, я вообще переименовала таблицу в my_blog_user. Жаль, что проблема осталась. Скрипт не может таблицу найти

[vreitech]

lisi4ka, когда таблица есть, но её не получается найти, это чаще всего означает, что не там ищут.
проверьте, что вы подключаетесь к правильному IP-адресу и порту, указываете верное имя БД (впрочем, об этому вы уже писали, что тут всё правильно) и не ошиблись со схемой (имя схемы действительно public).
потому как если отбросить варианты с ошибкой в коде (не знаток питона, но мне кажется, что либо у вас, либо у Vindicar'а там всё правильно), что у вас поломан питон или библиотека работы с postgres, из реальных вариантов остаётся только это и никаких секретных скрытых приколов или причин, почему на тот же самый запрос в dbeaver есть данные, а в скрипте на python нету.
ну и да, мой ответ выше кажется к вашей ситуации не относится: table_name - это реально существующее имя столбца в informtaion_schema.tables. мне почему-то показалось, что это суррогат, ошибся.

[vreitech]

lisi4ka, в переменную sql точно запрос не забыли запихнуть?
для conn точно не забыли корректно СУБД объявить?
print(len(result)) отдаёт 0 или другое число?

[o5a]

lisi4ka, это только значит, что у пользователя есть базовый доступ (эти таблицы из базовой схемы pg_catalog). Судя по результатам, у пользователя (под которым логинитесь) нет прав на эти таблицы (если это не администратор/superuser). Раздайте их (grant ...).

[lisi4ka]

vreitech, print(len(result)) len == 0

[lisi4ka]

o5a, Я под тем же пользаком сижу в dbeaver и он там может CRUD. Но не может создать запись в таблице т.к. не может её найти. Для теста я пытаюсь получить все таблицы с схема Public

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_type = 'BASE TABLE';

но получаю массив длиной в 0. Выполнив его под тем же пользователем в dbeaver получаю список

Или вы имели, что-то другое?

[vreitech]

lisi4ka, могу ещё предложить обернуть psycopg2.connect и cursor.execute в try ... catch блок.
что-то вроде:

from psycopg2 import errors

.........

# Подключение к базе данных
    try:
        conn = psycopg2.connect(
            dbname=os.getenv("DB_NAME"),
            user=os.getenv("DB_USERNAME"),
            password=os.getenv("DB_PASSWORD"),
            host=os.getenv("DB_HOST"),
            port=os.getenv("DB_PORT"),
        )
    except psycopg2.Error as err:
        print("\nError: ", err)
    sql = """
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'public'
        AND table_type = 'BASE TABLE';
    """
    with conn:
        with conn.cursor() as cursor:
            try:
                cursor.execute(sql)
            except psycopg2.Error as err:
                print("\nError: ", err)                
            # Получение результата и распечатка
            result = cursor.fetchall()
            print(len(result))

[lisi4ka]

На данный момент это лишние

[o5a]

lisi4ka,

Я под тем же пользаком сижу в dbeaver и он там может CRUD. Но не может создать запись в таблице т.к. не может её найти.

Под "может CRUD" подразумеваете, что может менять записи в таблицах, но не тех, что нужны, т.е. из схемы public?
Что будет, если в DBeaver (и потом скрипте) от этого пользователя просто делаете запрос

select* from public.название_той_таблицы

Если пользователь не видит нужных таблиц в information_schema, значит у него как раз нет на них прав. Также и прямой select из этой таблицы сразу покажет. что нет прав.

По вашему описанию также похоже, что в DBeaver и скрипте соединяетесь или под разным пользователем, или все-таки к разным базам.

[lisi4ka]

select* from public.название_той_таблицы

psycopg2.errors.UndefinedTable: relation "public.my_blog_user" does not exist

В DBeaver я получаю список записей в таблице.
Я удалила из DBeaver соединение к базе и заново из файлы .env перенесла данные для подключения к БД. И снова DBeaver выдал список записей в таблице.
Однако, вы мне помогли. Я дополнила .env портом к базе 5432 и получила запись из БД через psycopg2.
Тогда какую information_schema я получала без указания порта, если у меня 1 контейнер в докере с БД, а на компа локально нет БД...
Спасибо за помощь

[o5a]

lisi4ka, А до изменения получается порт пустой был? 5432 итак является портом по умолчанию, так что если не указывать, а потом указать 5432 это будет одно и то же соединение, один сервер. Возможно что-то еще параллельно изменили.

[lisi4ka]

o5a, Я его пропустила т.к. думала, что есть порт по умолчанию и он 5432, и в этом оказалось проблема. Больше изменений не было. К чему подключалась и на каком порту загадка, но хорошо что проблема разрешилась.