def insert_attrvalue_to_pg():
ret = Hive(env=ENV).query("SELECT attrvalue FROM mpintranet.attrvalue_result LIMIT 1")
if len(ret) > 0:
attrvalues = ret[0]["attrvalue"]
rows = PostgreSQL().query("SELECT * FROM text_value WHERE name = 'attrname_attrvalue_columns'")
if len(rows) > 0:
sql = "UPDATE text_value SET value = '{0}' WHERE name = 'attrname_attrvalue_columns'".format(attrvalues)
else:
sql = "INSERT INTO text_value (name, value) VALUES('attrname_attrvalue_columns', '{0}')".format(attrvalues)
PostgreSQL().execute(sql)
def to_db():
with open("vectors.json", encoding="utf8") as f:
d = json.load(f, encoding="utf8")
d = json.dumps(d)
db = PostgreSQL()
sql = """
INSERT INTO json_value
(value, name)
VALUES
(%s, 'word_vector')
"""
db.execute(sql, (d,))
def load_model_from_pg(model_name):
sql = """
SELECT value
FROM text_value
WHERE name = '{model_name}'
""".format(model_name=model_name)
return pickle.loads(PostgreSQL("v2_uat").query(sql)[0].get("value"))
def update_model_to_pg(model_obj, model_name):
sql = """
UPDATE text_value
SET value = %s
WHERE name = '{model_name}'
""".format(model_name=model_name)
PostgreSQL("v2_uat").execute(sql, (pickle.dumps(model_obj),))
def important_csv_to_db():
od = parse(
pandas.read_table("important_tag_data.csv", encoding="utf8", sep=","))
sql = """
INSERT INTO json_value (value, name)
VALUES (%s, 'important_dict')
"""
PostgreSQL().execute(sql, (json.dumps(od), ))
def save_model_to_pg(model_obj, model_name):
sql = """
INSERT INTO text_value
(name, value)
VALUES
('{model_name}', %s)
""".format(model_name=model_name)
PostgreSQL("v2_uat").execute(sql, (pickle.dumps(model_obj),))
def exists_model_in_pg(model_name):
sql = """
SELECT 1
FROM text_value
WHERE name = '{model_name}'
""".format(model_name=model_name)
ret = PostgreSQL("v2_uat").query(sql)
return True if ret else False
def get_attr_pnv():
sql = """
SELECT
DISTINCT attr_value
FROM attr_value
WHERE industry_id = 16
AND attr_name <> '品牌'
"""
return list(map(lambda x: x.get("attr_value"), PostgreSQL(env="v2_uat").query(sql)))
def get_registered_shop():
sql = """
SELECT DISTINCT s.id
FROM elengjing.public.user AS u
JOIN elengjing.public.shop AS s
ON u.shop_id = s.id
WHERE s.is_validated = 't'
"""
return PostgreSQL(env="v2_uat").query(sql)
def get_attribute_meta():
sql = """
SELECT
category_id,
attr_name,
attr_value
FROM attr_value
WHERE industry_id = 16
AND attr_name != '品牌'
"""
return pandas.DataFrame(PostgreSQL().query(sql)).astype(unicode)
def get_important_dict():
return PostgreSQL().query(
"SELECT value FROM json_value WHERE name = 'important_dict'")[0].get(
"value")
def get_essential_dict():
return PostgreSQL().query(
"SELECT value FROM json_value WHERE name = 'essential_dict'")[0].get(
"value")
def get_attr_head():
ret = PostgreSQL().query(
"SELECT value FROM text_value WHERE name = 'attrname_columns'")[0].get(
"value")
return ret.split(",")
def get_dummy_head():
ret = PostgreSQL().query(
"SELECT value FROM text_value WHERE name = 'attrname_attrvalue_columns'"
)[0].get("value")
return list(map(lambda x: x.split("_")[1], ret.split(",")))
def read_from_db():
db = PostgreSQL()
sql = "SELECT value FROM json_value WHERE name = 'word_vector'"
ret = db.query(sql, fetchone=True)[0].get("value")
return ret
def get_all_category():
lines = PostgreSQL("v2_uat").query(
"SELECT id FROM category WHERE industry_id = 16 AND is_leaf = 't'")
return [str(line.get("id")) for line in lines]
def essential_read_db():
sql = "SELECT value FROM json_value WHERE name = 'essential_dict'"
return PostgreSQL().query(sql)[0].get("value")
def get_all_category(industry_id=16):
from mplib.IO import PostgreSQL
sql = "SELECT id FROM category WHERE industry_id = {0} AND is_leaf = 't'".format(
industry_id)
lines = PostgreSQL("v2_uat").query(sql)
return [str(line.get("id")) for line in lines]
def important_read_db():
sql = "SELECT value FROM json_value WHERE name = 'important_dict'"
return PostgreSQL().query(sql)[0].get("value")
def get_word_vector():
ret = PostgreSQL().query(
"SELECT value FROM json_value WHERE name = 'word_vector'")[0].get(
"value")
return {k: numpy.array(v) for k, v in iteritems(ret)}