Doug0855 Doug0855 - 20 days ago 5
PHP Question

How to write a script to insert large amounts of data in mysql?

I am working on a database project where one of the requirements is that we must have 100,000 tuples of data. We originally were looking for general population data where each individual is represented by a tuple, but we quickly found out that sort of data isn't really available. No worries, we can create our own data!

So basically I need to write a basic script to just iterate through every tuple and insert basic stuff, like an ID(Just number them 1-100000), a set of random initials, a random income, and a random occupation from a list of 6 or so. Is this possible in mysql? I am reaaaaaaallly not excited about writing 100000 insert commands

Answer

Something like this:

for i in range(1, 1000000)
    initials = ?
    income = ?
    occupation = ?
    sql = "insert into t(initials, income, occupation) values ('{0}', {1}, '{2}')".format(initials, income, occupation)
    curs.execute(sql)

I'm not sure what values you want for the various fields. You can create lists of acceptable values and then use random() to choose from a list . . . or use random() to generate a numeric value.

It will take a little time to insert 1,000,000 rows. If this is an issue, write the data to a file and use load data infile.