<commit_before>"""=============================================================
PipelineUtilities.py - helper functions for CGAT pipelines
=============================================================

:Author: <NAME>
:Release: $Id$
:Date: |today|
:Tags: Python

Purpose
-------

To make excuting database commands and files IO as trivial and (TODO:
robust) as possible possible. Before changing the default behaviour of
any of these functions please discuss!. Better to add new functions
and then deprecate.

.. note::

   This module needs refactoring as it implements
   some functionality found elsewhere such as the
   Database commands and text IO commands.

Code
----


"""
import sqlite3
try:
    import CGATPipelines.Pipeline as P
except AttributeError, OSError:
    pass
import CGAT.IOTools as IOTools
import pickle
from pandas import DataFrame

try:
    PARAMS = P.getParameters()
except:
    PARAMS = {}


# ################### Database Queries ##############
def db_execute(cc, statements):
    '''excute a statement or statements against a cursor'''

    if type(statements) not in (list, tuple):
        statements = [statements]

    for statement in statements:
        cc.execute(statement)


def execute(queries, database=PARAMS.get("database_name", ""), attach=False):
    '''Execute a statement or a  list of statements (sequentially)'''

    dbhandle = sqlite3.connect(database)
    cc = dbhandle.cursor()

    if attach:
        db_execute(cc, attach)

    db_execute(cc, statement)
    cc.close()


def fetch(query, database=PARAMS.get("database_name", ""), attach=False):
    '''Fetch all query results and return'''

    try:
        cc = database.cursor()
    except AttributeError:

        dbhandle = sqlite3.connect(database)
        cc = dbhandle.cursor()

    if attach:
        db_execute(cc, attach)

    sqlresult = cc.execute(query).fetchall()
    cc.close()
    return sqlresult


def fetch_with_names(query,
                     database=PARAMS.get("database_name", ""),
                     attach=False):
    '''Fetch query results and returns them as an array of row arrays, in
       which the first entry is an array of the field names

    '''

    try:
        cc = database.cursor()
    except AttributeError:
        dbhandle = sqlite3.connect(database)
        cc = dbhandle.cursor()

    if attach:
        db_execute(cc, attach)

    sqlresult = cc.execute(query).fetchall()
    data = []
    # http://stackoverflow.com/questions/4147707/
    # python-mysqldb-sqlite-result-as-dictionary
    field_names = [d[0] for d in cc.description]
    data.append([name for name in field_names])
    for record in sqlresult:
        line = [field for field in record]
        data.append(line)

    cc.close()
    return data


def fetch_DataFrame(query,
                    database=PARAMS.get("database_name", ""),
                    attach=False):
    '''Fetch query results and returns them as a pandas dataframe'''

    try:
        cc = database.cursor()
    except AttributeError:
        dbhandle = sqlite3.connect(database)
        cc = dbhandle.cursor()

    if attach:
        if type(attach) is str:
            db_execute(cc, attach)
        elif isinstance(attach, (tuple, list)):
            for attach_statement in attach:
                db_execute(cc, attach_statement)

    sqlresult = cc.execute(query).fetchall()
    cc.close()

    # see http://pandas.pydata.org/pandas-docs/dev/generated/
    # pandas.DataFrame.from_records.html#pandas.DataFrame.from_records
    # this method is design to handle sql_records with proper type
    # conversion

    field_names = [d[0] for d in cc.description]
    pandas_DataFrame = DataFrame.from_records(
        sqlresult,
        columns=field_names)
    return pandas_DataFrame


def write_DataFrame(dataframe,
                    tablename,
                    database=PARAMS.get("database_name", ""),
                    index=False,
                    if_exists='replace'):
    '''write a pandas dataframe to an sqlite db, index on given columns
       index columns given as a string or list eg. "gene_id" or
       ["gene_id", "start"]

    '''

    dbhandle = sqlite3.connect(database)

    dataframe.to_sql(tablename,
                     con=dbhandle,
                     flavor='sqlite',
                     if_exists=if_exists)

    def indexStat(tablename, column):
        istat = ('create index %(tablename)s_%(column)s '
                 'on %(tablename)s(%(column)s)') % locals()
        return istat

    if index:
<commit_msg>Backwards compatibility with old style ini files<commit_after>"""=============================================================
PipelineUtilities.py - helper functions for CGAT pipelines
=============================================================

:Author: <NAME>
:Release: $Id$
:Date: |today|
:Tags: Python

Purpose
-------

To make excuting database commands and files IO as trivial and (TODO:
robust) as possible possible. Before changing the default behaviour of
any of these functions please discuss!. Better to add new functions
and then deprecate.

.. note::

   This module needs refactoring as it implements
   some functionality found elsewhere such as the
   Database commands and text IO commands.

Code
----


"""
import sqlite3
try:
    import CGATPipelines.Pipeline as P
except AttributeError, OSError:
    pass
import CGAT.IOTools as IOTools
import pickle
from pandas import DataFrame

try:
    PARAMS = P.getParameters()
except:
    PARAMS = {}


def getDatabase():
    '''Looks for the location of the database in a list
    of different places in the ini file. If it is not found,
    return "". This allows new database ini style, while
    maintaining backwards compatability. In thoery data_name is
    always present as it is set by Pipeline.py'''

    locations = ["database", "database_name"]

    for location in locations:
        database = PARAMS.get(location, None)
        if database is not None:
            return database

    return ""


# ################### Database Queries ##############
def db_execute(cc, statements):
    '''excute a statement or statements against a cursor'''

    if type(statements) not in (list, tuple):
        statements = [statements]

    for statement in statements:
        cc.execute(statement)


def execute(queries, database=getDatabase(), attach=False):
    '''Execute a statement or a  list of statements (sequentially)'''

    dbhandle = sqlite3.connect(database)
    cc = dbhandle.cursor()

    if attach:
        db_execute(cc, attach)

    db_execute(cc, statement)
    cc.close()


def fetch(query, database=getDatabase(), attach=False):
    '''Fetch all query results and return'''

    try:
        cc = database.cursor()
    except AttributeError:

        dbhandle = sqlite3.connect(database)
        cc = dbhandle.cursor()

    if attach:
        db_execute(cc, attach)

    sqlresult = cc.execute(query).fetchall()
    cc.close()
    return sqlresult


def fetch_with_names(query,
                     database=getDatabase(),
                     attach=False):
    '''Fetch query results and returns them as an array of row arrays, in
       which the first entry is an array of the field names

    '''

    try:
        cc = database.cursor()
    except AttributeError:
        dbhandle = sqlite3.connect(database)
        cc = dbhandle.cursor()

    if attach:
        db_execute(cc, attach)

    sqlresult = cc.execute(query).fetchall()
    data = []
    # http://stackoverflow.com/questions/4147707/
    # python-mysqldb-sqlite-result-as-dictionary
    field_names = [d[0] for d in cc.description]
    data.append([name for name in field_names])
    for record in sqlresult:
        line = [field for field in record]
        data.append(line)

    cc.close()
    return data


def fetch_DataFrame(query,
                    database=getDatabase(),
                    attach=False):
    '''Fetch query results and returns them as a pandas dataframe'''

    try:
        cc = database.cursor()
    except AttributeError:
        dbhandle = sqlite3.connect(database)
        cc = dbhandle.cursor()

    if attach:
        if type(attach) is str:
            db_execute(cc, attach)
        elif isinstance(attach, (tuple, list)):
            for attach_statement in attach:
                db_execute(cc, attach_statement)

    sqlresult = cc.execute(query).fetchall()
    cc.close()

    # see http://pandas.pydata.org/pandas-docs/dev/generated/
    # pandas.DataFrame.from_records.html#pandas.DataFrame.from_records
    # this method is design to handle sql_records with proper type
    # conversion

    field_names = [d[0] for d in cc.description]
    pandas_DataFrame = DataFrame.from_records(
        sqlresult,
        columns=field_names)
    return pandas_DataFrame


def write_DataFrame(dataframe,
                    tablename,
                    database=getDatabase(),
                    index=False,
                    if_exists='replace'):
    '''write a pandas dataframe to an sqlite db, index on given columns
       index columns given as a string or list eg. "gene_id" or
       ["gene_id", "start"]

    '''

    dbhandle = sqlite3.connect(database)

    dataframe.to_sql(tablename,
                     con=dbhandle,
                     flavor='sqlite',
                     if_exists=if_exists)

    def indexStat(tablename, column):
        istat = ('create index %(tablename)s_%(column)s '
                 'on %(tablename)s(%(column)s)') % locals()
        return istat

    if index:

{'fraction_non_alphanumeric': 0.12435884015492515, 'fraction_numerical': 0.0031403747513869987, 'mean_word_length': 2.6299392097264436, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6626154', 'n_tokens_mistral': 2748, 'n_tokens_neox': 2574, 'n_words': 811}