4
\$\begingroup\$

This program reads in a CSV file's header row and writes out SQL statements to a .sql file that can be used to create a new table in a MySQL database and load the data into it. If anyone can offer some suggestions for improving I would appreciate it.

import csv
import sys


def get_file():

    file = input('Filename: ')
    return file

def get_headers(csv_file):

    """
    Read in the CSV and extract the column names. Strip
    any whitespace, replace spaces underscore and make lowercase.

    """

    try:
        with open (csv_file, newline='') as f:
            reader = csv.reader(f)
            header_row = next(reader)
            headers = []
            for i in header_row:
                i = i.strip().replace(' ', '_').lower()
                headers.append(i)
            headers_minus_last = headers[:-1]
            last_header = headers[-1]
        return headers, headers_minus_last, last_header
    except FileNotFoundError as e:
        print('Sorry! The file cannot be found.')
        return
    except:
        print('An error has occured.', sys.exc_info()[0])
        return

def create_table_lines(tbl_nm, headers_minus_last, last_header):

    """
    Create the SQL commands that will be used to create the database table.
    This includes the column name from the first row of the CSV file and
    the column type (VARCHAR).

    """

    column_type = 'VARCHAR(255)'

    try:
        lines_to_write_table = []
        lines_to_write_table.append('CREATE TABLE {}\n('.format(tbl_nm))

        for i in headers_minus_last:
            lines_to_write_table.append(i + ' {},'.format(column_type))
        lines_to_write_table.append(last_header + ' {}'.format(column_type) + '\n);\n')
        return lines_to_write_table
    except:
        print('An error has occured.', sys.exc_info()[0])
        return


def create_data_lines(csv_file, tbl_nm, hdrs):

    """
    Create the SQL commands that will be used to load the data from the
    CSV file into the database table.

    """

    try:
        field_terminator = "','"
        line_terminator = "'\\r\\n'"
        enclosed_by = "'\"'"
        ignore_lines = '1'
        lines_to_write_data_load = []
        lines_to_write_data_load.append('LOAD DATA LOCAL INFILE {}'.format(csv_file))
        lines_to_write_data_load.append('INTO TABLE {}'.format(tbl_nm))
        lines_to_write_data_load.append('FIELDS TERMINATED BY {}'.format(field_terminator))
        lines_to_write_data_load.append('ENCLOSED BY {}'.format(enclosed_by))
        lines_to_write_data_load.append('LINES TERMINATED BY {}'.format(line_terminator))
        lines_to_write_data_load.append('IGNORE {} LINES'.format(ignore_lines))
        lines_to_write_data_load.append('(')
        for i in hdrs[:-1]:
            lines_to_write_data_load.append(i + ',')
        lines_to_write_data_load.append(hdrs[-1] + '\n);')
        return lines_to_write_data_load
    except:
        print('An error has occured.', sys.exc_info()[0])
        return


def write_lines_to_file(csv_file, tbl_nm, tbl_lines, data_lines):

    """
    Write the SQL statements to file.

    """

    try:
        with open (tbl_nm + '.sql', 'w') as f:
            for i in tbl_lines:
                f.write(i + '\n')
            for i in data_lines:
                f.write(i + '\n')
        print('Success! Results output to {}.sql'.format(tbl_nm))
    except:
        print('An error has occured.', sys.exc_info()[0])
        return


def main():

    try:
        file = get_file()
        table_name = file.split('.')[0]
        headers, headers_minus_last, last_header = get_headers(file)
        lines_to_write_table = create_table_lines(table_name, headers_minus_last, last_header)
        lines_to_write_data_load = create_data_lines(file, table_name, headers)

        get_headers(file)
        create_table_lines(table_name, headers_minus_last, last_header)
        create_data_lines(file, table_name, headers)
        write_lines_to_file(file, table_name, lines_to_write_table, lines_to_write_data_load)
    except:
        return


if __name__ == '__main__':
    main()
\$\endgroup\$
1
\$\begingroup\$

A couple of things immediately caught my eye:

  • the bare except clauses should be avoided - be more specific about what exceptions you are catching
  • instead of constructing your queries as list of formatted strings and then joining them, you can use multi-line strings with multiple placeholders additionally preserving the SQL query indentation making a positive impact on readability.
\$\endgroup\$
1
  • \$\begingroup\$ thanks for your input. I will correct the bare except clauses and make them explicit. For the multi-line string part...I'm not actually connecting to the MySQL database to run these commands. I'm generating a file that I will use to source the MySQL statements. Although, now I am questioning if I should take this project a step further and figure out the code to connect to the server/database and run these commands directly within MySQL. Thanks again for the response. \$\endgroup\$ – shavar May 8 '17 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.