<commit_before>'''
=========================
Infusionsoft Experiment
=========================


[![Build Status](https://travis-ci.org/BMJHayward/infusionsoft_xpmt.svg?branch=master)](https://travis-ci.org/BMJHayward/infusionsoft_xpmt)

API wrapper for Infusionsoft CRM. Infusionsoft a.k.a. 'IS' from here on. Intended usespecific reporting not found in

For target reports see classes inheriting from LocalDB, Query and Extract in dataserv.py.

DESCRIPTION
=============

Extract, transform, load data from IS, send to excel, csv, pandas, matplotlib, numpy etc.
This project will keep to the stdlib where ever possible to minimise dependencies, simplify deployment in several environme


dataserv.py is the main file of interest for the moment. this may be broken up in futuremore classes are ad

TODO:
========

+ Update tests - remove duplication
+ use pandas or matplotlib for dataviz
+ base method for common statistics
+ base method to return dict of common statistics
+ base method to connect to DB, do query, fetchall(), return and close DB
+ DONE: Remove hardcoded database names, e.g. dataserv.db
    + 7 places: 117, 142, 165, 358, 498, 560, 578
+ DONE: possible Report() class for inidividual reports to inherit from
+ DONE: complete methods to compare datetime objects
+ DONE: LeadtimeToSale() to output useful table of date(1stpurchase-1stcontact)
+ DONE: create file of functions to call _basequery() with different args
+ DONE:refactor CostSaleLeadource to return table-like object with column names as headers
'''
import glob
import sys
import os, os.path
import sqlite3
import csv
import time
import statistics
from datetime import datetime, timedelta, date
from infusionsoft.library import Infusionsoft
import json
import locale
import pickle
from collections import OrderedDict
from shutil import move

RAW_DATA_DIR = 'rawdata'
RESULT_DATA_DIR = 'resultdata'
DB_DIR = 'databases'

try:
    os.mkdir( RAW_DATA_DIR )
except FileExistsError as fee:
    print('using existing folder: ', fee)

try:
    os.mkdir( RESULT_DATA_DIR )
except FileExistsError as fee:
    print('using existing folder: ', fee)

try:
    os.mkdir( DB_DIR )
except FileExistsError as fee:
    print('using existing folder: ', fee)

class LocalDB:
    ''' Methods for operating on local sqlite database.
        Would like report classes to be able use either Query or LocalDB in the same way. Maybe.
    '''
    @staticmethod
    def sendto_sqlite(query_array, newtable, db='dataserv.db'):
        '''Use sqlite3 module to output to local DB. Saves API calls. Using text datatypes
        in tables to avoid type conversion for datetime objects.
        '''

        conn = sqlite3.connect(db, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
        c = conn.cursor()

        if isinstance(query_array, dict):
            create_table = 'CREATE TABLE ' + newtable + ' (key, value);'
            c.execute(create_table)
            insert_into_table = 'INSERT INTO ' + newtable + ' values (?,?);'
            for item in query_array.items():
                c.execute(insert_into_table, (item[0], str(item[1])))

        elif isinstance(query_array, list):
            create_table = 'CREATE TABLE ' + newtable + str(tuple(query_array.pop(0))) + ' ;'
            c.execute(create_table)
            questionmarks = '('+''.join(['?,' for i in range(len(query_array[0])-1)])+'?)'
            insert_into_table = 'INSERT INTO ' + newtable + ' values ' + questionmarks + ';'
            c.executemany(insert_into_table, query_array)

        else:
            raise TypeError('Need to pass list or dict')

        conn.commit()
        conn.close()

    @staticmethod
    def sendto_json(query_array, filename):
        '''Use json to store entire query as json file.'''

        with open(filename, 'x') as file:
            for item in query_array:
                json.dumps(item, file)

    @staticmethod
    def get_db_table(db_name, db_table):
        conn = sqlite3.connect(db_name)
        c = conn.cursor()
        c.execute('SELECT * FROM {}'.format(db_table))
        db_tbl = c.fetchall()

        return db_tbl

    @staticmethod
    def get_db_column(dbname, dbtbl, dbcol):
        conn = sqlite3.connect(dbname)
        cur = conn.cursor()
        cur.execute('SELECT {0} FROM {1}'.format(dbcol, dbtbl))
        returncolumn = cur.fetchall()

        return returncolumn

    @staticmethod
    def get_csv(filename):
        ''' Give local csv file as string, returns a list of lists of that file. '''
        csvdata = []
        with open(filename, newline = '', encoding='utf-8') as csvfile:
            dialect = csv.Sniffer().sniff(csvfile.read(1024))
            csvfile.seek(0)
            reader = csv.reader(csvfile, dialect, delimiter = ',')
            try:
                csvdata.extend([entry for entry in reader])<commit_msg>comment out csvfile.seek(0) for linux tests<commit_after>'''
=========================
Infusionsoft Experiment
=========================


[![Build Status](https://travis-ci.org/BMJHayward/infusionsoft_xpmt.svg?branch=master)](https://travis-ci.org/BMJHayward/infusionsoft_xpmt)

API wrapper for Infusionsoft CRM. Infusionsoft a.k.a. 'IS' from here on. Intended usespecific reporting not found in

For target reports see classes inheriting from LocalDB, Query and Extract in dataserv.py.

DESCRIPTION
=============

Extract, transform, load data from IS, send to excel, csv, pandas, matplotlib, numpy etc.
This project will keep to the stdlib where ever possible to minimise dependencies, simplify deployment in several environme


dataserv.py is the main file of interest for the moment. this may be broken up in futuremore classes are ad

TODO:
========

+ Update tests - remove duplication
+ use pandas or matplotlib for dataviz
+ base method for common statistics
+ base method to return dict of common statistics
+ base method to connect to DB, do query, fetchall(), return and close DB
+ DONE: Remove hardcoded database names, e.g. dataserv.db
    + 7 places: 117, 142, 165, 358, 498, 560, 578
+ DONE: possible Report() class for inidividual reports to inherit from
+ DONE: complete methods to compare datetime objects
+ DONE: LeadtimeToSale() to output useful table of date(1stpurchase-1stcontact)
+ DONE: create file of functions to call _basequery() with different args
+ DONE:refactor CostSaleLeadource to return table-like object with column names as headers
'''
import glob
import sys
import os, os.path
import sqlite3
import csv
import time
import statistics
from datetime import datetime, timedelta, date
from infusionsoft.library import Infusionsoft
import json
import locale
import pickle
from collections import OrderedDict
from shutil import move

RAW_DATA_DIR = 'rawdata'
RESULT_DATA_DIR = 'resultdata'
DB_DIR = 'databases'

try:
    os.mkdir( RAW_DATA_DIR )
except FileExistsError as fee:
    print('using existing folder: ', fee)

try:
    os.mkdir( RESULT_DATA_DIR )
except FileExistsError as fee:
    print('using existing folder: ', fee)

try:
    os.mkdir( DB_DIR )
except FileExistsError as fee:
    print('using existing folder: ', fee)

class LocalDB:
    ''' Methods for operating on local sqlite database.
        Would like report classes to be able use either Query or LocalDB in the same way. Maybe.
    '''
    @staticmethod
    def sendto_sqlite(query_array, newtable, db='dataserv.db'):
        '''Use sqlite3 module to output to local DB. Saves API calls. Using text datatypes
        in tables to avoid type conversion for datetime objects.
        '''

        conn = sqlite3.connect(db, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
        c = conn.cursor()

        if isinstance(query_array, dict):
            create_table = 'CREATE TABLE ' + newtable + ' (key, value);'
            c.execute(create_table)
            insert_into_table = 'INSERT INTO ' + newtable + ' values (?,?);'
            for item in query_array.items():
                c.execute(insert_into_table, (item[0], str(item[1])))

        elif isinstance(query_array, list):
            create_table = 'CREATE TABLE ' + newtable + str(tuple(query_array.pop(0))) + ' ;'
            c.execute(create_table)
            questionmarks = '('+''.join(['?,' for i in range(len(query_array[0])-1)])+'?)'
            insert_into_table = 'INSERT INTO ' + newtable + ' values ' + questionmarks + ';'
            c.executemany(insert_into_table, query_array)

        else:
            raise TypeError('Need to pass list or dict')

        conn.commit()
        conn.close()

    @staticmethod
    def sendto_json(query_array, filename):
        '''Use json to store entire query as json file.'''

        with open(filename, 'x') as file:
            for item in query_array:
                json.dumps(item, file)

    @staticmethod
    def get_db_table(db_name, db_table):
        conn = sqlite3.connect(db_name)
        c = conn.cursor()
        c.execute('SELECT * FROM {}'.format(db_table))
        db_tbl = c.fetchall()

        return db_tbl

    @staticmethod
    def get_db_column(dbname, dbtbl, dbcol):
        conn = sqlite3.connect(dbname)
        cur = conn.cursor()
        cur.execute('SELECT {0} FROM {1}'.format(dbcol, dbtbl))
        returncolumn = cur.fetchall()

        return returncolumn

    @staticmethod
    def get_csv(filename):
        ''' Give local csv file as string, returns a list of lists of that file. '''
        csvdata = []
        with open(filename, newline = '', encoding='utf-8') as csvfile:
            dialect = csv.Sniffer().sniff(csvfile.read(1024))
            # csvfile.seek(0)
            reader = csv.reader(csvfile, dialect, delimiter = ',')
            try:
                csvdata.extend([entry for entry in reader])
{'fraction_non_alphanumeric': 0.1083966331348799, 'fraction_numerical': 0.009135701088072264, 'mean_word_length': 3.170804794520548, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 4, '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': '3898262', 'n_tokens_mistral': 2945, 'n_tokens_neox': 2788, 'n_words': 952}