Python pandas.read_sql方法代碼示例

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql [as 別名]
def _fetch_sql(self, path):
        """
        fetch the information and pricetable from sql, not recommend to use manually,
        just set the fetch label to be true when init the object

        :param path:  engine object from sqlalchemy
        """
        try:
            content = pd.read_sql("xa" + self.code, path)
            pricetable = content.iloc[1:]
            commentl = [float(com) for com in pricetable.comment]
            self.price = pricetable[["date", "netvalue", "totvalue"]]
            self.price["comment"] = commentl
            self.name = json.loads(content.iloc[0].comment)["name"]
        except exc.ProgrammingError as e:
            # print('no saved copy of %s' % self.code)
            raise e 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql [as 別名]
def load_sqlite_table(database_path, table_name):
    """Returns (table, connection). table is a pandas DataFrame."""
    conn = sqlite3.connect(database_path)
    try:
        df = pd.read_sql("SELECT * FROM %s" % table_name, conn)
        #  print("\nLoading %s table from SQLite3 database." % table_name)
    except DatabaseError as e:
        if 'no such table' in e.args[0]:
            print("\nNo such table: %s" % table_name)
            print("Create the table before loading it. " +
                  "Consider using the create_sqlite_table function")
            raise DatabaseError
        else:
            print(e)
            raise Exception("Failed to create %s table. Unknown error." %
                            table_name)
    return df, conn 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql [as 別名]
def fetch_backend(key):
    prefix = ioconf.get("prefix", "")
    key = prefix + key
    backend = ioconf.get("backend")
    path = ioconf.get("path")
    if backend == "csv":
        key = key + ".csv"

    try:
        if backend == "csv":
            df0 = pd.read_csv(os.path.join(path, key))
        elif backend == "sql":
            df0 = pd.read_sql(key, path)
        else:
            raise ValueError("no %s option for backend" % backend)

        return df0

    except (FileNotFoundError, exc.ProgrammingError, KeyError):
        return None 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql [as 別名]
def export_filing_document_search(search_query_id: int, output_file_path: str):
    """
    Export a filing document search to a CSV file.
    :param search_query_id:
    :param output_file_path:
    :return:
    """
    # Local imports
    import django.db
    import pandas

    # Create query string
    query_string = """SELECT f.accession_number, f.date_filed, f.company_id, ci.name, ci.sic, ci.state_location, 
f.form_type, fd.sequence, fd.description, fd.sha1, sqt.term, sqr.count
FROM sec_edgar_searchqueryresult sqr
JOIN sec_edgar_searchqueryterm sqt ON sqt.id = sqr.term_id
JOIN sec_edgar_filingdocument fd ON fd.id = sqr.filing_document_id
JOIN sec_edgar_filing f ON f.id = fd.filing_id
JOIN sec_edgar_companyinfo ci ON ci.company_id = f.company_id AND ci.date = f.date_filed 
WHERE sqr.search_query_id = {0}
ORDER BY f.date_filed, f.company_id
""".format(search_query_id)
    query_df = pandas.read_sql(query_string, django.db.connection)
    query_df.to_csv(output_file_path, encoding="utf-8", index=False) 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql [as 別名]
def fileshare():
    connect_db ()
    try:
        FS_list = dp.read_sql ( 'select "Home Directory", "Profile Path" from UserTB', connection ).drop_duplicates()
        if not FS_list.values.any:
            pass
        else:
            FS_list = FS_list.drop_duplicates()
            FS_list = FS_list.to_string(header=False, index=False)
            FS_list = FS_list.upper()
            FS_list = FS_list.replace("\n", "").replace(" ", "")
            FS_list = FS_list.split("\\")
            uname_list = dp.read_sql ( 'select Name from ComputerTB', connection )
            uname_list = uname_list.to_string ( header=False, index=False )
            uname_list = uname_list.split ()
            l3 = [x for x in FS_list if x in uname_list]
            l3 = filter ( None, l3 )
            final = dp.DataFrame ( l3 )
            final = final.drop_duplicates ()
            final.to_sql("FileServer", connection, index=False, if_exists="replace")
    except ValueError:
        pass 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql [as 別名]
def __execute_sqla(self, exec_str, repl=None):
        if repl is not None:
            exec_str = exec_str.replace('?', '{}').format(
                    *[self.__insert_sanitize(item) for item in repl])

        # DEBUG
        engine = self.__engine
        try:
            res = engine.execute('SELECT * FROM rg_complex_dates;')
        except Exception:
            pass

        try:
            df =  pd.read_sql(exec_str, self.__engine, 
                              parse_dates=self.__parse_datetimes)
            if self.__parse_datetimes:
                utils.fix_pandas_datetimes(df, self.__parse_datetimes)
            return df.to_records(index=False)
        except sqla.exc.ResourceClosedError:
            # Query didn't return results
            return None 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql [as 別名]
def plants_utils_ferc1(pudl_engine):
    """
    Build a dataframe of useful FERC Plant & Utility information.

    Args:
        pudl_engine (sqlalchemy.engine.Engine): Engine for connecting to the
            PUDL database.

    Returns:
        pandas.DataFrame: A DataFrame containing useful FERC Form 1 Plant and
        Utility information.

    """
    pu_df = pd.merge(
        pd.read_sql("plants_ferc1", pudl_engine),
        pd.read_sql("utilities_ferc1", pudl_engine),
        on="utility_id_ferc1")
    return pu_df 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql [as 別名]
def plant_in_service(ferc1_meta, ferc1_table, ferc1_years):
    """Creates a DataFrame of the fields of plant_in_service_ferc1.

    Args:
        ferc1_meta (sa.MetaData): a MetaData object describing the cloned FERC
            Form 1 database
        ferc1_table (str): The name of the FERC 1 database table to read, in
            this case, the plant_in_service_ferc1 table.
        ferc1_years (list): The range of years from which to read data.

    Returns:
        pandas.DataFrame: A DataFrame containing all plant_in_service_ferc1
        records.

    """
    f1_plant_in_srvce = ferc1_meta.tables[ferc1_table]
    f1_plant_in_srvce_select = (
        sa.sql.select([f1_plant_in_srvce])
        .where(f1_plant_in_srvce.c.report_year.in_(ferc1_years))
    )

    return pd.read_sql(f1_plant_in_srvce_select, ferc1_meta.bind) 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql [as 別名]
def get_unique_partitions(env,db,table,auth_dict=auth_dict,custom_auth=False,connector="teradata",partition_key="", partition_type=""):

    env_n, env_dsn, env_short, usr, passw = load_db_info(env, custom_auth=custom_auth)

    sql = ""
    if partition_type == "year":
        sql = f"SELECT DISTINCT EXTRACT(YEAR FROM {partition_key}) as years \
                                from {db}.{table} order by years;"
    elif partition_type == "month":
        sql = f"SELECT DISTINCT EXTRACT(YEAR FROM {partition_key}) as years, \
                                EXTRACT(MONTH FROM {partition_key}) as months \
                                from {db}.{table} order by years, months;"
    else:
        raise Exception("Invalid partition_type: Must either be year or month")

    conn = connect_teradata(env, connector)
    df = pd.read_sql(sql, conn)
    unique_list = []
    if partition_type == "month":
        df["yearmonth"] = df["years"].map(str) + "D" + df["months"].map(str)
        unique_list = df["yearmonth"].tolist()
    elif partition_type == "year":
        unique_list = df["years"].tolist()
    else:
        raise Exception("Invalid partition_type: must be year or month")

    return(unique_list) 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql [as 別名]
def test_all_protein_have_gen(self):

        expected_protein_without_gene = 0
        protein_query = cellphonedb_app.cellphonedb.database_manager.database.session.query(Protein,
                                                                                            Multidata.name).join(
            Multidata)

        protein_df = pd.read_sql(protein_query.statement,
                                 cellphonedb_app.cellphonedb.database_manager.database.engine)
        protein_ids = protein_df['id_protein'].tolist()

        gene_query = cellphonedb_app.cellphonedb.database_manager.database.session.query(
            Gene.protein_id)
        gene_protein_ids = \
            pd.read_sql(gene_query.statement,
                        cellphonedb_app.cellphonedb.database_manager.database.engine)[
                'protein_id'].tolist()

        protein_without_gene = []
        for protein_id in protein_ids:
            if not protein_id in gene_protein_ids:
                protein_without_gene.append(protein_df[protein_df['id_protein'] == protein_id]['name'].iloc[0])

        if len(protein_without_gene) != expected_protein_without_gene:
            app_logger.warning('There are {} Proteins without gene'.format(len(protein_without_gene)))
            app_logger.warning(protein_without_gene)

            unknowed_proteins_without_gene = []
            for protein in protein_without_gene:
                if not protein in KNOWED_PROTEINS_WITHOUT_GENE:
                    unknowed_proteins_without_gene.append(protein)

            if unknowed_proteins_without_gene:
                app_logger.warning(
                    'There are {} unknowed proteins without gene'.format(len(unknowed_proteins_without_gene)))
                app_logger.warning(pd.Series(unknowed_proteins_without_gene).drop_duplicates().tolist())

        self.assertEqual(expected_protein_without_gene, len(protein_without_gene), 'There are Proteins without Gene.') 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql [as 別名]
def test_gene_are_not_duplicated(self):
        query = cellphonedb_app.cellphonedb.database_manager.database.session.query(Gene)
        dataframe = pd.read_sql(query.statement,
                                cellphonedb_app.cellphonedb.database_manager.database.engine)

        duplicated_genes = dataframe[dataframe.duplicated(keep=False)]
        if len(duplicated_genes):
            app_logger.warning(duplicated_genes.sort_values('gene_name').to_csv(index=False))

        self.assertEqual(
            len(duplicated_genes), 0,
            'There are %s not not expected duplicated genes in database. '
            'Please check WARNING_duplicated_genes.csv file' % len(duplicated_genes)) 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql [as 別名]
def get_all(self):
        query = self.database_manager.database.session.query(Interaction)
        interactions = pd.read_sql(query.statement, self.database_manager.database.engine)

        return interactions 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql [as 別名]
def get_interactions_by_multidata_id(self, id):
        """

        :type id: int
        :rtype: pd.DataFrame
        """
        query = self.database_manager.database.session.query(Interaction).filter(
            or_(Interaction.multidata_1_id == int(id), Interaction.multidata_2_id == int(id)))
        result = pd.read_sql(query.statement, self.database_manager.database.engine)

        return result 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql [as 別名]
def get_all_expanded(self, include_gene=True, suffixes=('_1', '_2')):
        interactions_query = self.database_manager.database.session.query(Interaction)

        interactions = pd.read_sql(interactions_query.statement, self.database_manager.database.engine)

        multidata_expanded: pd.DataFrame = self.database_manager.get_repository('multidata').get_all_expanded(
            include_gene)

        multidata_expanded = multidata_expanded.astype({'id_multidata': 'int64'})

        interactions = pd.merge(interactions, multidata_expanded, left_on=['multidata_1_id'], right_on=['id_multidata'])
        interactions = pd.merge(interactions, multidata_expanded, left_on=['multidata_2_id'], right_on=['id_multidata'],
                                suffixes=suffixes)

        return interactions 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql [as 別名]
def get_all_expanded(self):
        protein_multidata_join = Protein.protein_multidata_id == Multidata.id_multidata
        gene_protein_join = Gene.protein_id == Protein.id_protein
        query = self.database_manager.database.session.query(Gene, Protein, Multidata).join(
            Protein, gene_protein_join).join(Multidata, protein_multidata_join)

        result = pd.read_sql(query.statement, self.database_manager.database.session.bind)

        return result