def _create_table(
df: pd.DataFrame,
cursor: Cursor,
table: str,
schema: str,
mode: str,
index: bool,
dtype: Optional[Dict[str, str]],
varchar_lengths: Optional[Dict[str, int]],
) -> None:
if mode == "overwrite":
_drop_table(cursor=cursor, schema=schema, table=table)
elif _does_table_exist(cursor=cursor, schema=schema, table=table):
return
mysql_types: Dict[str, str] = _data_types.database_types_from_pandas(
df=df,
index=index,
dtype=dtype,
varchar_lengths_default="TEXT",
varchar_lengths=varchar_lengths,
converter_func=_data_types.pyarrow2mysql,
)
cols_str: str = "".join([f"`{k}` {v},\n" for k, v in mysql_types.items()])[:-2]
sql = f"CREATE TABLE IF NOT EXISTS `{schema}`.`{table}` (\n{cols_str})"
_logger.debug("Create table query:\n%s", sql)
cursor.execute(sql)
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 query(cursor: Cursor, sql: str, *args: Union[str, Tuple[str, ...],
Password]) -> bool:
"""
Run a SQL query against a database cursor, and return whether rows were affected.
"""
LOG.debug("Ran query: %r %% %r", sql, args)
cursor.execute(
sql, [str(arg) if isinstance(arg, Password) else arg for arg in args])
return bool(cursor.rowcount)
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_cached_entities(url: str, cursor: Cursor = cursor) -> Set[str]:
hash = hashlib.sha256()
hash.update(url.encode("utf-8"))
hash_result = hash.hexdigest()
get_sql = """SELECT hash, entities FROM opendigitalworld.article WHERE hash='{}'""".format(
hash_result)
cursor.execute(get_sql)
result = cursor.fetchone()
if result is not None:
entities = result[1]
return set(entities.split("|"))
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 delete_offers(cursor: Cursor, product_id: int, offers: list):
"""
Creates query to delete obsolete data in DB
:param cursor: connection cursor to DB
:param product_id: product id
:param offers: list of offers that will be updated
"""
cursor.execute("""
DELETE FROM {} WHERE `id` IN ({}) AND `product_id` = {}
""".format(Offer.__tablename__,
", ".join([str(offer) for offer in offers]),
product_id))
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 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 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 _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
def _drop_table(cursor: Cursor, schema: Optional[str], table: str) -> None:
schema_str = f"`{schema}`." if schema else ""
sql = f"DROP TABLE IF EXISTS {schema_str}`{table}`"
_logger.debug("Drop table query:\n%s", sql)
cursor.execute(sql)
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 select_database(cursor: Cursor, database: str) -> None:
query = "USE {};".format(database)
cursor.execute(query)
def select_database(cursor: Cursor, database: str) -> None:
query = "USE {};".format(database)
cursor.execute(query)
def setup_database_schema(cursor: pc.Cursor, queries: t.List[str]):
for query in queries:
cursor.execute(query)