Create SQL statements from CSV file for creating a MySQL table

Question

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() 

Answer 1

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.