Insert Data to multiple MySQL database tables from .csv file

Question

I have a database with tables: person, player, coach, and team. All the tables have an auto-increment id field as the primary key. Person has id, firstname, lastname. Player and coach both have the id field, as well as person_id and team_id as foreign keys to tie them to a team.id or person.id field in the other tables.

Now in order to fully populate these tables, I have several csv files with the list of names of the players in each team. Can I write a bash or python script to take this data and input not only the names to the person table, but also have the proper person and team id values put into the player table?

If the question isn't clear just ask and I'll do what I can to clarify. Thanks.

mysql> describe person;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| firstname | varchar(30) | NO   |     | NULL    |                |
| lastname  | varchar(30) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
mysql> describe player;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| person_id | int(11) | NO   | MUL | NULL    |                |
| team_id   | int(11) | NO   | MUL | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
mysql> describe team;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| teamname  | varchar(25) | NO   |     | NULL    |                |
| location  | varchar(40) | NO   |     | NULL    |                |
| city      | varchar(25) | NO   |     | NULL    |                |
| state     | varchar(2)  | NO   |     | NULL    |                |
| venue     | varchar(35) | NO   |     | NULL    |                |
| league_id | int(11)     | NO   | MUL | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

And here is an example of the csv file content: (AL-Central-Indians.csv)

Fausto,Carmona
Carlos,Carrasco
Kelvin,De La Cruz
Chad,Durbin

Answer 1

You can do this directly using the mysql command as follows:

load data local infile 'AL-Central-Indians.csv' into table player
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(person_id, team_id)

I got that from here. Although that page also deals with exporting excel into CSV first.

Answer 2

Using an ORM might be an overkill for your purpose, but it makes your life easy if you need to do real work with data. It will require you to install some software, but if you are willing to learn some new stuff, you will probably gain a lot down the road. Luckily, it is not very hard to start using it, for example, using Django:

• Download and install django
• Create a new project using django-admin startproject myproject
• Create a new app: ./manage.py startapp myapp
• Change the database connection parameters in settings.py
• ./manage.py inspectdb should create the models for you. Use ./manage.py inspectdb > myapp/models.py to save it.
• Execute export DJANGO_SETTINGS_MODULE=settings to allow you to use django from command line scripts

Now you can create an import_players.py script in this fashion:

from myapp.models import Player, Person, Coach, Team
for my_file in my_files: # TODO: Iterate through your files
    team = Team.objects.create(name=my_team_name) # creates a db record for a team
    for line in lines_in_my_file: # TODO: Iterate through lines in your file
        player = Player.objects.create(name=my_player_name, team=team) creates a db record for a player

See this to learn how to work with models: https://docs.djangoproject.com/en/dev/topics/db/models/