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_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 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)