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_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_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 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 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_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_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 create_offers(cursor: Cursor, product_id: int, offers: list):
"""
Creates the new found offers from api
:param cursor: connection cursor to DB
:param product_id: product id from DB
:param offers: list of offers that will be created
"""
o_fields = Offer.fields()
values = []
for offer in offers:
offer["product_id"] = product_id
values.append(tuple(int(offer[key]) for key in o_fields))
placeholder = ["%s" for _ in o_fields]
cursor.executemany(
"INSERT INTO `{}` ({}) VALUES ({})".format(Offer.__tablename__,
", ".join(o_fields),
", ".join(placeholder)),
values)
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 connect_to_data_base(self):
try:
self.connection = Connection(host='127.0.0.1',
user='******',
password='******',
database='tunedune_db',
port=3306)
except DatabaseError:
Ex_Handler.call('Error on creating connection')
return None
self.db_cursor = Cursor(self.connection)
def update_offers(cursor: Cursor, product_id: int, offers: list):
"""
Creates query to update data in DB
:param cursor: connection cursor to DB
:param product_id: product id
:param offers: list of offers that will be updated
"""
values = []
for offer in offers:
sets = []
for key in offer:
sets.append(offer[key])
values.append(sets)
# because product_id is specific for DB it needs to be removed
placeholder = [
"{} = %s".format(field) for field in Offer.fields(("product_id", ))
]
query = """
UPDATE `{}` SET {} WHERE id = {}
""".format(Offer.__tablename__, ", ".join(placeholder),
product_id).strip()
cursor.executemany(query, values)
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
with open('./files/2.txt', 'rb') as f:
html = f.read().decode('utf8')
f.close()
soup = BeautifulSoup(html, 'html.parser')
f_codes = soup.find('table', id='oTable').tbody.find_all('td', 'bzdm') # 基金代码
ret = ()
for f_code in f_codes:
ret += ({
'f_code': f_code.get_text()
, 'f_name': f_code.next_sibling.find('a').get_text()
, 'nav': f_code.next_sibling.next_sibling.get_text()
, 'accnav': f_code.next_sibling.next_sibling.next_sibling.get_text()
, 'updated': datetime.now().isoformat(' ', 'seconds')
},)
print(ret)
import pymysql
from pymysql.cursors import Cursor, SSCursor
from common.config import db_config
connection = pymysql.connect(**db_config)
cursor = Cursor(connection)
cursor.executemany("""insert into fund(f_code,f_name,nav,accnav,updated)
values(%(f_code)s,%(f_name)s,%(nav)s,%(accnav)s,%(updated)s)
ON duplicate KEY UPDATE updated=%(updated)s,nav=%(nav)s,accnav=%(accnav)s""", ret)
connection.commit()
connection.close()
def setup_schema_data(cursor: pc.Cursor, queries: t.List[t.List]):
for query, params in queries:
cursor.executemany(query, params)
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)
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()]
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)
fCodes = soup.find("table", id="oTable").tbody.find_all("td", "bzdm") # 基金编码
result = ()
for fCode in fCodes:
result += ({"fcode": fCode.get_text()
, "fname": fCode.next_sibling.find("a").get_text()
, "NAV": fCode.next_sibling.next_sibling.get_text()
, "ACCNAV": fCode.next_sibling.next_sibling.next_sibling.get_text(),
"updatetime": datetime.now().isoformat(sep=' ', timespec="seconds")}
,)
print(result)
import pymysql
from pymysql.cursors import Cursor, SSCursor
from common.config import dbconfig
connection = pymysql.connect(**dbconfig)
cursor = Cursor(connection)
# cursor.execute("sql")
# result = cursor.fetchall()
cursor.executemany("""
insert into myfund(fcode, fname, NAV, ACCNAV, updatetime)
values(%(fcode)s,%(fname)s,%(NAV)s,%(ACCNAV)s,%(updatetime)s)
ON duplicate KEY UPDATE `updatetime`=%(updatetime)s, NAV=%(NAV)s, ACCNAV=%(ACCNAV)s
""", result)
connection.commit()
connection.close()
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)
