def getActiveSKUs(merchantID):
conn = pbldb.dbConn()
query = "SELECT bfid, item_state FROM pbl_mkl_item_market_state where bfid like '%" + str(
merchantID) + "-%' and item_state='ACTIVE' and market_id='TMALL'"
activeSKUs = pd.read_sql_query(query, conn)
print(activeSKUs)
if (activeSKUs.size == 0):
query = "SELECT bfid, item_state FROM pbl_mkl_item_market_state where bfid like '%" + str(
merchantID
) + "-%' and item_state='MARKET_LISTING_PENDING' and market_id='TMALL'"
listingPendingSKUs = pd.read_sql_query(query, conn)
if (listingPendingSKUs.size == 0):
conn.close()
return False
else:
SSHConn.exec_mpexporter()
query = "SELECT bfid, item_state FROM pbl_mkl_item_market_state where bfid like '%" + str(
merchantID
) + "-%' and item_state='ACTIVE' and market_id='TMALL'"
activeSKUs = pd.read_sql_query(query, conn)
if (activeSKUs.size == 0):
conn.close()
return False
else:
dp.activeSKU = activeSKUs['bfid'][0]
return True
else:
dp.activeSKU = activeSKUs['bfid'][0]
print(dp.activeSKU)
return True
def checkDBEntry():
query = "select * from pbl_running_job where job_name = 'CatSynListing'"
conn = labsdb.dbConn()
df = pd.read_sql_query(query, conn)
row_count = len(df.axes[0])
print(row_count)
conn.close()
return row_count
def cleanDBSDB(merch_to_clean):
# Formating merchant code for delete query
merchName = str(merch_to_clean) + '-' + '%'
# Creating DB Connection
conn_clean = labsdb.dbConn()
# Creating cursors for multipe DB queries
cur_clean_pbl_cat_item_custom_label = conn_clean.cursor()
cur_clean_pbl_cat_item_product_type = conn_clean.cursor()
cur_clean_pbl_cat_item_additional_image = conn_clean.cursor()
cur_clean_pbl_mkl_item_market_state = conn_clean.cursor()
# cur_clean_pbl_cat_item_country_hscode = conn_clean.cursor()
cur_clean_pbl_cat_item_country = conn_clean.cursor()
cur_clean_pbl_cat_item_group = conn_clean.cursor()
cur_clean_pbl_cat_item = conn_clean.cursor()
cur_clean_pbl_running_job = conn_clean.cursor()
# List of queries for DB cleanups
clean_pbl_cat_item_custom_label = "delete from pbl_cat_item_custom_label where bfid like '%s'"
clean_pbl_cat_item_product_type = "delete from pbl_cat_item_product_type where bfid like '%s'"
clean_pbl_cat_item_additional_image = "delete from pbl_cat_item_additional_image where bfid like '%s'"
clean_pbl_mkl_item_market_state = "delete from pbl_mkl_item_market_state where bfid like '%s'"
# clean_pbl_cat_item_country_hscode = "delete from pbl_cat_item_country_hscode where bfid like '%s'"
clean_pbl_cat_item_country = "delete from pbl_cat_item_country where bfid like '%s'"
clean_pbl_cat_item_group = "delete from pbl_cat_item_group where bfid like '%s'"
clean_pbl_cat_item = "delete from pbl_cat_item where bfid like '%s'"
# Query to clean pbl running job table
clean_pbl_running_job = "delete from pbl_running_job where job_name = 'CatSynListing'"
# Executing delete queries on DB
cur_clean_pbl_cat_item_custom_label.execute(
clean_pbl_cat_item_custom_label % merchName)
cur_clean_pbl_cat_item_product_type.execute(
clean_pbl_cat_item_product_type % merchName)
cur_clean_pbl_cat_item_additional_image.execute(
clean_pbl_cat_item_additional_image % merchName)
cur_clean_pbl_mkl_item_market_state.execute(
clean_pbl_mkl_item_market_state % merchName)
# cur_clean_pbl_cat_item_country_hscode.execute(clean_pbl_cat_item_country_hscode % merchName)
cur_clean_pbl_cat_item_country.execute(clean_pbl_cat_item_country %
merchName)
cur_clean_pbl_cat_item_group.execute(clean_pbl_cat_item_group % merchName)
cur_clean_pbl_cat_item.execute(clean_pbl_cat_item % merchName)
cur_clean_pbl_running_job.execute(clean_pbl_running_job)
# Committing the delete queries
conn_clean.commit()
# Closing DB connection
conn_clean.close()
def getSFTPInfo(merchantID):
conn = labsdb.dbConn()
cur = conn.cursor()
query = "select data_feed_host, data_feed_port, data_feed_user, data_feed_password, data_feed_remote_directory from pbl_mrc_merchant where bf_merchant_id =" + "'" + str(
merchantID) + "'"
cur.execute(query)
row = cur.fetchone()
host_from_db = row[0]
port_from_db = row[1]
username_from_db = row[2]
password_from_db = row[3]
remote_directory_db = row[4]
conn.close()
return (host_from_db, port_from_db, username_from_db, password_from_db,
remote_directory_db)
def cleanDBSDB_test(merch_to_clean):
# Formating merchant code for delete query
merchName = str(merch_to_clean) + '-' + '%'
# Creating DB Connection
conn_clean = labsdb.dbConn()
#Creating cursor
cursor = conn_clean.cursor()
#Multiple Queries
clean_pbl_cat_item_custom_label = "delete from pbl_cat_item_custom_label where bfid like '%s'"
clean_pbl_cat_item_product_type = "delete from pbl_cat_item_product_type where bfid like '%s'"
clean_pbl_cat_item_additional_image = "delete from pbl_cat_item_additional_image where bfid like '%s'"
clean_pbl_mkl_item_market_state = "delete from pbl_mkl_item_market_state where bfid like '%s'"
# clean_pbl_cat_item_country_hscode = "delete from pbl_cat_item_country_hscode where bfid like '%s'"
clean_pbl_cat_item_country = "delete from pbl_cat_item_country where bfid like '%s'"
clean_pbl_cat_item_group = "delete from pbl_cat_item_group where bfid like '%s'"
clean_pbl_cat_item = "delete from pbl_cat_item where bfid like '%s'"
# Creating query
clean_merch_data = clean_pbl_cat_item_custom_label
clean_pbl_cat_item_product_type
clean_pbl_cat_item_additional_image
clean_pbl_mkl_item_market_state
clean_pbl_cat_item_country
clean_pbl_cat_item_group
clean_pbl_cat_item
clean_pbl_running_job = "delete from pbl_running_job where job_name = 'CatSynListing'"
# Deleting record from table
cursor.executemany(clean_merch_data, merchName)
cursor.execute(clean_pbl_running_job)
# Committing the delete queries
conn_clean.commit()
# Closing DB connection
conn_clean.close()
def cleanDB(merchID):
# Fetching the merchant from the calling script
merchant_to_query = str(merchID)
# Querying DB to get merchant status
conn = labsdb.dbConn()
cur = conn.cursor()
query = "select status from pbl_mrc_merchant where bf_merchant_id = %s" % merchant_to_query
cur.execute(query)
# Getting merchant status from query result
merch_status = cur.fetchone()
# Calling cleanup function if merchant is enabled else exiting the program
if (str(merch_status[0]) == 'ENABLED'):
cleanDBSDB(merchant_to_query)
# cleanDB(merchant_to_query)
conn.close()
return True
else:
conn.close()
return False
def pbldbConn():
# pbldb.server.start()
pbldbConn = pbldb.dbConn()
yield pbldbConn
pbldbConn.close()