Python pandas.read_sql_table方法代碼示例

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql_table [as 別名]
def tradedetails():
    if request.method == "GET":
        id = request.args.get("id")
        # if tradesonly is true then only look for buy and sells
        tradesonly = request.args.get("trades")
        df = pd.read_sql_table("trades", db.engine)
        # Filter only the trades for current user
        df = df[(df.user_id == current_user.username)]
        df = df[(df.trade_reference_id == id)]
        # Filter only buy and sells, ignore deposit / withdraw
        if tradesonly:
            df = df[(df.trade_operation == "B") | (df.trade_operation == "S")]
        # df['trade_date'] = pd.to_datetime(df['trade_date'])
        df.set_index("trade_reference_id", inplace=True)
        df.drop("user_id", axis=1, inplace=True)
        details = df.to_json()
        return details 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql_table [as 別名]
def plants_small_ferc1(pudl_engine):
    """Pull a useful dataframe related to the FERC Form 1 small plants."""
    plants_small_df = (
        pd.read_sql_table("plants_small_ferc1", pudl_engine)
        .drop(['id'], axis="columns")
        .merge(pd.read_sql_table("utilities_ferc1", pudl_engine),
               on="utility_id_ferc1")
        .pipe(pudl.helpers.organize_cols, ['report_year',
                                           'utility_id_ferc1',
                                           'utility_id_pudl',
                                           'utility_name_ferc1',
                                           "plant_name_original",
                                           'plant_name_ferc1',
                                           "record_id"])
    )
    return plants_small_df 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql_table [as 別名]
def fetch_atb_heat_rates(pudl_engine):
    """Get heat rate projections for power plants

    Data is originally from AEO, NREL does a linear interpolation between current and
    final years.

    Parameters
    ----------
    pudl_engine : sqlalchemy.Engine
        A sqlalchemy connection for use by pandas

    Returns
    -------
    DataFrame
        Power plant heat rate data by year with columns:
        ['technology', 'tech_detail', 'basis_year', 'heat_rate']
    """

    heat_rates = pd.read_sql_table("technology_heat_rates_nrelatb", pudl_engine)

    return heat_rates 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql_table [as 別名]
def load_ipm_plant_region_map(pudl_engine):
    """Load the table associating each power plant to an IPM region

    Parameters
    ----------
    pudl_engine : sqlalchemy.Engine
        A sqlalchemy connection for use by pandas

    Returns
    -------
    dataframe
        All plants in the NEEDS database and their associated IPM region. Columns are
        plant_id_eia and region.
    """
    region_map_df = pd.read_sql_table(
        "plant_region_map_epaipm", con=pudl_engine, columns=["plant_id_eia", "region"]
    )

    return region_map_df 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql_table [as 別名]
def load_ownership_eia860(pudl_engine, data_years=[2017]):

    cols = [
        "report_date",
        "utility_id_eia",
        "plant_id_eia",
        "generator_id",
        # "operational_status_code",
        "owner_utility_id_eia",
        "owner_name",
        "owner_state",
        "fraction_owned",
    ]
    ownership = pd.read_sql_table(
        "ownership_eia860", pudl_engine, columns=cols, parse_dates=["report_date"]
    )
    ownership = ownership.loc[ownership["report_date"].dt.year.isin(data_years)]

    return ownership 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql_table [as 別名]
def read_sql_table(
        cls,
        table_name,
        con,
        schema=None,
        index_col=None,
        coerce_float=True,
        parse_dates=None,
        columns=None,
        chunksize=None,
    ):
        ErrorMessage.default_to_pandas("`read_sql_table`")
        return cls.from_pandas(
            pandas.read_sql_table(
                table_name,
                con,
                schema=schema,
                index_col=index_col,
                coerce_float=coerce_float,
                parse_dates=parse_dates,
                columns=columns,
                chunksize=chunksize,
            )
        ) 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql_table [as 別名]
def download_all_stock_history_k_line():
    print('download all stock k-line start')

    try:
        engine = db.get_w_engine()
        df = pd.read_sql_table(STOCK_BASIC_TABLE, engine)
        codes = df[KEY_CODE].tolist()
        print('total stocks:{0}'.format(len(codes)))
        for code in codes:
            download_stock_kline_by_code(code)

        # codes = codes[::-1]
        #codes = r.lrange(INDEX_STOCK_BASIC, 0, -1)
        # pool = ThreadPool(processes=10)
        # pool.map(download_stock_kline_by_code, codes)
        # pool.close()
        # pool.join()

    except Exception as e:
        print(str(e))
    print('download all stock k-line finish') 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql_table [as 別名]
def serialize(self):
        """
        This method performs the parsing of the ORF file and the
        loading into the SQL database.
        """

        self.load_fasta()
        self.query_cache = pd.read_sql_table("query", self.engine, index_col="query_name", columns=["query_name", "query_id"])
        self.query_cache = self.query_cache.to_dict()["query_id"]
        self.initial_cache = (len(self.query_cache) > 0)

        if self.procs == 1:
            self.__serialize_single_thread()
        else:
            try:
                self.__serialize_multiple_threads()
            finally:
                pass 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql_table [as 別名]
def portfolio_tickers_json():
    if request.method == "GET":
        df = pd.read_sql_table("trades", db.engine)
        df = df[(df.user_id == current_user.username)]
        list_of_tickers = df.trade_asset_ticker.unique().tolist()
        try:
            list_of_tickers.remove(current_user.fx())
        except ValueError:
            pass
        return jsonify(list_of_tickers) 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql_table [as 別名]
def transactions_fx():
    # Gets the transaction table and fills with fx information
    # Note that it uses the currency exchange for the date of transaction
    # Get all transactions from db and format
    df = pd.read_sql_table('trades', db.engine)
    df = df[(df.user_id == current_user.username)]
    # df = df[(df.trade_operation == "B") | (df.trade_operation == "S")]
    df['trade_date'] = pd.to_datetime(df['trade_date'])
    df = df.set_index('trade_date')
    # Ignore times in df to merge - keep only dates
    df.index = df.index.floor('d')
    df.index.rename('date', inplace=True)
    # The current fx needs no conversion, set to 1
    df[current_user.fx()] = 1
    # Need to get currencies into the df in order to normalize
    # let's load a list of currencies needed and merge
    list_of_fx = df.trade_currency.unique().tolist()
    # loop through currency list
    for currency in list_of_fx:
        if currency == current_user.fx():
            continue
        # Make a price request
        df[currency] = df.apply(find_fx, axis=1)
    # Now create a cash value in the preferred currency terms
    df['fx'] = df.apply(lambda x: x[x['trade_currency']], axis=1)
    df['cash_value_fx'] = df['cash_value'].astype(float) / df['fx'].astype(float)
    df['trade_fees_fx'] = df['trade_fees'].astype(float) / df['fx'].astype(float)
    df['trade_price_fx'] = df['trade_price'].astype(float) / df['fx'].astype(float)
    return (df) 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql_table [as 別名]
def list_tickers():
    df = pd.read_sql_table('trades', db.engine)
    df = df[(df.user_id == current_user.username)]
    # remove the currencies from tickers
    df['is_currency'] = df['trade_asset_ticker'].apply(is_currency)
    df = df[df['is_currency'] == False]
    return (df.trade_asset_ticker.unique().tolist())


# ---------------- PANDAS HELPER FUNCTION --------------------------
# This is a function to concatenate a function returning multiple columns into
# a dataframe. 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql_table [as 別名]
def account_positions():
    transactions = Trades.query.filter_by(user_id=current_user.username)
    if transactions.count() == 0:
        return render_template("empty.html")
    df = pd.read_sql_table("trades", db.engine)
    df = df[(df.user_id == current_user.username)]
    df["trade_date"] = pd.to_datetime(df["trade_date"])

    account_table = df.groupby(["trade_account", "trade_asset_ticker"
                                ])[["trade_quantity"]].sum()
    # All accounts
    all_accounts = (account_table.query(
        "trade_asset_ticker != '" + current_user.fx() +
        "'").index.get_level_values("trade_account").unique().tolist())
    # Trim the account list only for accounts that currently hold a position
    account_table = account_table[account_table.trade_quantity != 0]
    # Remove accounts with USD only Positions
    account_table = account_table.query("trade_asset_ticker != 'USD'")

    # account_table = account_table['trade_asset_ticker' != 'USD']
    accounts = account_table.index.get_level_values(
        "trade_account").unique().tolist()
    tickers = (account_table.index.get_level_values(
        "trade_asset_ticker").unique().tolist())
    # if 'USD' in tickers:
    #     tickers.remove('USD')

    return render_template(
        "account_positions.html",
        title="Account Positions",
        accounts=accounts,
        tickers=tickers,
        account_table=account_table,
        all_accounts=all_accounts,
    ) 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql_table [as 別名]
def test_readonly_axis_blosc_to_sql(self):
        # GH11880
        if not _BLOSC_INSTALLED:
            pytest.skip('no blosc')
        if not self._SQLALCHEMY_INSTALLED:
            pytest.skip('no sqlalchemy')
        expected = DataFrame({'A': list('abcd')})
        df = self.encode_decode(expected, compress='blosc')
        eng = self._create_sql_engine("sqlite:///:memory:")
        df.to_sql('test', eng, if_exists='append')
        result = pandas.read_sql_table('test', eng, index_col='index')
        result.index.names = [None]
        assert_frame_equal(expected, result) 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql_table [as 別名]
def test_readonly_axis_zlib_to_sql(self):
        # GH11880
        if not _ZLIB_INSTALLED:
            pytest.skip('no zlib')
        if not self._SQLALCHEMY_INSTALLED:
            pytest.skip('no sqlalchemy')
        expected = DataFrame({'A': list('abcd')})
        df = self.encode_decode(expected, compress='zlib')
        eng = self._create_sql_engine("sqlite:///:memory:")
        df.to_sql('test', eng, if_exists='append')
        result = pandas.read_sql_table('test', eng, index_col='index')
        result.index.names = [None]
        assert_frame_equal(expected, result) 

# 需要導入模塊: import pandas [as 別名]
# 或者: from pandas import read_sql_table [as 別名]
def read_attrs(db_path, table=Annotation.__tablename__, index_col='TranscriptId'):
    """
    Read the attributes database file into a pandas DataFrame
    :param db_path: path to the attributes database
    :param table: table name. should generally be annotation
    :param index_col: column to index on. should generally be tx_id.
    :return: pandas DataFrame
    """
    engine = create_engine('sqlite:///{}'.format(db_path))
    return pd.read_sql_table(table, engine, index_col=index_col)