0

I am trying to find an efficient and reliable way to ensure that my INSERTs have indeed taken place for my empty tables.

While I would like to use a function like this

connection_info = "..."


def insert(statement: str, validate: bool):
    with psycopg.connect(connection_info) as connection:
        with connection.cursor() as cursor:
            cursor.execute(statement)
            if validate and not cursor.rowcount > 0:
               raise Error("It's not populated")
        connection.commit()

I don't know if there's a case where the cursor.rowcount is over zero, but not committed to the database. So, for now I use the following:

connection_info = "..."


def execute(statement: str):
    with psycopg.connect(connection_info) as connection:
        with connection.cursor() as cursor:
            cursor.execute(statement)
        connection.commit()


def is_populated(table: str) -> bool:
    with psycopg.connect(connection_info) as connection:
        with connection.cursor() as cursor:
            cursor.execute(f'SELECT 1 FROM {table}')
            rows = cursor.rowcount
        connection.commit()
    return rows > 0


def insert(statement: str, table: str, validate: bool):
    execute(statement)
    if validate and not is_populated(table):
        raise Error("it's not populated")

What's the best practice to ensure that my data are inserted into my tables?

PS I don't know if it makes and difference, but I'm using psycopg3

Will Be
  • 89
  • 1
  • 7
  • 1) FYI, I know the tag says `psycopg3` but the package is actually just `psycopg` v3.x.x. To that end add the actual version you are using to the question. 2) Your `cursor.rowcount > 0` is just counting the rows affected by the cursor statement not what is committed. And it won't be as neither the `connection.commit()` nor the `with` context clean up has been done. 3) The only way I know to check is to check from another session/connection. 4) Don't get into this habit: `cursor.execute(f'SELECT 1 FROM {table}')` use [sql](https://www.psycopg.org/psycopg3/docs/api/sql.html) – Adrian Klaver Apr 05 '22 at 15:33
  • @AdrianKlaver does this mean that the second code bracket is the only way to ensure a new table is populated or should I use `connection.close()` to be 100% sure? – Will Be Apr 05 '22 at 16:19
  • 1
    See here [Basic usage](https://www.psycopg.org/psycopg3/docs/basic/usage.html) and here [with connection](https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#diff-with). The `with psycopg.connect()` is a context manager that will close the connection automatically. – Adrian Klaver Apr 05 '22 at 17:04

0 Answers0