Use in Python

convert_table_file_to_insert_statement

Converts table file (CSV or Excel) to SQL insert statements.

If file doesn't contain row with types as second row - every value is treated as string and will be available in insert statement with single quotes.

If file contain row with types and types_row argument is set to True - types will be used to convert columns to given types.

Parameters:

Name	Type	Description	Default
path_to_file	str	Name of file containing data to be converted to SQL insert statements.	required
output_table	str	Name of table into which data should be inserted.	required
delimiter	Optional[str]	Delimiter of given CSV file.	','
has_types_row	Optional[bool]	If second row of table file contains row with types (from TYPES_MAP). Defaults to False.	False
sheet_name	Optional[str]	If file is Excel - pass sheet name which should be converted into insert statements.	None

Returns:

Type	Description
str	SQL insert statement

Examples:

from table2sql import convert_table_file_to_insert_statement

## some.csv
# a,b,c,d
# int,str,float,sql
# 1,2,3,(SELECT id FROM another.table WHERE name = 'Paul')
# 5,6,7,(SELECT id FROM another.table WHERE name = 'Paul')

inserts = convert_table_file_to_insert_statement(
    path_to_file="some.csv",
    output_table="test.table",
    has_types_row=True,
)

print(inserts)
# INSERT INTO some.table (a, b, c, d)
# VALUES (1, '2', 3.0, (SELECT id FROM another.table WHERE name = 'Paul')), (5, '6', 7.0, (SELECT id FROM another.table WHERE name = 'Paul'));

Source code in table2sql/main.py

def convert_table_file_to_insert_statement(
    path_to_file: str,
    output_table: str,
    delimiter: Optional[str] = ",",
    has_types_row: Optional[bool] = False,
    sheet_name: Optional[str] = None,
):
    # noqa: E501
    """Converts table file (CSV or Excel) to SQL insert statements.

    If file doesn't contain row with types as second row - every value is treated as string and
    will be available in insert statement with single quotes.

    If file contain row with types and `types_row` argument is set to `True` - types will be used
    to convert columns to given types.

    Args:
        path_to_file (str): Name of file containing data to be converted to SQL insert statements.
        output_table (str): Name of table into which data should be inserted.
        delimiter (str): Delimiter of given CSV file.
        has_types_row (bool, optional): If second row of table file contains row with types
          (from `TYPES_MAP`). Defaults to False.
        sheet_name (str, optional): If file is Excel - pass sheet name which should be converted
          into insert statements.

    Returns:
        str: SQL insert statement

    Example:
    ```python
    from table2sql import convert_table_file_to_insert_statement

    ## some.csv
    # a,b,c,d
    # int,str,float,sql
    # 1,2,3,(SELECT id FROM another.table WHERE name = 'Paul')
    # 5,6,7,(SELECT id FROM another.table WHERE name = 'Paul')

    inserts = convert_table_file_to_insert_statement(
        path_to_file="some.csv",
        output_table="test.table",
        has_types_row=True,
    )

    print(inserts)
    # INSERT INTO some.table (a, b, c, d)
    # VALUES (1, '2', 3.0, (SELECT id FROM another.table WHERE name = 'Paul')), (5, '6', 7.0, (SELECT id FROM another.table WHERE name = 'Paul'));
    ```
    """

    file_extension = _get_file_extension(path_to_file)

    if file_extension == "csv":
        rows = get_list_of_tuples_from_csv(path_to_file=path_to_file, delimiter=delimiter)
    elif file_extension in ("xlsx", "xlsm", "xltx", "xltm"):
        rows = get_list_of_tuples_from_excel(path_to_file=path_to_file, sheet_name=sheet_name)
    else:
        raise NotImplementedError(f"'.{file_extension}' file extension is not supported")

    types = None
    if has_types_row:
        [column_names, types_str, *values] = rows
        types_str = cast(Tuple[str, ...], types_str)
        types = _get_types_functions(types_str)
    else:
        [column_names, *values] = rows

    column_names_formatted = _get_columns_formatted(column_names)
    values_formatted = _get_values_formatted(values, types=types)

    return _get_insert_statement_formatted(
        table_name=output_table,
        column_names_formatted=column_names_formatted,
        values_formatted=values_formatted,
    )