def get_deprecated_protein_gene_rel(cursor: Cursor, protein: str,
database: str, config: dict) -> str:
select_database(cursor, database)
find_db_query = config['queries']['id_mapping'].format(protein)
cursor.execute(find_db_query)
old_db = cursor.fetchall()
try:
select_database(cursor, old_db[0][0])
except pymysql.err.InternalError:
return ''
except IndexError:
return ''
find_gene_query = config['queries']['protein2gene'].format(protein)
try:
cursor.execute(find_gene_query)
except pymysql.err.ProgrammingError:
find_gene_query = config['queries']['p2g_two'].format(protein)
cursor.execute(find_gene_query)
query_res = cursor.fetchall()
if len(query_res) > 1:
raise ValueError("Ambiguous protein to gene "
"mapping for {} {}".format(protein, query_res))
elif len(query_res) == 0:
raise ValueError("No mapping for protein {}".format(protein))
return query_res[0][0]
def get_deprecated_protein_gene_rel(cursor: Cursor, protein: str,
database: str, config: dict) -> str:
select_database(cursor, database)
find_db_query = config['queries']['id_mapping'].format(protein)
cursor.execute(find_db_query)
old_db = cursor.fetchall()
try:
select_database(cursor, old_db[0][0])
except pymysql.err.InternalError:
return ''
except IndexError:
return ''
find_gene_query = config['queries']['protein2gene'].format(protein)
try:
cursor.execute(find_gene_query)
except pymysql.err.ProgrammingError:
find_gene_query = config['queries']['p2g_two'].format(protein)
cursor.execute(find_gene_query)
query_res = cursor.fetchall()
if len(query_res) > 1:
raise ValueError("Ambiguous protein to gene "
"mapping for {} {}".format(protein, query_res))
elif len(query_res) == 0:
raise ValueError("No mapping for protein {}".format(protein))
return query_res[0][0]
def get_database_users(cursor: Cursor, database: str) -> List[str]:
"""
Look up all users with access to the given database.
"""
query(cursor, "SELECT User FROM mysql.db WHERE Host = %s AND Db = %s",
HOST, database.replace("_", "\\_"))
return [db[0] for db in cursor.fetchall()]
def get_user_databases(cursor: Cursor, user: str) -> List[str]:
"""
Look up all databases that the given user has access to.
"""
query(cursor, "SELECT Db FROM mysql.db WHERE User = %s AND Host = %s",
user, HOST)
return [db[0].replace("\\_", "_") for db in cursor.fetchall()]
def get_products(cursor: Cursor) -> tuple:
"""
Returns all stored product ids in DB
:param cursor: connection cursor to DB
:return: all product ids
"""
cursor.execute("SELECT `id` FROM `{}` ".format(Product.__tablename__))
return cursor.fetchall()
def get_users(cursor: Cursor, *names: str) -> List[str]:
"""
Look up existing users by name.
"""
if not names:
return []
query(cursor, "SELECT User FROM mysql.user WHERE User IN %s", names)
return [user[0] for user in cursor.fetchall()]
def fetch_data(cid: int, my_cursor: Cursor, mongo_db: Database):
ret = {}
# customer
my_cursor.execute("select * from channel_customer where id = %s", cid)
customer = my_cursor.fetchone()
if not customer:
return None
name = customer['company']
my_cursor.execute(
"select * from channel_contact where customer_id = %s limit 1", cid)
# email
contact = my_cursor.fetchone()
buyer_in_mongo = None
if contact:
email = contact['email']
else:
email = None
if not email:
collection = mongo_db[BUYERS_COLLECTION]
buyer_in_mongo = collection.find_one({'full_name': name})
email = fetch_email_from_mongo(mongo_db, buyer_in_mongo)
if not email:
return None
# product
my_cursor.execute(
"select product_content from channel_product where customer_id = %s",
cid)
db_products = my_cursor.fetchall()
if db_products:
products = [x['product_content'] for x in db_products]
else:
if not buyer_in_mongo:
collection = mongo_db[BUYERS_COLLECTION]
buyer_in_mongo = collection.find_one({'full_name': name})
if buyer_in_mongo:
products = fetch_products_from_mongo(mongo_db, buyer_in_mongo)
else:
products = None
ret['customer_id'] = customer['id']
ret['country'] = customer['country']
ret['name'] = name
ret['industry'] = customer['industry']
ret['email'] = email
ret['flag6c_grade'] = customer['flag6c_grade']
if products:
products = list(set(products))
elif products is None:
products = []
ret['products'] = products
ret['today_max'] = 0
ret['life_max'] = 0
ret['replied'] = 0
ret['unsubscribe'] = 0
ret['email_valid'] = 0
return ret
def _get_token_ctr_by_entity_id(self, cursor: Cursor, token_idx_lookup):
cursor.execute(
'select e.id as entity_id, left(p.content, 2000) as text from entities e join pages p on e.text = p.title'
)
entity_desc_bow = {}
for row in cursor.fetchall():
tokens = parse_text_for_tokens(row['text'])
text_idxs = [to_idx(token_idx_lookup, token) for token in tokens]
entity_desc_bow[row['entity_id']] = dict(Counter(text_idxs))
return entity_desc_bow
def get_product_offers(cursor: Cursor, pid: int) -> tuple:
"""
Returns all offers for a product
:param cursor: connection cursor to DB
:param pid: product id
:return: all offers for a product formatted to dict with columns names
"""
fields = Offer.fields()
cursor.execute("SELECT {} FROM `{}` WHERE `product_id` = {}".format(
",".join(fields), Offer.__tablename__, pid))
return tuple(dict(zip(fields, row)) for row in cursor.fetchall())
def get_xref_protein_gene_rel(cursor: Cursor, protein: str, database: str,
config: dict, taxon: str) -> str:
select_database(cursor, database)
find_gene_query = \
config['queries']['protein2xref'].format("{}.{}".format(taxon, protein))
cursor.execute(find_gene_query)
query_res = cursor.fetchall()
if len(query_res) > 1:
logger.warn("Ambiguous protein to gene "
"mapping for {} {}".format(protein, query_res))
gene = None
elif len(query_res) == 0:
logger.warn("No mapping for {}".format(protein))
gene = None
else:
gene = query_res[0][1]
return gene
def get_xref_protein_gene_rel(cursor: Cursor, protein: str,
database: str, config: dict, taxon: str) -> str:
select_database(cursor, database)
find_gene_query = \
config['queries']['protein2xref'].format("{}.{}".format(taxon, protein))
cursor.execute(find_gene_query)
query_res = cursor.fetchall()
if len(query_res) > 1:
logger.warn("Ambiguous protein to gene "
"mapping for {} {}".format(protein, query_res))
gene = None
elif len(query_res) == 0:
logger.warn("No mapping for {}".format(protein))
gene = None
else:
gene = query_res[0][1]
return gene
def sql_fetch_json(cursor: cursors.Cursor):
"""
Convert the pymysql SELECT result to json format
:param cursor:
:return:
"""
keys = []
for column in cursor.description:
keys.append(column[0])
key_number = len(keys)
json_data = []
for row in cursor.fetchall():
item = dict()
for q in range(key_number):
item[keys[q]] = row[q]
json_data.append(item)
return json_data
def get_user_grants(cursor: Cursor, user: str) -> List[str]:
"""
Look up all grants that the given user has.
"""
try:
query(cursor, "SHOW GRANTS FOR %s@%s", user, HOST)
except DatabaseError as ex:
if ex.args[0] == ER.NONEXISTING_GRANT:
return []
else:
raise
databases: List[str] = []
for grant in cursor.fetchall():
match = re.match(r"GRANT (.+) ON (?:\*|(['`\"])(.*?)\2)\.\*", grant[0])
if match:
if "ALL PRIVILEGES" in match.group(1).split(", "):
database = match.group(3) or "*"
databases.append(database.replace("\\_", "_"))
else:
LOG.warning("Ignoring non-parsable grant: %r", grant)
return databases
def _does_table_exist(cursor: Cursor, schema: Optional[str], table: str) -> bool:
schema_str = f"TABLE_SCHEMA = '{schema}' AND" if schema else ""
cursor.execute(f"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE " f"{schema_str} TABLE_NAME = '{table}'")
return len(cursor.fetchall()) > 0 # type: ignore
import pymysql
from pymysql.cursors import Cursor, SSCursor
from common.config import db_config
connection = pymysql.connect(**db_config)
# try:
# with connection.cursor() as cursor:
# cursor.execute('select * from fund')
# result = cursor.fetchone()
# print(result)
# finally:
# connection.close()
cursor = Cursor(connection)
cursor.execute('insert into fund(name) values(%s)', ('测试基金3', ))
connection.commit()
connection.close()
result = cursor.fetchall()
print(result)
def get_matched_databases(cursor: Cursor, like: str = "%") -> List[str]:
"""
Fetch names of all databases matching the given pattern.
"""
query(cursor, "SHOW DATABASES LIKE %s", like)
return [db[0] for db in cursor.fetchall()]