<s> '''<STR_LIT>''' <EOL> from __future__ import absolute_import <EOL> import logging <EOL> import os <EOL> import sqlite3 <EOL> import sys <EOL> import dataproperty <EOL> import pathvalidate <EOL> import six <EOL> from six . moves import range <EOL> import simplesqlite <EOL> from . sqlquery import SqlQuery <EOL> class SimpleSQLite ( object ) : <EOL> """<STR_LIT>""" <EOL> @ property <EOL> def database_path ( self ) : <EOL> """<STR_LIT>""" <EOL> return self . __database_path <EOL> @ property <EOL> def connection ( self ) : <EOL> """<STR_LIT>""" <EOL> return self . __connection <EOL> @ property <EOL> def mode ( self ) : <EOL> """<STR_LIT>""" <EOL> return self . __mode <EOL> def __init__ ( <EOL> self , database_path , mode = "<STR_LIT:a>" , <EOL> is_create_table_config = True , profile = False ) : <EOL> self . __initialize_connection ( ) <EOL> self . __is_profile = profile <EOL> self . __is_create_table_config = is_create_table_config <EOL> self . connect ( database_path , mode ) <EOL> def __del__ ( self ) : <EOL> self . close ( ) <EOL> def __exit__ ( self , exc_type , exc_value , traceback ) : <EOL> self . close ( ) <EOL> def is_connected ( self ) : <EOL> """<STR_LIT>""" <EOL> try : <EOL> self . check_connection ( ) <EOL> except simplesqlite . NullDatabaseConnectionError : <EOL> return False <EOL> return True <EOL> def check_connection ( self ) : <EOL> """<STR_LIT>""" <EOL> if self . connection is None : <EOL> raise simplesqlite . NullDatabaseConnectionError ( <EOL> "<STR_LIT>" ) <EOL> if dataproperty . is_empty_string ( self . database_path ) : <EOL> raise simplesqlite . NullDatabaseConnectionError ( <EOL> "<STR_LIT>" ) <EOL> def connect ( self , database_path , mode = "<STR_LIT:a>" ) : <EOL> """<STR_LIT>""" <EOL> self . close ( ) <EOL> if mode == "<STR_LIT:r>" : <EOL> self . __verify_sqlite_db_file ( database_path ) <EOL> elif mode in [ "<STR_LIT:w>" , "<STR_LIT:a>" ] : <EOL> self . __validate_db_path ( database_path ) <EOL> else : <EOL> raise ValueError ( "<STR_LIT>" + mode ) <EOL> if database_path == simplesqlite . MEMORY_DB_NAME : <EOL> self . __database_path = database_path <EOL> else : <EOL> self . __database_path = os . path . realpath ( database_path ) <EOL> self . __connection = sqlite3 . connect ( database_path ) <EOL> self . __mode = mode <EOL> if mode != "<STR_LIT:w>" : <EOL> return <EOL> for table in self . get_table_name_list ( ) : <EOL> self . drop_table ( table ) <EOL> def execute_query ( self , query , caller = None ) : <EOL> """<STR_LIT>""" <EOL> import time <EOL> self . check_connection ( ) <EOL> if dataproperty . is_empty_string ( query ) : <EOL> return None <EOL> if self . __is_profile : <EOL> exec_start_time = time . time ( ) <EOL> try : <EOL> result = self . connection . execute ( query ) <EOL> except sqlite3 . OperationalError : <EOL> _ , e , _ = sys . exc_info ( ) <EOL> if caller is None : <EOL> caller = logging . getLogger ( ) . findCaller ( ) <EOL> file_path , line_no , func_name = caller [ : <NUM_LIT:3> ] <EOL> message_list = [ <EOL> "<STR_LIT>" % ( <EOL> file_path , line_no , func_name ) , <EOL> "<STR_LIT>" % ( query ) , <EOL> "<STR_LIT>" % ( e ) , <EOL> "<STR_LIT>" % ( self . database_path ) , <EOL> ] <EOL> raise sqlite3 . OperationalError ( os . linesep . join ( message_list ) ) <EOL> if self . __is_profile : <EOL> self . __dict_query_count [ query ] = ( <EOL> self . __dict_query_count . get ( query , <NUM_LIT:0> ) + <NUM_LIT:1> ) <EOL> elapse_time = time . time ( ) - exec_start_time <EOL> self . __dict_query_totalexectime [ query ] = ( <EOL> self . __dict_query_totalexectime . get ( query , <NUM_LIT:0> ) + elapse_time ) <EOL> return result <EOL> def select ( self , select , table_name , where = None , extra = None ) : <EOL> """<STR_LIT>""" <EOL> self . verify_table_existence ( table_name ) <EOL> query = SqlQuery . make_select ( select , table_name , where , extra ) <EOL> return self . execute_query ( query , logging . getLogger ( ) . findCaller ( ) ) <EOL> def insert ( self , table_name , insert_record ) : <EOL> """<STR_LIT>""" <EOL> self . validate_access_permission ( [ "<STR_LIT:w>" , "<STR_LIT:a>" ] ) <EOL> self . verify_table_existence ( table_name ) <EOL> query = SqlQuery . make_insert ( table_name , self . __to_record ( <EOL> self . get_attribute_name_list ( table_name ) , insert_record ) ) <EOL> self . execute_query ( query , logging . getLogger ( ) . findCaller ( ) ) <EOL> def insert_many ( self , table_name , insert_record_list ) : <EOL> """<STR_LIT>""" <EOL> self . validate_access_permission ( [ "<STR_LIT:w>" , "<STR_LIT:a>" ] ) <EOL> self . verify_table_existence ( table_name ) <EOL> if dataproperty . is_empty_list_or_tuple ( insert_record_list ) : <EOL> return <EOL> record_list = self . __to_data_matrix ( <EOL> self . get_attribute_name_list ( table_name ) , insert_record_list ) <EOL> query = SqlQuery . make_insert ( <EOL> table_name , record_list [ <NUM_LIT:0> ] , is_insert_many = True ) <EOL> try : <EOL> self . connection . executemany ( query , record_list ) <EOL> except sqlite3 . OperationalError : <EOL> _ , e , _ = sys . exc_info ( ) <EOL> caller = logging . getLogger ( ) . findCaller ( ) <EOL> file_path , line_no , func_name = caller [ : <NUM_LIT:3> ] <EOL> raise sqlite3 . OperationalError ( <EOL> "<STR_LIT>" % ( <EOL> file_path , line_no , func_name ) + <EOL> "<STR_LIT>" % ( query ) + <EOL> "<STR_LIT>" % ( str ( e ) ) + <EOL> "<STR_LIT>" % ( self . database_path ) + <EOL> "<STR_LIT>" % ( record_list [ : <NUM_LIT:2> ] ) <EOL> ) <EOL> def update ( self , table_name , set_query , where = None ) : <EOL> """<STR_LIT>""" <EOL> self . validate_access_permission ( [ "<STR_LIT:w>" , "<STR_LIT:a>" ] ) <EOL> self . verify_table_existence ( table_name ) <EOL> query = SqlQuery . make_update ( table_name , set_query , where ) <EOL> return self . execute_query ( query , logging . getLogger ( ) . findCaller ( ) ) <EOL> def get_total_changes ( self ) : <EOL> """<STR_LIT>""" <EOL> self . check_connection ( ) <EOL> return self . connection . total_changes <EOL> def get_value ( self , select , table_name , where = None , extra = None ) : <EOL> """<STR_LIT>""" <EOL> self . verify_table_existence ( table_name ) <EOL> query = SqlQuery . make_select ( select , table_name , where , extra ) <EOL> result = self . execute_query ( query , logging . getLogger ( ) . findCaller ( ) ) <EOL> if result is None : <EOL> return None <EOL> fetch = result . fetchone ( ) <EOL> if fetch is None : <EOL> return None <EOL> return fetch [ <NUM_LIT:0> ] <EOL> def get_table_name_list ( self ) : <EOL> """<STR_LIT>""" <EOL> self . check_connection ( ) <EOL> query = "<STR_LIT>" <EOL> result = self . execute_query ( query , logging . getLogger ( ) . findCaller ( ) ) <EOL> if result is None : <EOL> return [ ] <EOL> return self . __get_list_from_fetch ( result . fetchall ( ) ) <EOL> def get_attribute_name_list ( self , table_name ) : <EOL> """<STR_LIT>""" <EOL> self . verify_table_existence ( table_name ) <EOL> query = "<STR_LIT>" % ( table_name ) <EOL> result = self . execute_query ( query , logging . getLogger ( ) . findCaller ( ) ) <EOL> return self . __get_list_from_fetch ( result . description ) <EOL> def get_attribute_type_list ( self , table_name ) : <EOL> """<STR_LIT>""" <EOL> self . verify_table_existence ( table_name ) <EOL> attribute_name_list = self . get_attribute_name_list ( table_name ) <EOL> query = "<STR_LIT>" % ( <EOL> "<STR_LIT:U+002C>" . join ( [ <EOL> "<STR_LIT>" % ( SqlQuery . to_attr_str ( attribute ) ) <EOL> for attribute in attribute_name_list ] ) , <EOL> table_name ) <EOL> result = self . execute_query ( query , logging . getLogger ( ) . findCaller ( ) ) <EOL> return result . fetchone ( ) <EOL> def get_profile ( self , profile_count = <NUM_LIT:50> ) : <EOL> """<STR_LIT>""" <EOL> from collections import namedtuple <EOL> TN_SQL_PROFILE = "<STR_LIT>" <EOL> value_matrix = [ <EOL> [ query , execute_time , self . __dict_query_count . get ( query , <NUM_LIT:0> ) ] <EOL> for query , execute_time <EOL> in six . iteritems ( self . __dict_query_totalexectime ) <EOL> ] <EOL> attribute_name_list = ( "<STR_LIT>" , "<STR_LIT>" , "<STR_LIT:count>" ) <EOL> con_tmp = simplesqlite . connect_sqlite_db_mem ( ) <EOL> try : <EOL> con_tmp . create_table_with_data ( <EOL> TN_SQL_PROFILE , <EOL> attribute_name_list , <EOL> data_matrix = value_matrix ) <EOL> except ValueError : <EOL> return [ ] <EOL> try : <EOL> result = con_tmp . select ( <EOL> select = "<STR_LIT>" % attribute_name_list , <EOL> table_name = TN_SQL_PROFILE , <EOL> extra = "<STR_LIT>" % ( <EOL> "<STR_LIT>" , "<STR_LIT>" , profile_count ) ) <EOL> except sqlite3 . OperationalError : <EOL> return [ ] <EOL> if result is None : <EOL> return [ ] <EOL> SqliteProfile = namedtuple ( <EOL> "<STR_LIT>" , "<STR_LIT:U+0020>" . join ( attribute_name_list ) ) <EOL> return [ SqliteProfile ( * profile ) for profile in result . fetchall ( ) ] <EOL> def get_sqlite_master ( self ) : <EOL> """<STR_LIT>""" <EOL> self . check_connection ( ) <EOL> old_row_factory = self . connection . row_factory <EOL> self . connection . row_factory = sqlite3 . Row <EOL> sqlite_master_list = [ ] <EOL> result = self . execute_query ( "<STR_LIT>" ) <EOL> for item in result . fetchall ( ) : <EOL> sqlite_master_list . append ( <EOL> dict ( [ [ key , item [ key ] ] for key in item . keys ( ) ] ) ) <EOL> self . connection . row_factory = old_row_factory <EOL> return sqlite_master_list <EOL> def has_table ( self , table_name ) : <EOL> """<STR_LIT>""" <EOL> try : <EOL> simplesqlite . validate_table_name ( table_name ) <EOL> except ValueError : <EOL> return False <EOL> return table_name in self . get_table_name_list ( ) <EOL> def has_attribute ( self , table_name , attribute_name ) : <EOL> """<STR_LIT>""" <EOL> self . verify_table_existence ( table_name ) <EOL> if dataproperty . is_empty_string ( attribute_name ) : <EOL> return False <EOL> return attribute_name in self . get_attribute_name_list ( table_name ) <EOL> def has_attribute_list ( self , table_name , attribute_name_list ) : <EOL> """<STR_LIT>""" <EOL> if dataproperty . is_empty_list_or_tuple ( attribute_name_list ) : <EOL> return False <EOL> not_exist_field_list = [ <EOL> attribute_name <EOL> for attribute_name in attribute_name_list <EOL> if not self . has_attribute ( table_name , attribute_name ) <EOL> ] <EOL> if len ( not_exist_field_list ) > <NUM_LIT:0> : <EOL> return False <EOL> return True <EOL> def verify_table_existence ( self , table_name ) : <EOL> """<STR_LIT>""" <EOL> simplesqlite . validate_table_name ( table_name ) <EOL> if self . has_table ( table_name ) : <EOL> return <EOL> raise simplesqlite . TableNotFoundError ( <EOL> "<STR_LIT>" % ( table_name , self . database_path ) ) <EOL> def verify_attribute_existence ( self , table_name , attribute_name ) : <EOL> """<STR_LIT>""" <EOL> self . verify_table_existence ( table_name ) <EOL> if self . has_attribute ( table_name , attribute_name ) : <EOL> return <EOL> raise simplesqlite . AttributeNotFoundError ( <EOL> "<STR_LIT>" % ( <EOL> attribute_name , table_name ) ) <EOL> def drop_table ( self , table_name ) : <EOL> """<STR_LIT>""" <EOL> self . validate_access_permission ( [ "<STR_LIT:w>" , "<STR_LIT:a>" ] ) <EOL> if self . has_table ( table_name ) : <EOL> query = "<STR_LIT>" % ( table_name ) <EOL> self . execute_query ( query , logging . getLogger ( ) . findCaller ( ) ) <EOL> self . commit ( ) <EOL> def create_table ( self , table_name , attribute_description_list ) : <EOL> """<STR_LIT>""" <EOL> self . validate_access_permission ( [ "<STR_LIT:w>" , "<STR_LIT:a>" ] ) <EOL> table_name = table_name . strip ( ) <EOL> if self . has_table ( table_name ) : <EOL> return True <EOL> query = "<STR_LIT>" % ( <EOL> table_name , "<STR_LIT:U+002CU+0020>" . join ( attribute_description_list ) ) <EOL> if self . execute_query ( query , logging . getLogger ( ) . findCaller ( ) ) is None : <EOL> return False <EOL> return True <EOL> def create_index ( self , table_name , attribute_name ) : <EOL> """<STR_LIT>""" <EOL> self . verify_table_existence ( table_name ) <EOL> self . validate_access_permission ( [ "<STR_LIT:w>" , "<STR_LIT:a>" ] ) <EOL> index_name = "<STR_LIT>" % ( <EOL> SqlQuery . sanitize ( table_name ) , SqlQuery . sanitize ( attribute_name ) ) <EOL> query = "<STR_LIT>" % ( <EOL> index_name , SqlQuery . to_table_str ( table_name ) , attribute_name ) <EOL> self . execute_query ( query , logging . getLogger ( ) . findCaller ( ) ) <EOL> def create_index_list ( self , table_name , attribute_name_list ) : <EOL> """<STR_LIT>""" <EOL> self . validate_access_permission ( [ "<STR_LIT:w>" , "<STR_LIT:a>" ] ) <EOL> if dataproperty . is_empty_list_or_tuple ( attribute_name_list ) : <EOL> return <EOL> for attribute in attribute_name_list : <EOL> self . create_index ( table_name , attribute ) <EOL> def create_table_with_data ( <EOL> self , table_name , attribute_name_list , data_matrix , <EOL> index_attribute_list = ( ) ) : <EOL> """<STR_LIT>""" <EOL> simplesqlite . validate_table_name ( table_name ) <EOL> self . validate_access_permission ( [ "<STR_LIT:w>" , "<STR_LIT:a>" ] ) <EOL> if dataproperty . is_empty_list_or_tuple ( data_matrix ) : <EOL> raise ValueError ( "<STR_LIT>" % ( <EOL> table_name , "<STR_LIT:U+002CU+0020>" . join ( attribute_name_list ) ) ) <EOL> data_matrix = self . __to_data_matrix ( attribute_name_list , data_matrix ) <EOL> self . __verify_value_matrix ( attribute_name_list , data_matrix ) <EOL> strip_index_attribute_list = list ( <EOL> set ( attribute_name_list ) . intersection ( set ( index_attribute_list ) ) ) <EOL> attr_description_list = [ ] <EOL> for col , value_type in sorted ( <EOL> six . iteritems ( self . __get_column_valuetype ( data_matrix ) ) ) : <EOL> attr_name = attribute_name_list [ col ] <EOL> attr_description_list . append ( <EOL> "<STR_LIT>" % ( attr_name , value_type ) ) <EOL> self . create_table ( table_name , attr_description_list ) <EOL> self . insert_many ( table_name , data_matrix ) <EOL> self . create_index_list ( table_name , strip_index_attribute_list ) <EOL> self . commit ( ) <EOL> def create_table_from_csv ( <EOL> self , csv_path , table_name = "<STR_LIT>" , <EOL> attribute_name_list = [ ] , <EOL> delimiter = "<STR_LIT:U+002C>" , quotechar = '<STR_LIT:">' , encoding = "<STR_LIT:utf-8>" ) : <EOL> """<STR_LIT>""" <EOL> import csv <EOL> csv_reader = csv . reader ( <EOL> open ( csv_path , "<STR_LIT:r>" ) , delimiter = delimiter , quotechar = quotechar ) <EOL> data_matrix = [ <EOL> [ <EOL> six . b ( data ) . decode ( encoding , "<STR_LIT:ignore>" ) <EOL> if not dataproperty . is_float ( data ) else data <EOL> for data in row <EOL> ] <EOL> for row in csv_reader <EOL> ] <EOL> if dataproperty . is_empty_list_or_tuple ( attribute_name_list ) : <EOL> header_list = data_matrix [ <NUM_LIT:0> ] <EOL> if any ( [ <EOL> dataproperty . is_empty_string ( header ) for header in header_list <EOL> ] ) : <EOL> raise ValueError ( <EOL> "<STR_LIT>" <EOL> "<STR_LIT>" ) <EOL> data_matrix = data_matrix [ <NUM_LIT:1> : ] <EOL> else : <EOL> header_list = attribute_name_list <EOL> if dataproperty . is_empty_string ( table_name ) : <EOL> table_name = os . path . splitext ( os . path . basename ( csv_path ) ) [ <NUM_LIT:0> ] <EOL> self . create_table_with_data ( table_name , header_list , data_matrix ) <EOL> def rollback ( self ) : <EOL> """<STR_LIT>""" <EOL> try : <EOL> self . check_connection ( ) <EOL> except simplesqlite . NullDatabaseConnectionError : <EOL> return <EOL> self . connection . rollback ( ) <EOL> def commit ( self ) : <EOL> """<STR_LIT>""" <EOL> try : <EOL> self . check_connection ( ) <EOL> except simplesqlite . NullDatabaseConnectionError : <EOL> return <EOL> self . connection . commit ( ) <EOL> def close ( self ) : <EOL> """<STR_LIT>""" <EOL> try : <EOL> self . check_connection ( ) <EOL> except simplesqlite . NullDatabaseConnectionError : <EOL> return <EOL> self . commit ( ) <EOL> self . connection . close ( ) <EOL> self . __initialize_connection ( ) <EOL> @ staticmethod <EOL> def __validate_db_path ( database_path ) : <EOL> if dataproperty . is_empty_string ( database_path ) : <EOL> raise ValueError ( "<STR_LIT>" ) <EOL> if database_path == simplesqlite . MEMORY_DB_NAME : <EOL> return <EOL> pathvalidate . validate_filename ( os . path . basename ( database_path ) ) <EOL> def __verify_sqlite_db_file ( self , database_path ) : <EOL> """<STR_LIT>""" <EOL> self . __validate_db_path ( database_path ) <EOL> if not os . path . isfile ( os . path . realpath ( database_path ) ) : <EOL> raise IOError ( "<STR_LIT>" + database_path ) <EOL> connection = sqlite3 . connect ( database_path ) <EOL> connection . close ( ) <EOL> @ staticmethod <EOL> def __verify_value_matrix ( field_list , value_matrix ) : <EOL> """<STR_LIT>""" <EOL> miss_match_idx_list = [ ] <EOL> for list_idx in range ( len ( value_matrix ) ) : <EOL> if len ( field_list ) == len ( value_matrix [ list_idx ] ) : <EOL> continue <EOL> miss_match_idx_list . append ( list_idx ) <EOL> if len ( miss_match_idx_list ) == <NUM_LIT:0> : <EOL> return <EOL> sample_miss_match_list = value_matrix [ miss_match_idx_list [ <NUM_LIT:0> ] ] <EOL> raise ValueError ( <EOL> "<STR_LIT>" + <EOL> "<STR_LIT>" % ( len ( field_list ) , str ( field_list ) ) + <EOL> "<STR_LIT>" % ( <EOL> len ( miss_match_idx_list ) , len ( value_matrix ) ) + <EOL> "<STR_LIT>" % ( <EOL> miss_match_idx_list [ <NUM_LIT:0> ] , <EOL> len ( sample_miss_match_list ) , str ( sample_miss_match_list ) ) <EOL> ) <EOL> @ staticmethod <EOL> def __get_list_from_fetch ( result ) : <EOL> """<STR_LIT>""" <EOL> return [ record [ <NUM_LIT:0> ] for record in result ] <EOL> def __initialize_connection ( self ) : <EOL> self . __database_path = None <EOL> self . __connection = None <EOL> self . __cursur = None <EOL> self . __mode = None <EOL> self . __dict_query_count = { } <EOL> self . __dict_query_totalexectime = { } <EOL> def validate_access_permission ( self , valid_permission_list ) : <EOL> """<STR_LIT>""" <EOL> self . check_connection ( ) <EOL> if dataproperty . is_empty_string ( self . mode ) : <EOL> raise ValueError ( "<STR_LIT>" ) <EOL> if self . mode not in valid_permission_list : <EOL> raise IOError ( str ( valid_permission_list ) ) <EOL> def __get_column_valuetype ( self , data_matrix ) : <EOL> """<STR_LIT>""" <EOL> TYPENAME_TABLE = { <EOL> dataproperty . Typecode . INT : "<STR_LIT>" , <EOL> dataproperty . Typecode . FLOAT : "<STR_LIT>" , <EOL> dataproperty . Typecode . STRING : "<STR_LIT>" , <EOL> } <EOL> prop_extractor = dataproperty . PropertyExtractor ( ) <EOL> prop_extractor . data_matrix = data_matrix <EOL> col_prop_list = prop_extractor . extract_column_property_list ( ) <EOL> return dict ( [ <EOL> [ col , TYPENAME_TABLE [ col_prop . typecode ] ] <EOL> for col , col_prop in enumerate ( col_prop_list ) <EOL> ] ) <EOL> def __convert_none ( self , value ) : <EOL> if value is None : <EOL> return "<STR_LIT>" <EOL> return value <EOL> def __to_record ( self , attr_name_list , value ) : <EOL> try : <EOL> return [ <EOL> self . __convert_none ( value . get ( attr_name ) ) <EOL> for attr_name in attr_name_list <EOL> ] <EOL> except AttributeError : <EOL> pass <EOL> try : <EOL> dict_value = value . _asdict ( ) <EOL> return [ <EOL> self . __convert_none ( dict_value . get ( attr_name ) ) <EOL> for attr_name in attr_name_list <EOL> ] <EOL> except AttributeError : <EOL> pass <EOL> if dataproperty . is_list_or_tuple ( value ) : <EOL> return value <EOL> raise ValueError ( "<STR_LIT>" ) <EOL> def __to_data_matrix ( self , attr_name_list , data_matrix ) : <EOL> return [ <EOL> self . __to_record ( attr_name_list , record ) <EOL> for record in data_matrix <EOL> ] </s>
