def test_ambiguous_names(self):
self.assertEqual(errorcodes.lookup('2F004'),
"READING_SQL_DATA_NOT_PERMITTED")
self.assertEqual(errorcodes.lookup('38004'),
"READING_SQL_DATA_NOT_PERMITTED")
self.assertEqual(errorcodes.READING_SQL_DATA_NOT_PERMITTED, '38004')
self.assertEqual(errorcodes.READING_SQL_DATA_NOT_PERMITTED_, '2F004')
def pruebaConectorBD():
SQL_PRUEBA = """SELECT table_name FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND table_schema = 'public'
ORDER BY table_type, table_name;"""
try:
print("********************* PRUEBA PARA OBTENER TODOS *********************")
cnn = ConexionBD(SQL_PRUEBA, params = None, tipoConsulta= ConexionBD.SELECT)
print(cnn.obtenerTodos())
print("********************* PRUEBA PARA OBTENER VARIOS *********************")
cnn = ConexionBD(SQL_PRUEBA, params = None, tipoConsulta= ConexionBD.SELECT)
for e in cnn.obtenerVarios(2):
print(e)
print("********************* PRUEBA PARA OBTENER UNO *********************")
cnn = ConexionBD(SQL_PRUEBA, params = None, tipoConsulta= ConexionBD.SELECT)
print(cnn.obtenerUno())
print("PRUEBAS A CONECTORBD HECHAS SATISFACTORIAMENTE")
except Exception, e:
print(e)
print(errorcodes.lookup(e.pgcode[:2]))
print(errorcodes.lookup(e.pgcode))
print("ERROR EN PRUEBAS A CONECTORBD")
def get_strerror(exc):
if exc.pgcode == None:
return "Code=None: Database error"
return "Code={0}: {1}: {2}".format(
exc.pgcode,
errorcodes.lookup(exc.pgcode[:2]),
errorcodes.lookup(exc.pgcode)
)
def retrying(func, env):
"""
Call ``func`` until the function returns without serialisation
error. A serialisation error occurs when two requests in independent
cursors perform incompatible changes (such as writing different
values on a same record). By default, it retries up to 5 times.
:param callable func: The function to call, you can pass arguments
using :func:`functools.partial`:.
:param odoo.api.Environment env: The environment where the registry
and the cursor are taken.
"""
try:
for tryno in range(1, MAX_TRIES_ON_CONCURRENCY_FAILURE + 1):
tryleft = MAX_TRIES_ON_CONCURRENCY_FAILURE - tryno
try:
result = func()
if not env.cr._closed:
env.cr.flush() # submit the changes to the database
break
except (IntegrityError, OperationalError) as exc:
if env.cr._closed:
raise
env.cr.rollback()
env.registry.reset_changes()
if request:
request.session.clear()
request.session.update(json.loads(request.session.json_data))
if isinstance(exc, IntegrityError):
raise _as_validation_error(env, exc) from exc
if exc.pgcode not in PG_CONCURRENCY_ERRORS_TO_RETRY:
raise
if not tryleft:
_logger.info("%s, maximum number of tries reached!", errorcodes.lookup(exc.pgcode))
raise
wait_time = random.uniform(0.0, 2 ** tryno)
_logger.info("%s, %s tries left, try again in %.04f sec...", errorcodes.lookup(exc.pgcode), tryleft, wait_time)
time.sleep(wait_time)
else:
# handled in the "if not tryleft" case
raise RuntimeError("unreachable")
except Exception:
env.registry.reset_changes()
raise
if not env.cr._closed:
env.cr.commit() # effectively commits and execute post-commits
env.registry.signal_changes()
return result
def open_spider(self, spider):
# 连接到数据库。
try:
self.connector = psycopg2.connect(user=self.username,
password=self.password,
host=self.host,
database=self.database)
self.logger.info('Conneting to database successfully!')
except psycopg2.Error as e:
sys.exit('Failed to connect database. Returned: {0:s}'.format(
errorcodes.lookup(e.pgcode)))
# 如果表不存在，则首先建表。
cursor = self.connector.cursor()
try:
cursor.execute(
'CREATE TABLE IF NOT EXISTS {0:s} (user_id varchar(20) PRIMARY KEY NOT NULL, user_name text NOT NULL, gender varchar(5) NOT NULL, district text NOT NULL, crawl_date date NOT NULL);'
.format(self.table_name_dict['user_info']))
cursor.execute(
'CREATE TABLE IF NOT EXISTS {0:s} (user_id varchar(20) PRIMARY KEY NOT NULL, follow_list text[] NOT NULL, crawl_date date NOT NULL);'
.format(self.table_name_dict['follow']))
cursor.execute(
'CREATE TABLE IF NOT EXISTS {0:s} (user_id varchar(20) PRIMARY KEY NOT NULL, fan_list text[] NOT NULL, crawl_date date NOT NULL);'
.format(self.table_name_dict['fan']))
cursor.execute(
'CREATE TABLE IF NOT EXISTS {0:s} (user_id varchar(20) NOT NULL, post_id varchar(20) NOT NULL, publish_time timestamp NOT NULL, crawl_date date NOT NULL, PRIMARY KEY(user_id, post_id));'
.format(self.table_name_dict['post']))
cursor.execute(
'CREATE TABLE IF NOT EXISTS {0:s} (user_id varchar(20) NOT NULL, post_id varchar(20) NOT NULL, text text NOT NULL, crawl_date date NOT NULL, PRIMARY KEY(user_id, post_id));'
.format(self.table_name_dict['text']))
cursor.execute(
'CREATE TABLE IF NOT EXISTS {0:s} (user_id varchar(20) NOT NULL, post_id varchar(20) NOT NULL, image_list text[] NOT NULL, crawl_date date NOT NULL, PRIMARY KEY(user_id, post_id));'
.format(self.table_name_dict['image']))
cursor.execute(
'CREATE TABLE IF NOT EXISTS {0:s} (user_id varchar(20) NOT NULL, post_id varchar(20) NOT NULL, comment_list json NOT NULL, crawl_date date NOT NULL, PRIMARY KEY(user_id, post_id));'
.format(self.table_name_dict['comment']))
cursor.execute(
'CREATE TABLE IF NOT EXISTS {0:s} (user_id varchar(20) NOT NULL, post_id varchar(20) NOT NULL, forward_list json NOT NULL, crawl_date date NOT NULL, PRIMARY KEY(user_id, post_id));'
.format(self.table_name_dict['forward']))
cursor.execute(
'CREATE TABLE IF NOT EXISTS {0:s} (user_id varchar(20) NOT NULL, post_id varchar(20) NOT NULL, thumbup_list json NOT NULL, crawl_date date NOT NULL, PRIMARY KEY(user_id, post_id));'
.format(self.table_name_dict['thumbup']))
self.connector.commit()
self.logger.info('Table check finished!')
except psycopg2.Error as e:
self.logger.error('Table check failed. Returned: {0:s}.'.format(
errorcodes.lookup(e.pgcode)))
finally:
cursor.close()
def execute_sql(name, score):
conn = util.connect_db(db_connect_command)
cursor = conn.cursor()
try:
cursor.execute("INSERT INTO finishes (score, name) VALUES (%s, %s);",
(score, name))
conn.commit()
conn.close()
print("t2")
return render_template("restart.html")
except psycopg2.Error as e:
conn.rollback()
conn.close()
print(errorcodes.lookup(e.pgcode))
return errorcodes.lookup(e.pgcode[:2])
def insert_pgp_key(k, dbcursor):
if (k == None or k == ""):
print "skipping empty key '%s'" %k
return
# first try inserting the key
try:
print "inserting pgp key %s" %k
#print row
#print dbcursor.mogrify("INSERT INTO contactdb_pgpkey (pgp_key_id) VALUES (%s)", (k,))
dbcursor.execute("INSERT INTO contactdb_pgpkey (pgp_key_id) VALUES (%s)", (k,))
#print "inserted id %d" %(dbcursor.lastrowid)
except Exception, e:
print "could not insert pgp key %s" %k
print errorcodes.lookup(e)
def leaderboards():
conn = util.connect_db(db_connect_command)
cursor = conn.cursor()
results = [0, '']
try:
cursor.execute("SELECT * FROM finishes ORDER BY score ASC LIMIT 6")
rows = cursor.fetchall()
conn.commit()
conn.close()
return render_template("leaderboards.html", result=rows)
except psycopg2.Error as e:
conn.rollback()
conn.close()
print(errorcodes.lookup(e.pgcode))
return errorcodes.lookup(e.pgcode[:2])
def get_instances_by_status(status):
"""Returns a JSON object containing all the data for instances with a status"""
try:
status = str(status)
conn = instdb.get_connection()
curs = conn.cursor()
curs.execute("""SELECT username, db_name, e_group, category, creation_date, expiry_date, db_type, db_size, no_connections, project, description, version, state, status, master, slave, host
FROM dod_instances WHERE status = :status
ORDER BY db_name""", {"status": status})
rows = curs.fetchall()
cols = [i[0] for i in curs.description]
if rows:
res = create_json_from_result(rows, cols)
# Load the user's data from FIM and join it to the result in Json
connF = fimdb.get_connection()
cursF = connF.cursor()
cursF.execute("""SELECT instance_name, owner_first_name, owner_last_name, owner_login, owner_mail, owner_phone1, owner_phone2, owner_portable_phone, owner_department, owner_group, owner_section
FROM fim_ora_ma.db_on_demand""")
rowsF = cursF.fetchall()
colsF = [i[0] for i in cursF.description]
if rowsF:
usersJson = create_dict_from_result(rowsF, colsF, "INSTANCE_NAME")
for instance in res:
if instance["DB_NAME"] in usersJson:
instance["USER"] = usersJson[instance["DB_NAME"]]
return res
return None
except DatabaseError as dberr:
logging.error("PG Error: %s", dberr.pgerror)
logging.error("PG Error lookup: %s", errorcodes.lookup(dberr.pgcode))
return None
finally:
instdb.end_connection(conn)
def addHeadpat(dbURL, guildID, url):
'''
Adds a URL to the database given in a headpats(guild, url) table
dbURL: database URL
guildID: guild ID
url: Image of the headpat
'''
command = f"INSERT INTO headpats(guild, url) VALUES ('{guildID}','{url}')"
res = -2
try:
conn = db.connect(dbURL)
cur = conn.cursor()
cur.execute(command)
conn.commit()
res = 0
except db.DatabaseError as error:
traceback.print_exc()
logger.error(errorcodes.lookup(error.pgcode))
#logger.error(errorcodes.UNIQUE_VIOLATION)
if error.pgcode == errorcodes.UNIQUE_VIOLATION:
res = -1
else:
res = -2
except Exception as error:
res = -2
finally:
cur.close()
conn.close()
return res
def _check_stmt_err(stmt, force):
"""Validate results of an executed Statement object relative to force flag
Creating a constraint can produce a 'benign' error if it already exists.
If `force` is true, such an error is ignored.
:param Statement stmt:
:param bool force:
:return: None
:raise: DatabaseError
"""
if stmt.err is None:
return
# Detect error 42P16: multiple primary keys ... are not allowed.
# This error is produced when the primary key is applied redundantly.
already_exists = (
hasattr(stmt.err, 'pgcode')
and stmt.err.pgcode
and psycopg2_errorcodes.lookup(
stmt.err.pgcode) == 'INVALID_TABLE_DEFINITION')
if force and already_exists:
return
raise stmt.err
def insert_pgp_key(k, dbcursor):
if (k == None or k == ""):
print "skipping empty key '%s'" % k
return
# first try inserting the key
try:
print "inserting pgp key %s" % k
#print row
#print dbcursor.mogrify("INSERT INTO contactdb_pgpkey (pgp_key_id) VALUES (%s)", (k,))
dbcursor.execute(
"INSERT INTO contactdb_pgpkey (pgp_key_id) VALUES (%s)", (k, ))
#print "inserted id %d" %(dbcursor.lastrowid)
except Exception, e:
print "could not insert pgp key %s" % k
print errorcodes.lookup(e)
def execute_db_query(db_name, query_string, args):
"""
Inputs:
db_name: type(string)
query_string: type(string)
args: type(list of strings)
returns:
type:
pg.Error or NoneType)
"""
conn = pg.connect("dbname=%s" %(db_name))
curr = conn.cursor()
try:
curr.execute(query_string, args)
conn.commit()
except pg.Error as err:
return errorcodes.lookup(err.pgcode)
try:
result = curr.fetchall()
except pg.ProgrammingError as e:
print e
result = None
curr.close()
conn.close()
return result
def get(self):
#connect_db
db = psycopg2.connect(database="meetjobs",
user="******",
password="******",
host="192.168.163.128",
port="5432")
cur = db.cursor()
l_sql = "SELECT ProductId,ProductName1,StockQty FROM Product"
try:
cur.execute(l_sql)
except Exception as e:
db.close()
return {"message": errorcodes.lookup(e.pgcode)}
rows = cur.fetchall()
if rows == None:
db.close()
return {"message": "No available Product!"}
l_itemlist = []
#format example {“stock”:{“商品id”:”A001”, “品名”:”曼特寧特調”, ”庫存量”:”45”}}
for row in rows:
l_item = {
"ProductId": row[0],
"ProductName1": row[1],
"StockQty": str(row[2])
}
l_itemlist.append(l_item)
db.close()
return {"item_list": l_itemlist}
def log_pg_exception_details(exc: Exception) -> bool:
"""Logs details from a database exception."""
if not isinstance(exc, dbapi.Error):
return False
try:
for arg in exc.args:
_log.debug('exc.arg: %s', arg)
except AttributeError:
_log.debug('exception has no <.args>')
_log.debug('pgerror: [%s]', exc.pgerror)
if exc.pgcode is None:
_log.debug('pgcode : %s', exc.pgcode)
else:
_log.debug('pgcode : %s (%s)', exc.pgcode, SQL_error_codes.lookup(exc.pgcode))
log_cursor_state(exc.cursor)
try:
diags = exc.diag
except AttributeError:
_log.debug('<.diag> not available')
diags = None
if diags is None:
return True
for attr in dir(diags):
if attr.startswith('__'):
continue
val = getattr(diags, attr)
if val is None:
continue
_log.debug('%s: %s', attr, val)
return True
def log_pg_exception_details(exc):
if not isinstance(exc, dbapi.Error):
return False
try:
args = exc.args
for arg in args:
_log.debug('exc.arg: %s', arg)
except AttributeError:
_log.debug('exception has no <.args>')
_log.debug('pgerror: [%s]', exc.pgerror)
if exc.pgcode is None:
_log.debug('pgcode : %s', exc.pgcode)
else:
_log.debug('pgcode : %s (%s)', exc.pgcode,
SQL_error_codes.lookup(exc.pgcode))
if exc.cursor is None:
_log.debug('cursor: None')
else:
log_cursor_state(exc.cursor)
try:
exc.diag
for attr in dir(exc.diag):
if attr.startswith('__'):
continue
val = getattr(exc.diag, attr)
if val is None:
continue
_log.debug('%s: %s', attr, val)
except AttributeError:
_log.debug('diag: not available')
return True
def get_functional_alias(db_name):
"""Returns the funcional alias and dnsname for a certain database"""
try:
with POOL.getconn() as conn:
with conn.cursor() as curs:
curs.execute("""select dns_name, alias
from functional_aliases
where db_name = %s""", (db_name,))
logging.debug('DB query: %s', curs.query)
return curs.fetchone()
except DatabaseError as dberr:
logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode[:2]))
logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode))
return None
finally:
POOL.putconn(conn)
def save_feed_entries(entries):
"""
Stores the given list of entries in the database
Arguments:
* feed_id - id of the feed to store the entries under
* entries - a list of feed entries
"""
cursor = db.get_cursor(db.get_connection())
insert_stmt = """INSERT INTO entries(
item_id,
entry_published,
entry_title,
entry_author,
entry_link,
feed_id
) VALUES ( %s, %s, %s, %s, %s, %s );"""
for entry in entries:
try:
cursor.execute(insert_stmt, entry)
cursor.connection.commit()
except IntegrityError as ie:
err = errorcodes.lookup(ie.pgcode)
if(err != 'UNIQUE_VIOLATION'): # Unique violation
logger.info("Integrity error: %s", ie)
raise
cursor.connection.rollback()
cursor.connection.commit() # Probably not neccesary
cursor.close()
return True
def bulkDictionaryInsert(self, table_name, col_dict, id_column=None):
"""
"""
if len(col_dict) == 0:
return
placeholders = ', '.join(['%s'] * len(col_dict))
columns = ', '.join(col_dict.keys())
sql = "INSERT into %s ( %s ) VALUES ( %s )" % (table_name, columns, placeholders)
if id_column is not None:
sql = sql + " RETURNING " + id_column
try:
self.cur.execute(sql, col_dict.values())
if id_column is not None:
id_of_new_row = self.cur.fetchone()[0]
except Exception as e:
pgError = errorcodes.lookup(e.pgcode)
raise RuntimeError(pgError)
if id_column is not None:
return id_of_new_row
else:
return None
def batchCommit(self):
"""
"""
try:
self.con.commit()
except Exception as e:
pgError =errorcodes.lookup(e.pgcode)
raise RuntimeError(pgError)
def next_dnsname():
"""Returns the next dnsname which can be used for a newly created
instance, if any."""
try:
with POOL.getconn() as conn:
with conn.cursor() as curs:
curs.execute("""select dns_name
from functional_aliases
where db_name is NULL order by dns_name limit 1""")
logging.debug('DB query: %s', curs.query)
return curs.fetchone() # First unused dnsname or None
except DatabaseError as dberr:
logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode[:2]))
logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode))
return None
finally:
POOL.putconn(conn)
def next_dnsname():
"""Returns the next dnsname which can be used for a newly created
instance, if any."""
try:
with POOL.getconn() as conn:
with conn.cursor() as curs:
curs.execute("""select dns_name
from functional_aliases
where db_name is NULL order by dns_name limit 1""")
logging.debug('DB query: %s', curs.query)
return curs.fetchone() # First unused dnsname or None
except DatabaseError as dberr:
logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode[:2]))
logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode))
return None
finally:
POOL.putconn(conn)
def insertToDB( fecha, id_est, value, table, conn):
""" This function inserts single values into the specified table """
dateFormat = "MM/DD/YYY/HH24"
sql = "SET TimeZone='UTC'; INSERT INTO %s (fecha, val, id_est) VALUES (to_timestamp('%s','%s'), '%s', '%s')\n" % (table, fecha, dateFormat, value, id_est)
cur = conn.cursor();
try:
cur.execute(sql);
conn.commit()
except psycopg2.DatabaseError as e:
#except psycopg2.IntegrityError as e:
if e.pgcode == '25P02':
print('Failed to insert query, CODE:', e.pgcode, " Detail: ", errorcodes.lookup(e.pgcode[:2]))
else:
print('Failed to insert query, CODE:', e.pgcode, " Detail: ", errorcodes.lookup(e.pgcode[:2]))
cur.close()
conn.rollback()
def exec_sql(self, sql):
try:
results = self.cur.execute(sql)
except Exception as e:
print(e)
pgError = errorcodes.lookup(e.pgcode)
raise RuntimeError(pgError)
return results
def fetch_sql(self, sql, blocksize=1000):
try:
self.cur.execute(sql)
results = _result_iter(self.cur, arraysize=blocksize)
except Exception as e:
pgError = errorcodes.lookup(e.pgcode)
raise RuntimeError(pgError)
return results
def save_to_db(conn_string, name_file, db_file, insert_func):
count = 0
start_time = datetime.now()
with psycopg2.connect(conn_string) as conn:
with conn.cursor() as cur:
for row in get_line(db_file):
sql = insert_func(row,name_file)
try:
cur.execute(sql)
count = count + 1
if count % 1000 == 0:
conn.commit()
print count," done"
except Exception,e:
print errorcodes.lookup(e.pgcode)
print sql,e
break
def get_functional_alias(db_name):
"""Returns the funcional alias and dnsname for a certain database"""
try:
with POOL.getconn() as conn:
with conn.cursor() as curs:
curs.execute(
"""select dns_name, alias
from functional_aliases
where db_name = %s""", (db_name, ))
logging.debug('DB query: %s', curs.query)
return curs.fetchone()
except DatabaseError as dberr:
logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode[:2]))
logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode))
return None
finally:
POOL.putconn(conn)
def allow_pgcodes(cr, *codes):
"""Context manager that will omit specified error codes.
E.g., suppose you expect a migration to produce unique constraint
violations and you want to ignore them. Then you could just do::
with allow_pgcodes(cr, psycopg2.errorcodes.UNIQUE_VIOLATION):
cr.execute("INSERT INTO me (name) SELECT name FROM you")
.. warning::
**All** sentences inside this context will be rolled back if **a single
error** is raised, so the above example would insert **nothing** if a
single row violates a unique constraint.
This would ignore duplicate files but insert the others::
cr.execute("SELECT name FROM you")
for row in cr.fetchall():
with allow_pgcodes(cr, psycopg2.errorcodes.UNIQUE_VIOLATION):
cr.execute("INSERT INTO me (name) VALUES (%s)", row[0])
:param *str codes:
Undefined amount of error codes found in :mod:`psycopg2.errorcodes`
that are allowed. Codes can have either 2 characters (indicating an
error class) or 5 (indicating a concrete error). Any other errors
will be raised.
"""
try:
from psycopg2 import errorcodes, ProgrammingError
except ImportError:
from psycopg2cffi import errorcodes, ProgrammingError
try:
with cr.savepoint():
yield
except ProgrammingError as error:
msg = "Code: {code}. Class: {class_}. Error: {error}.".format(
code=error.pgcode,
class_=errorcodes.lookup(error.pgcode[:2]),
error=errorcodes.lookup(error.pgcode))
if error.pgcode not in codes and error.pgcode[:2] in codes:
logger.info(msg)
else:
logger.exception(msg)
raise
def allow_pgcodes(cr, *codes):
"""Context manager that will omit specified error codes.
E.g., suppose you expect a migration to produce unique constraint
violations and you want to ignore them. Then you could just do::
with allow_pgcodes(cr, psycopg2.errorcodes.UNIQUE_VIOLATION):
cr.execute("INSERT INTO me (name) SELECT name FROM you")
.. warning::
**All** sentences inside this context will be rolled back if **a single
error** is raised, so the above example would insert **nothing** if a
single row violates a unique constraint.
This would ignore duplicate files but insert the others::
cr.execute("SELECT name FROM you")
for row in cr.fetchall():
with allow_pgcodes(cr, psycopg2.errorcodes.UNIQUE_VIOLATION):
cr.execute("INSERT INTO me (name) VALUES (%s)", row[0])
:param *str codes:
Undefined amount of error codes found in :mod:`psycopg2.errorcodes`
that are allowed. Codes can have either 2 characters (indicating an
error class) or 5 (indicating a concrete error). Any other errors
will be raised.
"""
try:
from psycopg2 import errorcodes, ProgrammingError
except ImportError:
from psycopg2cffi import errorcodes, ProgrammingError
try:
with cr.savepoint():
yield
except ProgrammingError as error:
msg = "Code: {code}. Class: {class_}. Error: {error}.".format(
code=error.pgcode,
class_=errorcodes.lookup(error.pgcode[:2]),
error=errorcodes.lookup(error.pgcode))
if error.pgcode not in codes and error.pgcode[:2] in codes:
logger.info(msg)
else:
logger.exception(msg)
raise
def delete_arch(arch_id):
try:
# Deletes a specific arch
retval = app.config['db'].delete_arch(arch_id)
if(retval == True):
response.status = "200 DELETE OK"
elif(retval == False):
response.status = "404 Cannot DELETE"
elif(retval == "23503"):
response.status = "409 " + str(errorcodes.lookup(retval))
else:
response.status = "500 " + str(errorcodes.lookup(retval))
return response.status
except Exception as e:
raise Exception('Exception encountered: ' + str(e))
return None
def print_error(err):
_, _, traceback = sys.exc_info() # err_type, err_obj, traceback
open_brace_idx, close_brace_idx = err.pgerror.index(
"("), err.pgerror.index(")")
line_num = traceback.tb_lineno
print(line_num)
print(errorcodes.lookup(err.pgcode))
print(err.pgerror[open_brace_idx + 1:close_brace_idx])
def pg_errorname_lookup(pgcode):
"""
Args:
pgcode(int): A PostgreSQL error code.
Returns:
The error name from a PostgreSQL error code as per: https://www.postgresql.org/docs/9.5/static/errcodes-appendix.html
"""
return pgerrorcodes.lookup(str(pgcode))
def connect_db(connect_command):
'''
'''
try:
conn = psycopg2.connect(connect_command)
except psycopg2.Error as e:
print(errorcodes.lookup(e.pgcode[:2]))
return 0
return conn
def update_functional_alias(dnsname, db_name, alias):
"""Updates a dnsname record with its db_name and alias"""
try:
with POOL.getconn() as conn:
with conn.cursor() as curs:
logging.debug("Updating functional alias record (%s, %s, %s)",
dnsname, db_name, alias)
curs.execute("""update functional_aliases
set db_name = %s, alias = %s where dns_name = %s""",
(db_name, alias, dnsname,))
logging.debug('DB query: %s', curs.query)
conn.commit()
# Return True if the record was updated succesfully
return curs.rowcount == 1
except DatabaseError as dberr:
logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode[:2]))
logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode))
return None
finally:
POOL.putconn(conn)
def host_metadata(host):
"""Returns a JSON object containing the metadata for all the entities
residing on a host"""
try:
with POOL.getconn() as conn:
with conn.cursor() as curs:
curs.execute("""select db_name, data
from (
select db_name, json_array_elements(data->'hosts') host, data
from metadata)
as foo
where trim(foo.host::text, '"') = %s""", (host, ))
res = curs.fetchall() # Either a list of tuples or empty list
return res if res else None
except DatabaseError as dberr:
logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode[:2]))
logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode))
return None
finally:
POOL.putconn(conn)
def exec_sql(self, sql):
"""
:param sql:
:return:
"""
try:
self.cur.execute(sql)
results = self.cur.rowcount
except Exception as e:
pgError = errorcodes.lookup(e.pgcode)
raise RuntimeError(pgError)
return results
def host_metadata(host):
"""Returns a JSON object containing the metadata for all the entities
residing on a host"""
try:
with POOL.getconn() as conn:
with conn.cursor() as curs:
curs.execute(
"""select db_name, data
from (
select db_name, json_array_elements(data->'hosts') host, data
from metadata)
as foo
where trim(foo.host::text, '"') = %s""", (host, ))
res = curs.fetchall() # Either a list of tuples or empty list
return res if res else None
except DatabaseError as dberr:
logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode[:2]))
logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode))
return None
finally:
POOL.putconn(conn)
def init_db(filename_list, connect_command):
'''
'''
conn = connect_db(connect_command)
if conn == 0:
print('cannot connect to db')
else:
cursor = conn.cursor()
try:
# setup database
for filename in filename_list:
fp = open(filename, 'r')
cursor.execute(fp.read())
fp.close()
conn.commit()
conn.close()
return 1
except psycopg2.Error as e:
print(errorcodes.lookup(e.pgcode))
print(errorcodes.lookup(e.pgcode[:2]))
return 0
def _raise_execution_error(self, sql, sql_args, error):
"""Rollbacks the current transaction and raises a useful error
The error message contains the name of the transaction, the failed
query, the arguments of the failed query and the error generated.
Raises
------
ValueError
"""
self.rollback()
raise ValueError("Error running SQL: %s. MSG: %s\n" %
(errorcodes.lookup(error.pgcode), error.message))
def open_spider(self, spider):
try:
self.connector = psycopg2.connect(
user = self.username,
database = self.database
)
self.cursor = self.connector.cursor()
print('Conneting to database successfully!')
except psycopg2.Error as e:
print(
'Failed to connect database. Returned: %s'
% errorcodes.lookup(e.pgcode)
)
exit(-1)
def pruebaConectorBD():
SQL_PRUEBA = """SELECT table_name FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND table_schema = 'public'
ORDER BY table_type, table_name;"""
try:
print(
"********************* PRUEBA PARA OBTENER TODOS *********************"
)
cnn = ConexionBD(SQL_PRUEBA,
params=None,
tipoConsulta=ConexionBD.SELECT)
print(cnn.obtenerTodos())
print(
"********************* PRUEBA PARA OBTENER VARIOS *********************"
)
cnn = ConexionBD(SQL_PRUEBA,
params=None,
tipoConsulta=ConexionBD.SELECT)
for e in cnn.obtenerVarios(2):
print(e)
print(
"********************* PRUEBA PARA OBTENER UNO *********************"
)
cnn = ConexionBD(SQL_PRUEBA,
params=None,
tipoConsulta=ConexionBD.SELECT)
print(cnn.obtenerUno())
print("PRUEBAS A CONECTORBD HECHAS SATISFACTORIAMENTE")
except Exception, e:
print(e)
print(errorcodes.lookup(e.pgcode[:2]))
print(errorcodes.lookup(e.pgcode))
print("ERROR EN PRUEBAS A CONECTORBD")
def index():
conn = util.connect_db(db_connect_command)
if conn == 0:
info = 'error'
else:
cursor = conn.cursor()
try:
sql_command = 'select * from student;'
cursor.execute(sql_command)
info = cursor.fetchall()
conn.close()
except psycopg2.Error as e:
conn.close()
info = errorcodes.lookup(e.pgcode[:2])
return render_template('index.html', info = info)
def bulk_post_cleanup(self, table_name):
sql = """
delete from {0}
where etl_updated=0
and nk in (select nk from {0} where etl_updated = 1);
update {0} set etl_updated = 0
where etl_updated = 1;""".format( # nosec
table_name)
try:
self.cur.execute(sql)
except Exception as e:
pgError = errorcodes.lookup(e.pgcode)
raise RuntimeError(pgError)
def bulkPostCleanup(self,table_name):
"""
"""
sql = """
delete from {0}
where etl_updated=0
and nk in (select nk from {0} where etl_updated = 1);
update {0} set etl_updated = 0 where etl_updated = 1;""" .format(table_name)
try:
self.cur.execute(sql)
except Exception as e:
pgError = errorcodes.lookup(e.pgcode)
raise RuntimeError(pgError)
def _check_stmt_err(stmt, force):
"""Check statement for errors.
If the error is benign and force is true, ignore the error.
:param pedsnetdcc.db.Statement stmt:
:param bool force:
:return: None
:raise: DatabaseError if error in a statement
:raise: psycopg2.OperationalError if connection error
"""
if stmt.err is None:
return
dropping = 'DROP CONSTRAINT' in stmt.sql
creating = 'ADD CONSTRAINT' in stmt.sql
# Detect error 42704
does_not_exist = (
hasattr(stmt.err, 'pgcode')
and stmt.err.pgcode
and psycopg2_errorcodes.lookup(stmt.err.pgcode) == 'UNDEFINED_OBJECT')
# Detect error 42710
already_exists = (
hasattr(stmt.err, 'pgcode')
and stmt.err.pgcode
and psycopg2_errorcodes.lookup(stmt.err.pgcode) == 'DUPLICATE_OBJECT')
if dropping and force and does_not_exist:
return
if creating and force and already_exists:
return
raise stmt.err
def delete_resource(resource):
"""Deletes an entry in the resources table"""
try:
with POOL.getconn() as conn:
with conn.cursor() as curs:
curs.execute("""delete from dod_instance_request where db_name = %s""", (resource, ))
logging.debug('DB query: %s', curs.query)
conn.commit()
return curs.rowcount == 1
except DatabaseError as dberr:
logging.error("PG Error: %s", dberr.pgerror)
logging.error("PG Error lookup: %s", errorcodes.lookup(dberr.pgcode))
return None
finally:
POOL.putconn(conn)
def _check_stmt_err(stmt, force):
"""Check statement for errors.
If the error is benign and force is true, ignore the error.
:param pedsnetdcc.db.Statement stmt:
:param bool force:
:return: None
:raise: DatabaseError if error in a statement
:raise: psycopg2.OperationalError if connection error
"""
if stmt.err is None:
return
dropping = stmt.sql.startswith('DROP')
creating = stmt.sql.startswith('CREATE')
# Detect error 42P01 (doesn't exist); btw, an index is a table in PG
does_not_exist = (
hasattr(stmt.err, 'pgcode')
and stmt.err.pgcode
and psycopg2_errorcodes.lookup(stmt.err.pgcode) == 'UNDEFINED_OBJECT')
# Detect error 42P07 (already exists); btw, an index is a table in PG
already_exists = (
hasattr(stmt.err, 'pgcode')
and stmt.err.pgcode
and psycopg2_errorcodes.lookup(stmt.err.pgcode) == 'DUPLICATE_TABLE')
if dropping and force and does_not_exist:
return
if creating and force and already_exists:
return
raise stmt.err
def update_functional_alias(dnsname, db_name, alias):
"""Updates a dnsname record with its db_name and alias"""
try:
with POOL.getconn() as conn:
with conn.cursor() as curs:
logging.debug("Updating functional alias record (%s, %s, %s)",
dnsname, db_name, alias)
curs.execute(
"""update functional_aliases
set db_name = %s, alias = %s where dns_name = %s""", (
db_name,
alias,
dnsname,
))
logging.debug('DB query: %s', curs.query)
conn.commit()
# Return True if the record was updated succesfully
return curs.rowcount == 1
except DatabaseError as dberr:
logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode[:2]))
logging.error("PG Error: %s", errorcodes.lookup(dberr.pgcode))
return None
finally:
POOL.putconn(conn)
def pg_error(stmt):
"""Return the PostgresSQL condition name from a Statement result.
See https://www.postgresql.org/docs/9.5/static/errcodes-appendix.html.
If there is no error on the statement, an empty string will be returned.
:param Statement stmt: executed Statement
:return: PG condition name, or ''
:rtype: str
"""
if hasattr(stmt.err, 'pgcode') and stmt.err.pgcode:
condition = psycopg2_errorcodes.lookup(stmt.err.pgcode)
if condition:
return condition
return ''
def get_resource(resource):
"""Returns an entry in the resources table"""
try:
resource = str(resource)
with POOL.getconn() as conn:
with conn.cursor() as curs:
curs.execute("""select * from dod_instance_request where db_name = %s""",
(resource, ))
res = curs.fetchone()
return res if res else None
except DatabaseError as dberr:
logging.error("PG Error: %s", dberr.pgerror)
logging.error("PG Error lookup: %s", errorcodes.lookup(dberr.pgcode))
return None
finally:
POOL.putconn(conn)
def register_user(cur, form):
username = form['username']
email = form['email']
posted_password = form['password']
password = bcrypt.hashpw(posted_password, bcrypt.gensalt())
try:
cur.execute('''
INSERT INTO booknet_user (login_name, email, password, level_id, date_created, is_active)
VALUES(%s, %s, %s, 1, current_timestamp, true)
RETURNING user_id
''', (username,email,password))
if cur.rowcount == 1:
user_id = cur.fetchone()[0]
return True, user_id, None
except Exception, e:
return False, None, errorcodes.lookup(e.pgcode[:2])
def get_metadata(entity):
"""Returns a JSON object containing all the metadata for a certain entity"""
try:
entity = str(entity)
with POOL.getconn() as conn:
with conn.cursor() as curs:
curs.execute("""select data from metadata where db_name = %s""",
(entity, ))
res = curs.fetchone()
return res[0] if res else None
except DatabaseError as dberr:
logging.error("PG Error: %s", dberr.pgerror)
logging.error("PG Error lookup: %s", errorcodes.lookup(dberr.pgcode))
return None
finally:
POOL.putconn(conn)
def delete_metadata(entity):
"""Deletes the metadata entry for an entity"""
try:
with POOL.getconn() as conn:
with conn.cursor() as curs:
curs.execute("""delete from metadata where db_name = %s""",
(entity, ))
logging.debug('DB query: %s', curs.query)
conn.commit()
return curs.rowcount == 1
except DatabaseError as dberr:
logging.error("PG Error: %s", dberr.pgerror)
logging.error("PG Error lookup: %s", errorcodes.lookup(dberr.pgcode))
return None
finally:
POOL.putconn(conn)
def delete_metadata(entity):
"""Deletes the metadata entry for an entity"""
try:
with POOL.getconn() as conn:
with conn.cursor() as curs:
curs.execute("""delete from metadata where db_name = %s""",
(entity, ))
logging.debug('DB query: %s', curs.query)
conn.commit()
return curs.rowcount == 1
except DatabaseError as dberr:
logging.error("PG Error: %s", dberr.pgerror)
logging.error("PG Error lookup: %s", errorcodes.lookup(dberr.pgcode))
return None
finally:
POOL.putconn(conn)
def _raise_execution_error(self, sql, sql_args, error):
"""Rollbacks the current transaction and raises a useful error
The error message contains the name of the transaction, the failed
query, the arguments of the failed query and the error generated.
Raises
------
ValueError
"""
self.rollback()
try:
ec_lu = errorcodes.lookup(error.pgcode)
raise ValueError(
"Error running SQL: %s. MSG: %s\n" % (ec_lu, str(error)))
except (KeyError, AttributeError):
raise ValueError("Error running SQL query: %s" % str(error))
def _raise_execution_error(self, sql, sql_args, error):
"""Rollbacks the current transaction and raises a useful error
The error message contains the name of the transaction, the failed
query, the arguments of the failed query and the error generated.
Raises
------
ValueError
"""
self.rollback()
try:
ec_lu = errorcodes.lookup(error.pgcode)
raise ValueError(
"Error running SQL: %s. MSG: %s\n" % (ec_lu, str(error)))
except (KeyError, AttributeError):
raise ValueError("Error running SQL query: %s" % str(error))
def execute_sql_display(sql_query=''):
'''
begin with select, so return results back
'''
conn = util.connect_db(db_connect_command)
cursor = conn.cursor()
try:
cursor.execute(sql_query)
result = cursor.fetchall()
# conn.commit()
conn.close()
return str(result)
except psycopg2.Error as e:
conn.rollback()
conn.close()
return errorcodes.lookup(e.pgcode[:2])