create database by load a csv files using the header as columnnames (and add a column that has the filename as a name)
Question: I have CSV files that I want to make database tables from in mysql. I've
searched all over and can't find anything on how to use the header as the
column names for the table. I suppose this must be possible. In other words,
when creating a new table in MySQL do you really have to define all the
columns, their names, their types etc in advance. It would be great if MySQL
could do something like Office Access where it converts to the corresponding
type depending on how the value looks.

I know this is maybe a too broadly defined question, but any pointers in this
matter would be helpful. I am learning Python too, so if it can be done
through a python script that would be great too.

Thank you very much.


Answer: Using Python, you could use the [csv
DictReader](https://docs.python.org/2/library/csv.html#csv.DictReader) module
to makes it pretty easy to use the headers from the csv files as labels for
the input data. It basically reads all lines in as a dictionary object with
the keys as the headers, so you can use the keys as the source for your column
names when accessing mySQL.

A quick example that reads a csv into a list of dictionaries:

example.csv:

    
    
    name,address,city,state,phone
    jack,111 washington st, somewhere, NE, 888-867-5309
    jill,112 washington st, somewhere else, NE, 888-867-5310
    john,113 washington st, another place, NE, 888-867-5311
    

example.py:

    
    
    import csv
    
    data = []
    with open("example.csv") as csvfile:
        reader = csv.DictReader(csvfile)
        for line in reader:
            data.append(line)
    
    print(data[0].keys())
    print(data[0]['address'])
    print(data[1]['name'])
    print(data[2]['phone'])
    

output:

    
    
    $:python example.py
    dict_keys(['name', 'address', 'city', 'state', 'phone'])
    111 washington st
    jill
     888-867-5311
    

More in-depth examples at: <http://java.dzone.com/articles/python-101-reading-
and-writing>

Some info on connection to MySQL in Python: [How do I connect to a MySQL
Database in Python?](http://stackoverflow.com/questions/372885/how-do-i-
connect-to-a-mysql-database-in-python)

