Python pandas.read_sql_table() Examples

def extract(self, query=None):
        with self.engine.connect() as connection:
            if query is not None:
                return pd.read_sql_query(query, connection)

            elif 'query' in self.options['data']:
                return pd.read_sql_query(self.options['data']['query'], connection)

            else:  # table
                return pd.read_sql_table(self.options['data']['table'], connection,
                                         schema=self.options['data']['schema']) 

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 

def read_sqlite_table(db_path, table_name, sel_columns=None):
    """
    Read data from a single table in SQLite3

    :param db_path: string full path to database
    :param table_name: string name of table within database
    :param columns: string of table names to parse
    :return: List of all entries
    """
    engine = sqlalchemy.create_engine(('sqlite:///' + db_path))
    if sel_columns:
        try:
            return pd.read_sql_query('SELECT ' + sel_columns + ' from ' + table_name +';', engine)
        except sqlalchemy.exc.OperationalError:
            try:
                return pd.read_sql_table(table_name, engine, columns=sel_columns.split(', '))
            except KeyError as e:
                logging.error(KeyError(e))
            except ValueError as e:
                logging.error(ValueError(e))
    else:
        return pd.read_sql_table(table_name, engine) 

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

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) 

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) 

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. 

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

def read_sql_table(self, table_name):
        """
        指定したtableのデータフレームを返す
        :param table_name: table名
        :return: Dataframe
        """
        return pd.read_sql_table(table_name=table_name, con=self.engine) 

def __init__(self, path=None):
        if path is None:
            path = "sqlite:///" + os.path.join(os.environ["HOME"], "tmp", "keras_logs.db")
        db_path = path.replace("sqlite:///", "")
        try:
            self.logs = pd.read_sql_table("log", path)
            self.runs = pd.read_sql_table("run", path).rename(columns={"id": "runid"}).sort_values("runid", ascending=False)
            self.df = self.logs.merge(self.runs)
        except ValueError:
            self.runs = pd.DataFrame({"runid":[], "comment":[], "user":[]}) 

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) 

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) 

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) 

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) 

def Dataframefactory(table_name,sep = ',',iotype = 'fs'):
    ##directly return Pandas dataframe
    if iotype == 'fs':
        return(pd.read_csv(nnenv.getResourcePath() + nnenv.getItem(table_name),sep=sep))
    if iotype == 'db':
        return(pd.read_sql_table(table_name=nnenv.getItem(table_name),con=nnenv.getConnectable()))  
    else:
        print('IOtype is only for db or fs')
        raise(Exception) 

def Dataframefactory(table_name,sep = ',',iotype = 'fs'):
    ##directly return Pandas dataframe
    if iotype == 'fs':
        return(pd.read_csv(nnenv.getResourcePath() + nnenv.getItem(table_name),sep=sep))
    if iotype == 'db':
        return(pd.read_sql_table(table_name=nnenv.getItem(table_name),con=nnenv.getConnectable()))  
    else:
        print('IOtype is only for db or fs')
        raise(Exception) 

def Dataframefactory(table_name,sep = ',',iotype = 'fs'):
    ##directly return Pandas dataframe
    if iotype == 'fs':
        return(pd.read_csv(nnenv.getResourcePath() + nnenv.getItem(table_name),sep=sep))
    if iotype == 'db':
        return(pd.read_sql_table(table_name=nnenv.getItem(table_name),con=nnenv.getConnectable()))  
    else:
        print('IOtype is only for db or fs')
        raise(Exception) 

def Dataframefactory(table_name,sep = ',',iotype = 'fs'):
    ##directly return Pandas dataframe
    if iotype == 'fs':
        return(pd.read_csv(nnenv.getResourcePath() + nnenv.getItem(table_name),sep=sep))
    if iotype == 'db':
        return(pd.read_sql_table(table_name=nnenv.getItem(table_name),con=nnenv.getConnectable()))  
    else:
        print('IOtype is only for db or fs')
        raise(Exception) 

def Dataframefactory(table_name,sep = ',',iotype = 'fs'):
    ##directly return Pandas dataframe
    if iotype == 'fs':
        return(pd.read_csv(nnenv.getResourcePath() + nnenv.getItem(table_name),sep=sep))
    if iotype == 'db':
        return(pd.read_sql_table(table_name=nnenv.getItem(table_name),con=nnenv.getConnectable()))  
    else:
        print('IOtype is only for db or fs')
        raise(Exception) 

def Dataframefactory(table_name,sep = ',',iotype = 'fs'):
    ##directly return Pandas dataframe
    if iotype == 'fs':
        return(pd.read_csv(nnenv.getResourcePath() + nnenv.getItem(table_name),sep=sep))
    if iotype == 'db':
        return(pd.read_sql_table(table_name=nnenv.getItem(table_name),con=nnenv.getConnectable()))  
    else:
        print('IOtype is only for db or fs')
        raise(Exception) 

def Dataframefactory(table_name,sep = ',',iotype = 'fs'):
    ##directly return Pandas dataframe
    if iotype == 'fs':
        return(pd.read_csv(nnenv.getResourcePath() + nnenv.getItem(table_name),sep=sep))
    if iotype == 'db':
        return(pd.read_sql_table(table_name=nnenv.getItem(table_name),con=nnenv.getConnectable()))  
    else:
        print('IOtype is only for db or fs')
        raise(Exception) 

def Dataframefactory(table_name,sep = ',',iotype = 'fs',con=nnenv.getConnectable()):
    ##directly return Pandas dataframe
    if iotype == 'fs':
        return(pd.read_csv(nnenv.getResourcePath() + nnenv.getItem(table_name),sep=sep,engine='python'))
    if iotype == 'db':
        return(pd.read_sql_table(table_name=nnenv.getItem(table_name),con=con)) 

def Dataframefactory(table_name,sep = ',',iotype = 'fs'):
    ##directly return Pandas dataframe
    if iotype == 'fs':
        return(pd.read_csv(nnenv.getResourcePath() + nnenv.getItem(table_name),sep=sep))
    if iotype == 'db':
        return(pd.read_sql_table(table_name=nnenv.getItem(table_name),con=nnenv.getConnectable()))  
    else:
        print('IOtype is only for db or fs')
        raise(Exception) 

def Dataframefactory(table_name,sep = ',',iotype = 'fs'):
    ##directly return Pandas dataframe
    if iotype == 'fs':
        return(pd.read_csv(nnenv.getResourcePath() + nnenv.getItem(table_name),sep=sep,encoding="utf-8",dtype="str"))
    if iotype == 'db':
        return(pd.read_sql_table(table_name=nnenv.getItem(table_name),con=nnenv.getConnectable()))  
    else:
        print('IOtype is only for db or fs')
        raise(Exception) 

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) 

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) 

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) 

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) 

def main_loop(coordinator, select=[], lang=None, test_fail=False, restart=False):

    if restart is True:

        latest_start_time = pd.read_sql_table('timetable', coordinator.dbh.engine)
        latest_start_time = latest_start_time['latest_start_time'][0]

        update_query = f"""
                        UPDATE friends
                        SET burned=0
                        WHERE UNIX_TIMESTAMP(timestamp) > {latest_start_time}
                       """
        coordinator.dbh.engine.execute(update_query)
    start_time = time.time()

    pd.DataFrame({'latest_start_time': [start_time]}).to_sql('timetable', coordinator.dbh.engine,
                                                             if_exists='replace')
    collectors = coordinator.start_collectors(select=select,
                                              lang=lang, fail=test_fail, restart=restart,
                                              retries=4)
    stdout.write("\nstarting {} collectors\n".format(len(collectors)))
    stdout.flush()

    i = 0
    timeout = 3600

    for instance in collectors:
        instance.join(timeout=timeout)
        if instance.is_alive():
            raise RuntimeError(f"Thread {instance.name} took longer than {timeout} seconds \
to finish.")
        if instance.err is not None:
            raise instance.err
        i += 1
        stdout.write(f"Thread {instance.name} joined. {i} collector(s) finished\n")
        stdout.flush() 

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) 

def load_annotation(ref_db_path):
    """
    Load the reference annotation table
    :param ref_db_path: path to reference genome database. Must have table Annotation.__tablename__
    :return: DataFrame
    """
    engine = create_engine('sqlite:///' + ref_db_path)
    df = pd.read_sql_table(Annotation.__tablename__, engine)
    return df 

def load_alignment_evaluation(db_path):
    """
    Loads the transMap alignment evaluation table
    :param db_path: path to genome database
    :return: DataFrame
    """
    engine = create_engine('sqlite:///' + db_path)
    df = pd.read_sql_table(TmEval.__tablename__, engine)
    df = pd.pivot_table(df, index=['TranscriptId', 'AlignmentId'], columns='classifier', values='value')
    return df.reset_index() 

def load_filter_evaluation(db_path):
    """
    Loads the transMap alignment filtering evaluation table
    :param db_path: path to genome database
    :return: DataFrame
    """
    engine = create_engine('sqlite:///' + db_path)
    return pd.read_sql_table(TmFilterEval.__tablename__, engine) 

def load_luigi_stats(db_path, table):
    """
    Loads the luigi stats from the stats db
    :param db_path: path to database
    :return: DataFrame
    """
    engine = create_engine('sqlite:///' + db_path)
    return pd.read_sql_table(table, engine) 

def DBToDF(dbFn, tables= None):
    dFrames = {}
    from sqlalchemy import create_engine
    engine = create_engine('sqlite:///' + dbFn, echo=False)
    if not tables:
        tables = engine.table_names() # read em all
    for t in tables:
        dFrames[t] = pd.read_sql_table(t, engine)
    return dFrames 

def get_df_from_db():

    engine = create_engine(tweet_db_url)

    df = pd.read_sql_table('tweets', engine)

    df['TimeStamp'] = pd.to_datetime(df['TimeStamp'], unit='s')
    df = df.set_index(df['TimeStamp'])
    df = df[~df.index.duplicated(keep='first')].sort_index()

    df = df.loc[df.iloc[df.index.get_loc((df.index[-1] - pd.DateOffset(hours=1)), method='nearest')]['TimeStamp']:df.index[-1]]

    return df 

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) 

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) 

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) 

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) 

def get(self, key):
        """Return a DataFrame for a given key (table name)."""
        while True:
            try:
                with self.engine.connect() as conn, conn.begin():
                    data = pd.read_sql_table(key, conn)
                break
            except sqlalchemy.exc.OperationalError:
                sleep(0.001)
        data = data.set_index('index')
        return data.applymap(unserialize) 

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) 

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) 

def check_init_db(self, dbpath):
        engine = create_engine(f'sqlite:///{dbpath}')
        self.assertSetEqual(set(engine.table_names()), set(self.TABLES.keys()))
        for tname, cols in self.TABLES.items():
            df = pd.read_sql_table(tname, engine)
            self.assertSetEqual(set(df.columns), cols)
            if tname not in 'metadata projects'.split():
                self.assertEqual(df.shape[0], 0)
            elif tname == 'metadata':
                self.assertEqual(df.shape[0], 1)
            else:
                self.assertSequenceEqual(df['name'].tolist(), ['default'])
                self.assertSequenceEqual(df['location'].tolist(), [f'{op.dirname(dbpath)}']) 

def test_add_project(self):
        """Test if the add project command works."""
        # nonexistent folder
        os.environ['KEPLER_HOME'] = self.config_dir
        cmd = 'kepler add project -n foo -p /tmp/foo'.split()
        out = check_output(cmd, stderr=STDOUT)
        self.assertTrue(out.rstrip().decode('utf8').endswith(
            'No such directory: /tmp/foo'))

        cmd = 'kepler add project -n foo -p .'.split()
        check_call(cmd)
        engine = get_engine()
        df = pd.read_sql_table('projects', engine)
        p = df[df['name'] == 'foo'].iloc[0]
        self.assertEqual(p['location'], os.getcwd()) 

def get_table_df(self,table,columns=None):
        """
        :param table: string type, db_name.table_name
        :param columns: lit type with string value, like: ['acc_name', 'initial']
        :return: DataFrame type
        """
        if columns:
            return pd.read_sql_table(table, self.engine)
        else:
            return pd.read_sql_table(table, self.engine, columns) 

def get_table_df(self,table,columns=None):
        """
        :param table: string type, db_name.table_name
        :param columns: lit type with string value, like: ['acc_name', 'initial']
        :return: DataFrame type
        """
        if columns:
            return pd.read_sql_table(table, self.engine, columns=columns)
        else:
            return pd.read_sql_table(table, self.engine) 

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) 

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) 

def load_df_from_sqlconnection(
    conn_item: SQLConnection,
    run_params: Dict,
) -> pd.DataFrame:
    """Load a DF from a SQL connection.

    :param conn_item: SQLConnection object with the connection parameters.

    :param password: Password

    :return: Data frame or raise an exception.
    """
    # Get the engine from the DB
    db_engine = create_db_engine(
        conn_item.conn_type,
        conn_item.conn_driver,
        conn_item.db_user,
        run_params['password'],
        conn_item.db_host,
        conn_item.db_name)

    # Try to fetch the data
    data_frame = pd.read_sql_table(run_params['db_table'], db_engine)

    # Strip white space from all string columns and try to convert to
    # datetime just in case
    return _process_object_column(data_frame) 

def load_table(
    table_name: str,
    columns: Optional[List[str]] = None,
    filter_exp: Optional[Dict] = None,
) -> Optional[pd.DataFrame]:
    """Load a Pandas data frame from the SQL DB.

    :param table_name: Table name

    :param columns: Optional list of columns to load (all if NOne is given)

    :param filter_exp: JSON expression to filter a subset of rows

    :return: data frame
    """
    if table_name not in connection.introspection.table_names():
        return None

    if settings.DEBUG:
        logger.debug('Loading table %s', table_name)

    if columns or filter_exp:
        # A list of columns or a filter exp is given
        query, query_fields = get_select_query_txt(
            table_name,
            column_names=columns,
            filter_formula=filter_exp)
        return pd.read_sql_query(
            query,
            OnTaskSharedState.engine,
            params=query_fields)

    # No special fields given, load the whole thing
    return pd.read_sql_table(table_name, OnTaskSharedState.engine) 

def test_readsql_pd_ints(creds_and_setup, data, benchmark):
    # setup
    table_name = "perf_ints"
    to_sql(
        data["ints"], table_name=table_name, creds=creds_and_setup, index=False, if_exists="replace"
    )

    # benchmark!
    benchmark(pd.read_sql_table, table_name=table_name, con=creds_and_setup.engine) 

def run(running_config: RunningConfig):
    target = HDFStore(running_config.fout)
    df = pd.read_sql_table(
        "proxy_logs",
        "sqlite:///mydatabase.sqlite3",
        index_col='id'
    )

    request = json_to_columns(df, 'request')
    request["session"] = df["proxy_session_id"]
    response = json_to_columns(df, 'response')
    response["session"] = df["proxy_session_id"]
    request_headers = request['headers'].apply(pd.Series)
    response_headers = response['headers'].apply(pd.Series)
    request = request.drop("headers", 1)
    request_headers_norm = pd.melt(
        request_headers.reset_index(), id_vars=["id"], var_name="header"
    )
    request_headers_norm = request_headers_norm.dropna()
    request_headers_norm["type"] = "request"
    response = response.drop("headers", 1)
    response_headers_norm = pd.melt(
        response_headers.reset_index(), id_vars=["id"], var_name="header"
    )
    response_headers_norm = response_headers_norm.dropna()
    response_headers_norm["type"] = "response"
    headers_norm = pd.concat([request_headers_norm, response_headers_norm])
    target.put("request", request, format="table", data_columns=True)
    target.put("response", response, format="table", data_columns=True)
    target.put("headers", headers_norm, format="table", data_columns=True)
    target.close() 

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) 

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) 

def table_archiver(server, database, source_table, destination_table, timestamp_column_name='ArchivedDTS'):
    """
    Takes a table and archives a complete copy of it with the addition of a timestamp of when the archive occurred to a
    given destination table on the same database.

    This should build a new table if the table doesn't exist.

    Args:
        server (str): Server name 
        database (str): Database name 
        source_table (str): Source table name 
        destination_table (str): Destination table name 
        timestamp_column_name (str): New timestamp column name 

    Returns:
        (str): A string with details on records archived.
    
    Example usage:

    ```
    from healthcareai.common.table_archiver import table_archiver
    table_archiver('localhost', 'SAM_123', 'RiskScores', 'RiskScoreArchive', 'ArchiveDTS')
    ```
    """
    # Basic input validation
    if type(server) is not str:
        raise HealthcareAIError('Please specify a server address')
    if type(database) is not str:
        raise HealthcareAIError('Please specify a database name')
    if type(source_table) is not str:
        raise HealthcareAIError('Please specify a source table name')
    if type(destination_table) is not str:
        raise HealthcareAIError('Please specify a destination table name')

    start_time = time.time()

    connection_string = 'mssql+pyodbc://{}/{}?driver=SQL+Server+Native+Client+11.0'.format(server, database)

    # Load the table to be archived
    df = pd.read_sql_table(source_table, connection_string)
    number_records_to_add = len(df)

    # Add timestamp to dataframe
    df[timestamp_column_name] = datetime.datetime.fromtimestamp(time.time()).strftime('%Y-%m-%d %H:%M:%S')

    # Save the new dataframe out to the db without the index, appending values
    df.to_sql(destination_table, connection_string, index=False, if_exists='append')

    end_time = time.time()
    delta_time = end_time - start_time
    result = 'Archived {0} records from {1}/{2}/{3} to {4} in {5} seconds'.format(
        number_records_to_add,
        server,
        database,
        source_table,
        destination_table,
        delta_time)

    return result 

def run(self):
        def load_evals(tx_mode):
            """Loads the error tracks from the database"""
            cds_table = tools.sqlInterface.tables['CDS'][tx_mode]['evaluation']
            mrna_table = tools.sqlInterface.tables['mRNA'][tx_mode]['evaluation']
            cds_df = pd.read_sql_table(cds_table.__tablename__, engine).set_index('AlignmentId')
            mrna_df = pd.read_sql_table(mrna_table.__tablename__, engine).set_index('AlignmentId')
            return {'CDS': cds_df, 'mRNA': mrna_df}

        pipeline_args = self.get_pipeline_args()
        track, trackdb = self.output()
        chrom_sizes = GenomeFiles.get_args(pipeline_args, self.genome).sizes
        engine = tools.sqlInterface.create_engine('sqlite:///' + pipeline_args.dbs[self.genome])
        evals = {tx_mode: load_evals(tx_mode) for tx_mode in self.tx_modes}
        consensus_args = Consensus.get_args(pipeline_args, self.genome)
        consensus_gp_info = pd.read_csv(consensus_args.consensus_gp_info, sep='\t',
                                        header=0, na_filter=False).set_index('transcript_id')
        aln_ids = set(consensus_gp_info.alignment_id)
        rows = []
        for aln_id in aln_ids:
            tx_mode = tools.nameConversions.alignment_type(aln_id)
            if tx_mode not in ['transMap', 'augTM', 'augTMR']:
                continue
            mode = 'CDS'
            df = tools.misc.slice_df(evals[tx_mode][mode], aln_id)
            if len(df) == 0:
                mode = 'mRNA'
                df = tools.misc.slice_df(evals[tx_mode][mode], aln_id)
            for tx_id, s in df.iterrows():
                bed = s.tolist()
                bed[3] = '/'.join([tx_id, bed[3], mode])
                rows.append(bed)

        tmp = luigi.LocalTarget(is_tmp=True)
        with tmp.open('w') as tmp_handle:
            tools.fileOps.print_rows(tmp_handle, rows)
        tools.procOps.run_proc(['bedSort', tmp.path, tmp.path])
        with tools.fileOps.TemporaryFilePath() as out_path:
            cmd = ['bedToBigBed', '-type=bed12', '-tab', tmp.path, chrom_sizes, out_path]
            tools.procOps.run_proc(cmd, stderr='/dev/null')
            tools.fileOps.atomic_install(out_path, track.path)


        with trackdb.open('w') as outf:
            outf.write(error_template.format(genome=self.genome, path=os.path.basename(track.path))) 

def package_db_to_csv(session=None, tables='all'):
    """
    This will get the configuration management tables from the database
       and package them to csv files to be read by initialize_db_from_csv

    Parameters
    ----------
    session : object or None
        session on current database. If session is None, a new session
        on the default database is created and used.
    tables: string
        comma-separated list of names of tables to initialize or 'all'.

    Returns
    -------
    list
        list of filenames written
    """
    if session is None:  # pragma: no cover
        db = mc.connect_to_mc_db(None)
        session = db.sessionmaker()

    data_prefix = cm_table_info.data_prefix
    cm_tables = cm_table_info.cm_tables

    if tables == 'all':
        tables_to_write = cm_tables.keys()
    else:
        tables_to_write = tables.split(',')

    print("Writing packaged files to current directory.")
    print("--> If packing from qmaster, be sure to use 'cm_pack.py --go' to "
          "copy, commit and log the change.")
    print("    Note:  this works via the hera_cm_db_updates repo.")
    files_written = []
    for table in tables_to_write:
        data_filename = data_prefix + table + '.csv'
        table_data = pd.read_sql_table(table, session.get_bind())
        print("\tPackaging:  " + data_filename)
        table_data.to_csv(data_filename, index=False)
        files_written.append(data_filename)
    return files_written 

def __init__(self, name, src, na_values=None, exclude_attr_cols=['_tid_'],
            fpath=None, df=None, schema_name=None, table_query=None, db_engine=None):
        """
        :param name: (str) name to assign to dataset.
        :param na_values: (str or list[str]) values to interpret as NULL.
        :param exclude_attr_cols: (list[str]) list of columns to NOT treat as
            attributes during training/learning.
        :param src: (Source) type of source to load from. Note additional
            parameters MUST be provided for each specific source:
                Source.FILE: :param fpath:, read from CSV file
                Source.DF: :param df:, read from pandas DataFrame
                Source.DB: :param db_engine:, read from database table with :param name:
                Source.SQL: :param table_query: and :param db_engine:, use result
                    from :param table_query:

        :param fpath: (str) File path to CSV file containing raw data
        :param df: (pandas.DataFrame) DataFrame contain the raw ingested data
        :param schema_name: (str) Schema used while loading Source.DB
        :param table_query: (str) sql query to construct table from
        :param db_engine: (DBEngine) database engine object
        """
        self.name = name
        self.index_count = 0
        # Copy the list to memoize
        self.exclude_attr_cols = list(exclude_attr_cols)
        self.df = pd.DataFrame()

        if src == Source.FILE:
            if fpath is None:
                raise Exception("ERROR while loading table. File path for CSV file name expected. Please provide <fpath> param.")
            # TODO(richardwu): use COPY FROM instead of loading this into memory
            # TODO(richardwu): No support for numerical values. To be added.
            self.df = pd.read_csv(fpath, dtype=str, na_values=na_values, encoding='utf-8')
            # Normalize the dataframe: drop null columns, convert to lowercase strings, and strip whitespaces.
            for attr in self.df.columns.values:
                if self.df[attr].isnull().all():
                    logging.warning("Dropping the following null column from the dataset: '%s'", attr)
                    self.df.drop(labels=[attr], axis=1, inplace=True)
                    continue
                if attr not in exclude_attr_cols:
                    self.df[attr] = self.df[attr].str.strip().str.lower()
        elif src == Source.DF:
            if df is None:
                raise Exception("ERROR while loading table. Dataframe expected. Please provide <df> param.")
            self.df = df
        elif src == Source.DB:
            if db_engine is None:
                raise Exception("ERROR while loading table. DB connection expected. Please provide <db_engine>.")
            self.df = pd.read_sql_table(name, db_engine.conn, schema=schema_name)
        elif src == Source.SQL:
            if table_query is None or db_engine is None:
                raise Exception("ERROR while loading table. SQL Query and DB connection expected. Please provide <table_query> and <db_engine>.")
            db_engine.create_db_table_from_query(self.name, table_query)
            self.df = pd.read_sql_table(name, db_engine.conn)