Skip to content

Instantly share code, notes, and snippets.

@mikeblas
Created September 15, 2022 18:17
Show Gist options
  • Save mikeblas/5c166b2555763278228583c867e6f279 to your computer and use it in GitHub Desktop.
Save mikeblas/5c166b2555763278228583c867e6f279 to your computer and use it in GitHub Desktop.
generate some sample data for a Reddit question about MySQL Performance
import random
def build_data(tablename, filename, rows):
with open(filename, 'w') as outfile:
outfile.write(f"INSERT INTO {tablename} (id, type, category_id) VALUES\n")
type_list = ['pet', 'plant', 'book', 'snack', 'tool']
for x in range(rows):
if x > 0:
outfile.write(",\n")
chosen_type = random.choice(type_list)
category = 1 + random.randint(0, 5)
offset = 1 if random.random() > 0.66 else 1000001
outfile.write(f"({x+offset}, '{chosen_type}', {category})")
outfile.write("\n")
def make_pets(tablename, filename, rows):
with open(filename, 'w') as outfile:
outfile.write(f"INSERT INTO {tablename} (pet_id, some_name, dog_id, cat_id) VALUES")
for x in range(rows):
if x > 0:
outfile.write(",\n")
pet_name = ''.join(random.choice("abcdefghkmnqrstuvwxyz0123456789") for i in range(10))
dog_id = 'NULL' if random.random() > 0.11 else str(random.randint(0, 10000))
cat_id = 'NULL' if random.random() > 0.18 else str(random.randint(0, 10000))
outfile.write(f"({x+1}, '{pet_name}', {dog_id}, {cat_id})")
outfile.write("\n")
def build_sample():
build_data('cats', 'cats.sql', 10000)
build_data('dogs', 'dogs.sql', 10000)
make_pets('pets', 'pets.sql', 25000)
if __name__ == '__main__':
build_sample()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment