def get_records_by_page(self, page):
with get_connection(self._db_name) as c:
cursor = c.cursor()
cursor.execute(
f'SELECT * FROM {self._table_name} LIMIT {self.limit} OFFSET {(page - 1) * self.limit}'
)
return cursor.fetchmany(self.limit)
def _table_exists(self):
with get_connection(self._db_name) as c:
cursor = c.cursor()
cursor.execute(
"SELECT * FROM information_schema.tables WHERE table_name=%s",
(self._table_name.lower(), ))
return bool(cursor.rowcount)
def get_public_tables(self):
with get_connection(self._db_name) as c:
cursor = c.cursor()
cursor.execute(
"SELECT table_name FROM information_schema.tables WHERE table_schema='public'"
)
if cursor.rowcount > self._limit:
logger.warning(
'There are more tables than the limit allows. Only first %d will be returned (fetched: '
'%d)', self._limit, cursor.rowcount)
return map(itemgetter(0), cursor.fetchmany(self._limit))
def __init__(self, db_name, query_str):
self._db_name = db_name
self._limit = OptionQueryRowsLimit().get_option()
self._query_str = query_str
self._rows_affected = None
with get_connection(self._db_name) as c:
self._cursor = c.cursor()
try:
self._cursor.execute(self._query_str)
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
logger.exception('Error while trying to execute query "%s"',
self._query_str)
raise QueryExecutionError(self._query_str, e) from e
if self.is_dml:
c.commit()
def test_get_connection(self):
self.assertIsNotNone(get_connection(global_pcm.pg_db_name))
def test_non_int_readonly_env(self):
with mock.patch.dict('os.environ', {'READONLY': '?'}):
connection = get_connection(global_pcm.pg_db_name)
self.assertIsNotNone(connection)
self.assertTrue(connection.readonly)
def get_total_number_of_rows(self):
with get_connection(self._db_name) as c:
cursor = c.cursor(cursor_factory=psycopg2.extras.DictCursor)
cursor.execute(f'SELECT COUNT (*) FROM {self._table_name}')
return cursor.fetchone()['count']
def get_columns(self):
with get_connection(self._db_name) as c:
cursor = c.cursor()
cursor.execute(f'SELECT * FROM {self._table_name} LIMIT 1')
return [desc[0] for desc in cursor.description]