Using Python to fill dummy data in a MySQL table
I was messing around with Sakila DB and I created a table director
using the same schema as the table actor
.
For those unfamiliar, the table actor
has 4 columns:
- actors_id
- first_name
- last_name
- last_update
The table I created is director
similar in structure.
Now there is a table film_actor
, I think it is used as a join table since it has composite primary keys used to bind the table actor
to the table film
.
Now I want to create a table director_film
... you guessed it! Create a link to films with associated directors.
The problem is that there are 1000 films in Sakila. I want every director (I created 15) to be associated with every movie.
Is there a way to use Python to randomly assign a director to a movie? This is on localhost and I am currently on port 3306 by default using the root user.
The primary keys for director
and are film
automatically incremented starting at 1.
source to share
If you assume you only have one director per movie, it would be advisable to add a field director
to the table film
.
Then you can run the following SQL query to set an arbitrary director (an integer from 1 to 15) for each tape:
UPDATE film SET director = FLOOR( 1 + RAND( ) *15 )
EDIT:
Copy the content film_actor
to director_film
:
INSERT INTO director_film (SELECT * FROM film_actor )
Then let's create a random director:
UPDATE director_film SET director = FLOOR( 1 + RAND( ) *15 )
source to share