def insert_multiple(table, columns_to_insert_lst, insert_values_dict_lst):
"""
Multiple row insert in pg_python
:param table: table to insert into.
:param columns_to_insert_lst: columns value provided.
:param insert_values_dict_lst: values of corresponding columns.
:return:
"""
global crawler_db, print_debug_log, params_map
connection = crawler_db.get_connection()
cursor = crawler_db.get_cursor()
is_pararmeters_correct = check_multiple_insert_param(
columns_to_insert_lst, insert_values_dict_lst)
if not is_pararmeters_correct:
print("ERROR in parameters passsed")
return
command, values = make_postgres_write_multiple_statement(
table, columns_to_insert_lst, insert_values_dict_lst, print_debug_log)
try:
cursor.execute(command, values)
connection.commit()
except Exception as e:
print("Db Cursor Write Error: %s" % e)
crawler_db = Db(params_map)
return False
return True
def update_multiple(table, column_to_update, columns_to_query_lst,
query_values_dict_lst):
"""
Multiple update support in pg_python
:param table: table to update into
:param column_to_update: Single column for set clause
:param columns_to_query_lst: column names for where clause
:param query_values_dict_lst: values for where and Set.
:return:
"""
global crawler_db, print_debug_log, params_map
connection = crawler_db.get_connection()
cursor = crawler_db.get_cursor()
is_pararmeters_correct = check_parameters(column_to_update,
columns_to_query_lst,
query_values_dict_lst)
if not is_pararmeters_correct:
print("ERROR in parameters passsed")
return
command, values = make_postgres_update_multiple_statement(
table, column_to_update, columns_to_query_lst, query_values_dict_lst,
print_debug_log)
try:
cursor.execute(command, values)
connection.commit()
except Exception as e:
print("Db Cursor update_multiple Error: %s" % e)
crawler_db = Db(params_map)
return False
return True
def pg_server(db_name, username, password, host_address, debug=True):
global crawler_db, print_debug_log, params_map
params_map = {
'dbname': db_name,
'user': username,
'password': password,
'host': host_address,
}
crawler_db = Db(params_map)
print_debug_log = debug
return crawler_db
def write_raw(command, values):
"""
:params command, values. Execution commands dirctly for postgres
"""
global crawler_db, print_debug_log, params_map
connection = crawler_db.get_connection()
cursor = crawler_db.get_cursor()
try:
cursor.execute(command, values)
connection.commit()
except Exception as e:
print("Db Cursor Write Error: %s" % e)
crawler_db = Db(params_map)
return False
return True
def update_raw(command):
"""
Update statement in the raw format,
:param command: SQL command
:return: number of rows affected
"""
global crawler_db, print_debug_log, params_map
connection = crawler_db.get_connection()
cursor = crawler_db.get_cursor()
try:
cursor.execute(command)
rowcount = cursor.rowcount
connection.commit()
except Exception as e:
print("Db Cursor Update Error: %s" % e)
crawler_db = Db(params_map)
return -1
return rowcount
def write(table, kv_map):
"""
:param table: String.
:param kv_map: Key values.
:return success_bool:
"""
global crawler_db, print_debug_log, params_map
connection = crawler_db.get_connection()
cursor = crawler_db.get_cursor()
command, values = make_postgres_write_statement(table, kv_map,
print_debug_log)
try:
cursor.execute(command, values)
connection.commit()
except Exception as e:
print("Db Cursor Write Error: %s" % e)
crawler_db = Db(params_map)
return False
return True
def delete(table, where_kv_map):
"""
Delete the rows resulting from the mentined kv map. No limit.
:param table: table name, must be string
:param where_kv_map: the kv map to search for values, all values ARE ANDed.
:return: True or False
"""
global crawler_db, print_debug_log, params_map
connection = crawler_db.get_connection()
cursor = crawler_db.get_cursor()
command, values = make_postgres_delete_statement(table, where_kv_map,
print_debug_log)
try:
cursor.execute(command, values)
connection.commit()
except Exception as e:
print("Db Cursor Delete Error: %s" % e)
crawler_db = Db(params_map)
return False
return True
def update(table, update_kv_map, where_kv_map, clause='='):
"""
:param table: table name, type string
:param update_kv_map: the NEW keyvalue map for values to be updated
:param where_kv_map: the kv map to search for values, all values ARE ANDed.
:return: Success or Failure.
"""
global crawler_db, print_debug_log, params_map
connection = crawler_db.get_connection()
cursor = crawler_db.get_cursor()
command, values = make_postgres_update_statement(table, update_kv_map,
where_kv_map, clause,
print_debug_log)
try:
cursor.execute(command, values)
connection.commit()
except Exception as e:
print("Db Cursor Update Error: %s" % e)
crawler_db = Db(params_map)
return False
return True