def _verify_value_matrix(self): if dataproperty.is_empty_list_or_tuple(self.value_matrix): raise EmptyValueError() if dataproperty.is_empty_list_or_tuple(self.header_list): return for row, value_list in enumerate(self.value_matrix): dict_invalid = {} if len(self.header_list) != len(value_list): erroror_key = "row=%d" % (row) dict_invalid[erroror_key] = ( "expected-col-size=%d, actual= %s" % ( len(value_list), len(self.header_list)) ) if len(dict_invalid) > 0: import os message = [ "_verify_value_matrix: miss match length with header and value", " header: col-size=%d %s" % ( len(self.header_list), self.header_list), " value: %s" % (str(dict_invalid)), ] raise ValueError(os.linesep.join(message))
def _verify_value_matrix(self): if dataproperty.is_empty_list_or_tuple(self.value_matrix): raise EmptyValueError() if dataproperty.is_empty_list_or_tuple(self.header_list): return for row, value_list in enumerate(self.value_matrix): dict_invalid = {} if len(self.header_list) != len(value_list): erroror_key = "row=%d" % (row) dict_invalid[erroror_key] = ( "expected-col-size=%d, actual= %s" % (len(value_list), len(self.header_list))) if len(dict_invalid) > 0: import os message = [ "_verify_value_matrix: miss match length with header and value", " header: col-size=%d %s" % (len(self.header_list), self.header_list), " value: %s" % (str(dict_invalid)), ] raise ValueError(os.linesep.join(message))
def make_insert(cls, table, insert_tuple, is_insert_many=False): """ Make INSERT query. :param str table: Table name of executing the query. :param list/tuple insert_tuple: Insertion data. :param bool is_insert_many: Make query that insert multiple data at once, if the value is |True|. :return: Query of SQLite. :rtype: str :raises ValueError: If ``insert_tuple`` is empty |list|/|tuple|. :raises ValueError: |raises_validate_table_name| """ validate_table_name(table) table = cls.to_table_str(table) if dataproperty.is_empty_list_or_tuple(insert_tuple): raise ValueError("empty insert list/tuple") if is_insert_many: value_list = ['?' for _i in insert_tuple] else: value_list = [ "'%s'" % (value) if isinstance(value, six.string_types) and value != "NULL" else str(value) for value in insert_tuple ] return "INSERT INTO %s VALUES (%s)" % ( table, ",".join(value_list))
def make_insert(cls, table, insert_tuple, is_insert_many=False): """ Make INSERT query. :param str table: Table name of execute query. :param list/tuple insert_tuple: Insertion data. :param bool is_insert_many: ``True`` if inserting multiple data. :return: Query of SQLite. :rtype: str :raises ValueError: If ``insert_tuple`` is empty list/tuple. .. seealso:: :py:func:`validate_table_name() <simplesqlite.validate_table_name>` """ validate_table_name(table) table = cls.to_table_str(table) if dataproperty.is_empty_list_or_tuple(insert_tuple): raise ValueError("empty insert list/tuple") if is_insert_many: value_list = ['?' for _i in insert_tuple] else: value_list = [ "'%s'" % (value) if isinstance(value, six.string_types) and value != "NULL" else str(value) for value in insert_tuple ] return "INSERT INTO %s VALUES (%s)" % (table, ",".join(value_list))
def __write_value_row(self, value_list): if dataproperty.is_empty_list_or_tuple(value_list): return self._write_line(self.char_left_side_row + self.column_delimiter.join(value_list) + self.char_right_side_row)
def has_attribute_list(self, table_name, attribute_name_list): """ :param str table_name: Table name that exists attribute. :param str attribute_name: Attribute name to be tested. :return: ``True`` if the table has the all of the attribute. :rtype: bool .. seealso:: :py:meth:`has_attribute` """ if dataproperty.is_empty_list_or_tuple(attribute_name_list): return False not_exist_field_list = [ attribute_name for attribute_name in attribute_name_list if not self.has_attribute(table_name, attribute_name) ] if len(not_exist_field_list) > 0: return False return True
def to_table_data(self): self._validate_source_data() if dataproperty.is_empty_list_or_tuple(self._loader.header_list): header_list = self._source_data[0] if any([ dataproperty.is_empty_string(header) for header in header_list ]): raise InvalidDataError( "the first line includes empty string item: " "the first line expected to contain header data.") data_matrix = self._source_data[1:] else: header_list = self._loader.header_list data_matrix = self._source_data if len(data_matrix) == 0: raise InvalidDataError( "data row must be greater or equal than one") yield TableData( self._loader.make_table_name(), header_list, data_matrix)
def findFile(search_root_dir_path, re_pattern_text): result = findFileAll( search_root_dir_path, os.path.isfile, re_pattern_text, find_count=1) if dataproperty.is_empty_list_or_tuple(result): return None return result[0]
def __write_value_row(self, value_list): if dataproperty.is_empty_list_or_tuple(value_list): return self._write_line( self.char_left_side_row + self.column_delimiter.join(value_list) + self.char_right_side_row)
def findDirectory(search_root_dir_path, re_pattern, find_count=-1): result = findFileAll( search_root_dir_path, os.path.isdir, re_pattern, find_count=1) if dataproperty.is_empty_list_or_tuple(result): return None return result[0]
def has_attribute_list(self, table_name, attribute_name_list): """ :param str table_name: Table name that exists attribute. :param str attribute_name_list: Attribute names to be tested. :return: |True| if the table has all of the attribute. :rtype: bool :raises simplesqlite.TableNotFoundError: |raises_verify_table_existence| :Examples: .. code:: python from simplesqlite import SimpleSQLite, TableNotFoundError import six table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") con.create_table_with_data( table_name=table_name, attribute_name_list=["attr_a", "attr_b"], data_matrix=[[1, "a"], [2, "b"]]) six.print_(con.has_attribute_list(table_name, ["attr_a"])) six.print_(con.has_attribute_list( table_name, ["attr_a", "attr_b"])) six.print_(con.has_attribute_list( table_name, ["attr_a", "attr_b", "not_existing"])) try: six.print_(con.has_attribute("not_existing", ["attr_a"])) except TableNotFoundError as e: six.print_(e) .. parsed-literal:: True True False 'not_existing' table not found in /tmp/sample.sqlite """ if dataproperty.is_empty_list_or_tuple(attribute_name_list): return False not_exist_field_list = [ attribute_name for attribute_name in attribute_name_list if not self.has_attribute(table_name, attribute_name) ] if len(not_exist_field_list) > 0: return False return True
def create_table_with_data(self, table_name, attribute_name_list, data_matrix, index_attribute_list=()): """ Create table if not exists. And insert data to the created table. :param str table_name: Table name to create. :param list attribute_name_list: Attribute names of the table. :param dict/namedtuple/list/tuple data_matrix: Data to be inserted. :param tuple index_attribute_list: Attribute name list to create index. :raises ValueError: If ``data_matrix`` is empty. .. seealso:: :py:meth:`create_table` :py:meth:`insert_many` :py:meth:`create_index_list` """ validate_table_name(table_name) self.validate_access_permission(["w", "a"]) if dataproperty.is_empty_list_or_tuple(data_matrix): raise ValueError("input data is null: '%s (%s)'" % (table_name, ", ".join(attribute_name_list))) data_matrix = self.__to_data_matrix(attribute_name_list, data_matrix) self.__verify_value_matrix(attribute_name_list, data_matrix) strip_index_attribute_list = list( set(attribute_name_list).intersection(set(index_attribute_list))) attr_description_list = [] table_config_matrix = [] for col, value_type in sorted( six.iteritems(self.__get_column_valuetype(data_matrix))): attr_name = attribute_name_list[col] attr_description_list.append("'%s' %s" % (attr_name, value_type)) table_config_matrix.append([ table_name, attr_name, value_type, attr_name in strip_index_attribute_list, ]) self.__create_table_config(table_config_matrix) self.create_table(table_name, attr_description_list) self.insert_many(table_name, data_matrix) self.create_index_list(table_name, strip_index_attribute_list) self.commit()
def create_table_with_data( self, table_name, attribute_name_list, data_matrix, index_attribute_list=()): """ Create table if not exists. And insert data to the created table. :param str table_name: Table name to create. :param list attribute_name_list: Attribute names of the table. :param dict/namedtuple/list/tuple data_matrix: Data to be inserted. :param tuple index_attribute_list: Attribute name list to create index. :raises ValueError: If ``data_matrix`` is empty. .. seealso:: :py:meth:`create_table` :py:meth:`insert_many` :py:meth:`create_index_list` """ validate_table_name(table_name) self.validate_access_permission(["w", "a"]) if dataproperty.is_empty_list_or_tuple(data_matrix): raise ValueError("input data is null: '%s (%s)'" % ( table_name, ", ".join(attribute_name_list))) data_matrix = self.__to_data_matrix(attribute_name_list, data_matrix) self.__verify_value_matrix(attribute_name_list, data_matrix) strip_index_attribute_list = list( set(attribute_name_list).intersection(set(index_attribute_list))) attr_description_list = [] table_config_matrix = [] for col, value_type in sorted( six.iteritems(self.__get_column_valuetype(data_matrix))): attr_name = attribute_name_list[col] attr_description_list.append( "'%s' %s" % (attr_name, value_type)) table_config_matrix.append([ table_name, attr_name, value_type, attr_name in strip_index_attribute_list, ]) self.__create_table_config(table_config_matrix) self.create_table(table_name, attr_description_list) self.insert_many(table_name, data_matrix) self.create_index_list(table_name, strip_index_attribute_list) self.commit()
def __write_separator_row(self, value_list): if dataproperty.is_empty_list_or_tuple(value_list): return left_cross_point = self.char_cross_point right_cross_point = self.char_cross_point if dataproperty.is_empty_string(self.char_left_side_row): left_cross_point = u"" if dataproperty.is_empty_string(self.char_right_side_row): right_cross_point = u"" self._write_line(left_cross_point + self.char_cross_point.join(value_list) + right_cross_point)
def __write_separator_row(self, value_list): if dataproperty.is_empty_list_or_tuple(value_list): return left_cross_point = self.char_cross_point right_cross_point = self.char_cross_point if dataproperty.is_empty_string(self.char_left_side_row): left_cross_point = u"" if dataproperty.is_empty_string(self.char_right_side_row): right_cross_point = u"" self._write_line( left_cross_point + self.char_cross_point.join(value_list) + right_cross_point)
def _write_header(self): if dataproperty.is_empty_list_or_tuple(self.header_list): return header_format_props = self.format_table.get( self.FormatName.HEADER, self.default_format) header_format = self.__add_format(header_format_props) self.stream.write_row( row=self.first_header_row, col=0, data=self.header_list, cell_format=header_format) for row in range(self.first_header_row, self.last_header_row): self.stream.write_row( row=row, col=0, data=[""] * len(self.header_list), cell_format=header_format)
def create_index_list(self, table_name, attribute_name_list): """ :param str table_name: Table name that exists attribute. :param list attribute_name_list: List of attribute names to create indices. .. seealso:: :py:meth:`.create_index` """ self.validate_access_permission(["w", "a"]) if dataproperty.is_empty_list_or_tuple(attribute_name_list): return for attribute in attribute_name_list: self.create_index(table_name, attribute)
def create_table_with_data( self, table_name, attribute_name_list, data_matrix, index_attribute_list=()): """ Create a table if not exists. And insert data into the created table. :param str table_name: Table name to create. :param list attribute_name_list: List of attribute names of the table. :param data_matrix: Data to be inserted into the table. :type data_matrix: List of |dict|/|namedtuple|/|list|/|tuple| :param tuple index_attribute_list: List of attribute names of create indices. :raises ValueError: If the ``data_matrix`` is empty. .. seealso:: :py:meth:`.create_table` :py:meth:`.insert_many` :py:meth:`.create_index_list` """ validate_table_name(table_name) self.validate_access_permission(["w", "a"]) if dataproperty.is_empty_list_or_tuple(data_matrix): raise ValueError("input data is null: '%s (%s)'" % ( table_name, ", ".join(attribute_name_list))) data_matrix = RecordConvertor.to_record_list( attribute_name_list, data_matrix) self.__verify_value_matrix(attribute_name_list, data_matrix) strip_index_attribute_list = list( set(attribute_name_list).intersection(set(index_attribute_list))) attr_description_list = [] for col, value_type in sorted( six.iteritems(self.__get_column_valuetype(data_matrix))): attr_name = attribute_name_list[col] attr_description_list.append( "'%s' %s" % (attr_name, value_type)) self.create_table(table_name, attr_description_list) self.insert_many(table_name, data_matrix) self.create_index_list(table_name, strip_index_attribute_list) self.commit()
def create_index_list(self, table_name, attribute_name_list): """ :param str table_name: Table name that exists attribute. :param list attribute_name_list: Attribute name list to create index. .. seealso:: :py:meth:`create_index` """ self.validate_access_permission(["w", "a"]) if dataproperty.is_empty_list_or_tuple(attribute_name_list): return for attribute in attribute_name_list: self.create_index(table_name, attribute)
def _preprocess_property(self): if self._preprocessed_property: return self._value_matrix = [] self._column_prop_list = [] self._value_prop_matrix = [] if dataproperty.is_empty_list_or_tuple(self.value_matrix): return self._prop_extractor.header_list = self.header_list self._prop_extractor.data_matrix = self.__value_matrix_org self._column_prop_list = self._prop_extractor.extract_column_property_list() self._value_prop_matrix = self._prop_extractor.extract_data_property_matrix() self._preprocessed_property = True
def _write_header(self): if dataproperty.is_empty_list_or_tuple(self.header_list): return header_format_props = self.format_table.get(self.FormatName.HEADER, self.default_format) header_format = self.__add_format(header_format_props) self.stream.write_row(row=self.first_header_row, col=0, data=self.header_list, cell_format=header_format) for row in range(self.first_header_row, self.last_header_row): self.stream.write_row(row=row, col=0, data=[""] * len(self.header_list), cell_format=header_format)
def insert_many(self, table_name, insert_record_list): """ Execute INSERT query for multiple records. :param str table: Table name of execute query :param dict/namedtuple/list/tuple insert_record: Record to be inserted :raises ValueError: If database connection is invalid. :raises IOError: If open mode is neither ``"w"`` nor ``"a"``. :raises sqlite3.OperationalError: If failed to execute query. .. seealso:: :py:meth:`check_connection` :py:meth:`verify_table_existence` :py:meth:`make_insert() <simplesqlite.SqlQuery.make_insert>` """ self.validate_access_permission(["w", "a"]) self.verify_table_existence(table_name) if dataproperty.is_empty_list_or_tuple(insert_record_list): return record_list = self.__to_data_matrix( self.get_attribute_name_list(table_name), insert_record_list) query = SqlQuery.make_insert( table_name, record_list[0], is_insert_many=True) try: self.connection.executemany(query, record_list) except sqlite3.OperationalError: _, e, _ = sys.exc_info() # for python 2.5 compatibility caller = logging.getLogger().findCaller() file_path, line_no, func_name = caller[:3] raise sqlite3.OperationalError( "%s(%d) %s: failed to execute query:\n" % ( file_path, line_no, func_name) + " query=%s\n" % (query) + " msg='%s'\n" % (str(e)) + " db=%s\n" % (self.database_path) + " records=%s\n" % (record_list[:2]) )
def insert_many(self, table_name, insert_record_list): """ Execute INSERT query for multiple records. :param str table: Table name of executing the query. :param insert_record: Records to be inserted. :type insert_record: |dict|/|namedtuple|/|list|/|tuple| :raises IOError: |raises_write_permission| :raises simplesqlite.NullDatabaseConnectionError: |raises_check_connection| :raises simplesqlite.TableNotFoundError: |raises_verify_table_existence| :raises sqlite3.OperationalError: |raises_operational_error| .. seealso:: :py:meth:`.sqlquery.SqlQuery.make_insert` """ self.validate_access_permission(["w", "a"]) self.verify_table_existence(table_name) if dataproperty.is_empty_list_or_tuple(insert_record_list): return record_list = RecordConvertor.to_record_list( self.get_attribute_name_list(table_name), insert_record_list) query = SqlQuery.make_insert( table_name, record_list[0], is_insert_many=True) try: self.connection.executemany(query, record_list) except sqlite3.OperationalError: _, e, _ = sys.exc_info() # for python 2.5 compatibility caller = logging.getLogger().findCaller() file_path, line_no, func_name = caller[:3] raise sqlite3.OperationalError( "%s(%d) %s: failed to execute query:\n" % ( file_path, line_no, func_name) + " query=%s\n" % (query) + " msg='%s'\n" % (str(e)) + " db=%s\n" % (self.database_path) + " records=%s\n" % (record_list[:2]) )
def _preprocess_property(self): if self._preprocessed_property: return self._value_matrix = [] self._column_prop_list = [] self._value_prop_matrix = [] if dataproperty.is_empty_list_or_tuple(self.value_matrix): return self._prop_extractor.header_list = self.header_list self._prop_extractor.data_matrix = self.__value_matrix_org self._column_prop_list = self._prop_extractor.extract_column_property_list( ) self._value_prop_matrix = self._prop_extractor.extract_data_property_matrix( ) self._preprocessed_property = True
def insert_many(self, table_name, insert_record_list): """ Execute INSERT query for multiple records. :param str table: Table name of execute query :param dict/namedtuple/list/tuple insert_record: Record to be inserted :raises ValueError: If database connection is invalid. :raises IOError: If open mode is neither ``"w"`` nor ``"a"``. :raises sqlite3.OperationalError: If failed to execute query. .. seealso:: :py:meth:`check_connection` :py:meth:`verify_table_existence` :py:meth:`make_insert() <simplesqlite.SqlQuery.make_insert>` """ self.validate_access_permission(["w", "a"]) self.verify_table_existence(table_name) if dataproperty.is_empty_list_or_tuple(insert_record_list): return record_list = self.__to_data_matrix( self.get_attribute_name_list(table_name), insert_record_list) query = SqlQuery.make_insert(table_name, record_list[0], is_insert_many=True) try: self.connection.executemany(query, record_list) except sqlite3.OperationalError: _, e, _ = sys.exc_info() # for python 2.5 compatibility caller = logging.getLogger().findCaller() file_path, line_no, func_name = caller[:3] raise sqlite3.OperationalError( "%s(%d) %s: failed to execute query:\n" % (file_path, line_no, func_name) + " query=%s\n" % (query) + " msg='%s'\n" % (str(e)) + " db=%s\n" % (self.database_path) + " records=%s\n" % (record_list[:2]))
def make_insert(cls, table, insert_tuple, is_insert_many=False): """ Make INSERT query. :param str table: Table name of execute query. :param list/tuple insert_tuple: Insertion data. :param bool is_insert_many: ``True`` if inserting multiple data. :return: Query of SQLite. :rtype: str :raises ValueError: If ``insert_tuple`` is empty list/tuple. .. seealso:: :py:func:`validate_table_name() <simplesqlite.validate_table_name>` """ validate_table_name(table) table = cls.to_table_str(table) if dataproperty.is_empty_list_or_tuple(insert_tuple): raise ValueError("empty insert list/tuple") if is_insert_many: value_list = ['?' for _i in insert_tuple] else: value_list = [ "'%s'" % (value) if isinstance(value, six.string_types) and value != "NULL" else str(value) for value in insert_tuple ] return "INSERT INTO %s VALUES (%s)" % ( table, ",".join(value_list))
def test_normal(self, con): profile_list = con.get_profile() assert dataproperty.is_empty_list_or_tuple(profile_list)
def _write_header(self): if dataproperty.is_empty_list_or_tuple(self.header_list): return super(CsvTableWriter, self)._write_header()
def _verify_header(self): if dataproperty.is_empty_list_or_tuple(self.header_list): raise EmptyHeaderError()
def create_table_from_csv( self, csv_path, table_name="", attribute_name_list=[], delimiter=",", quotechar='"', encoding="utf-8"): """ Create table from a csv file. :param str csv_path: Path to the csv file. :param str table_name: Table name to create (default=""). Use csv file base name as the table name if table_name is empty. :param list attribute_name_list: Attribute names of the table (default=[]). Use first line of the csv file as attribute list if attribute_name_list is empty. :param str delimiter: A one-character string used to separate fields. It defaults to `','`. :param str quotechar: A one-character string used to quote fields containing special characters, such as the delimiter or quotechar, or which contain new-line characters. It defaults to `'"'`. :param str encoding: csv file encoding. It defaults to `'utf-8`` :raises ValueError: .. seealso:: :py:meth:`create_table_with_data` :py:meth:`csv.reader` """ import csv csv_reader = csv.reader( open(csv_path, "r"), delimiter=delimiter, quotechar=quotechar) data_matrix = [ [ six.b(data).decode(encoding, "ignore") if not dataproperty.is_float(data) else data for data in row ] for row in csv_reader ] if dataproperty.is_empty_list_or_tuple(attribute_name_list): header_list = data_matrix[0] if any([ dataproperty.is_empty_string(header) for header in header_list ]): raise ValueError( "the first line include empty string: " "the first line expected to contain header data.") data_matrix = data_matrix[1:] else: header_list = attribute_name_list if dataproperty.is_empty_string(table_name): # use csv filename as a table name if table_name is a empty string. table_name = os.path.splitext(os.path.basename(csv_path))[0] self.create_table_with_data(table_name, header_list, data_matrix)
def create_table_from_csv(self, csv_path, table_name="", attribute_name_list=[], delimiter=",", quotechar='"', encoding="utf-8"): """ Create table from a csv file. :param str csv_path: Path to the csv file. :param str table_name: Table name to create (default=""). Use csv file base name as the table name if table_name is empty. :param list attribute_name_list: Attribute names of the table (default=[]). Use first line of the csv file as attribute list if attribute_name_list is empty. :param str delimiter: A one-character string used to separate fields. It defaults to `','`. :param str quotechar: A one-character string used to quote fields containing special characters, such as the delimiter or quotechar, or which contain new-line characters. It defaults to `'"'`. :param str encoding: csv file encoding. It defaults to `'utf-8`` :raises ValueError: .. seealso:: :py:meth:`create_table_with_data` :py:meth:`csv.reader` """ import csv csv_reader = csv.reader(open(csv_path, "r"), delimiter=delimiter, quotechar=quotechar) data_matrix = [[ six.b(data).decode(encoding, "ignore") if not dataproperty.is_float(data) else data for data in row ] for row in csv_reader] if dataproperty.is_empty_list_or_tuple(attribute_name_list): header_list = data_matrix[0] if any([ dataproperty.is_empty_string(header) for header in header_list ]): raise ValueError( "the first line include empty string: " "the first line expected to contain header data.") data_matrix = data_matrix[1:] else: header_list = attribute_name_list if dataproperty.is_empty_string(table_name): # use csv filename as a table name if table_name is a empty string. table_name = os.path.splitext(os.path.basename(csv_path))[0] self.create_table_with_data(table_name, header_list, data_matrix)
def test_normal(self, con): attribute_name_list, profile_list = con.get_profile() assert dataproperty.is_empty_list_or_tuple(attribute_name_list) assert dataproperty.is_empty_list_or_tuple(profile_list)