def upload_metrics(connection: connection, cursor: cursor,
metrics: list[Metric]):
"""
Insert the metrics straight into the database
Args:
cursor ([type]): database cursor
metrics (list[Metric]): list of metrics
"""
for metric in metrics:
if not metric.wikibreak_category2:
cursor.execute(
"INSERT INTO templates (name, year, month, category, wikibreak_category1, wikibreak_subcategory, amount, cumulative_amount) VALUES('{}', {}, {}, '{}', '{}', '{}', {}, {})"
.format(metric.name, metric.year, metric.month,
metric.category, metric.wikibreak_category1,
metric.wikibreak_subcategory, metric.amount,
metric.cumulative_amount))
else:
cursor.execute(
"INSERT INTO templates (name, year, month, category, wikibreak_category1, wikibreak_category2, wikibreak_subcategory, amount, cumulative_amount) VALUES('{}', {}, {}, '{}', '{}', '{}', '{}', {}, {})"
.format(metric.name, metric.year, metric.month,
metric.category, metric.wikibreak_category1,
metric.wikibreak_category2,
metric.wikibreak_subcategory, metric.amount,
metric.cumulative_amount))
connection.commit()
print("Metrics updated successfully")
def execute(connection: connection, statement: str) -> Optional[List[Tuple[str, ...]]]:
"""Execute PGSQL statement and fetches the statement response.
Parameters
----------
connection: psycopg2.extensions.connection
Active connection to a PostGreSQL database.
statement: str
PGSQL statement to run against the database.
Returns
-------
response: list or None
List of tuples, where each tuple represents a formatted line of response from the database engine, where
each tuple item roughly corresponds to a column. For instance, while a raw SELECT response might include
the table headers, psycopg2 returns only the rows that matched. If no response was given, None is returned.
"""
response = list() # type: List
# See the following link for reasoning behind both with statements:
# http://initd.org/psycopg/docs/usage.html#with-statement
#
# Additionally, the with statement makes this library safer to use with
# higher-level libraries (e.g. SQLAlchemy) that don't inherently respect
# PostGreSQL's autocommit isolation-level, since the transaction is
# properly completed for each statement.
with connection:
with connection.cursor(cursor_factory=Psycopg2Cursor) as cursor:
cursor.execute(statement)
connection.commit()
# Get response
try:
response = cursor.fetchall()
if not response:
# Empty response list
log('<No Response>', logger_name=_LOGGER_NAME)
return None
except ProgrammingError as e:
if e.args and e.args[0] == 'no results to fetch':
# No response available (i.e. no response given)
log('<No Response>', logger_name=_LOGGER_NAME)
return None
# Some other programming error; re-raise
raise e
log('Response', logger_name=_LOGGER_NAME)
log('--------', logger_name=_LOGGER_NAME)
for line in response:
log(str(line), logger_name=_LOGGER_NAME)
return response
def tpc_commit(self, xid=None):
"Commit a prepared two-phase transaction"
try:
if not xid:
# use current tpc transaction
tpc_xid = self.__tpc_xid
isolation_level = self.__tpc_prev_isolation_level
else:
# use a recovered tpc transaction
tpc_xid = xid
isolation_level = self.isolation_level
if not xid in self.tpc_recover():
raise ProgrammingError(
"Requested TPC transaction is not prepared!")
if not tpc_xid:
raise ProgrammingError(
"Cannot tpc_commit() without a TPC transaction!")
if self.__tpc_prepared or (xid != self.__tpc_xid and xid):
# a two-phase commit:
# set isolation level for the commit
self.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
curs = self.cursor()
try:
curs.execute("COMMIT PREPARED %s", (tpc_xid[1], ))
finally:
curs.close()
# return to previous isolation level
self.set_isolation_level(isolation_level)
else:
try:
# a single-phase commit
connection.commit(self)
finally:
# return to previous isolation level
self.set_isolation_level(isolation_level)
finally:
# transaction is done, clear xid
self.__tpc_xid = None
def tpc_commit(self,xid=None):
"Commit a prepared two-phase transaction"
try:
if not xid:
# use current tpc transaction
tpc_xid = self.__tpc_xid
isolation_level = self.__tpc_prev_isolation_level
else:
# use a recovered tpc transaction
tpc_xid = xid
isolation_level = self.isolation_level
if not xid in self.tpc_recover():
raise ProgrammingError("Requested TPC transaction is not prepared!")
if not tpc_xid:
raise ProgrammingError("Cannot tpc_commit() without a TPC transaction!")
if self.__tpc_prepared or (xid != self.__tpc_xid and xid):
# a two-phase commit:
# set isolation level for the commit
self.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
curs = self.cursor()
try:
curs.execute("COMMIT PREPARED %s", (tpc_xid[1],))
finally:
curs.close()
# return to previous isolation level
self.set_isolation_level(isolation_level)
else:
try:
# a single-phase commit
connection.commit(self)
finally:
# return to previous isolation level
self.set_isolation_level(isolation_level)
finally:
# transaction is done, clear xid
self.__tpc_xid = None
def commit(self):
if self.__tpc_xid:
raise ProgrammingError("Cannot commit() inside a TPC transaction!")
connection.commit(self)
def commit(self):
if self.__tpc_xid:
raise ProgrammingError("Cannot commit() inside a TPC transaction!")
connection.commit(self)