Debug School

rakesh kumar
rakesh kumar

Posted on

How to generate random data using sql query

step1: write code in python and run in jupyter notebook

import random

# List of major Indian cities
cities = [
    "Mumbai", "Delhi", "Bangalore", "Hyderabad", "Ahmedabad", "Chennai", "Kolkata", "Pune", "Jaipur", "Lucknow",
    "Kanpur", "Nagpur", "Indore", "Thane", "Bhopal", "Visakhapatnam", "Patna", "Vadodara", "Ghaziabad", "Ludhiana",
    "Agra", "Nashik", "Faridabad", "Meerut", "Rajkot", "Varanasi", "Srinagar", "Aurangabad", "Dhanbad", "Amritsar",
    "Navi Mumbai", "Allahabad", "Howrah", "Gwalior", "Jabalpur", "Coimbatore", "Vijayawada", "Jodhpur", "Madurai",
    "Raipur", "Kota", "Guwahati", "Chandigarh", "Solapur", "Hubli", "Mysore", "Tiruchirappalli", "Bareilly",
    "Aligarh", "Tiruppur", "Moradabad", "Gorakhpur", "Bhubaneswar", "Salem", "Warangal", "Guntur", "Bikaner",
    "Noida", "Jamshedpur", "Bhilai", "Cuttack", "Firozabad", "Kochi", "Dehradun", "Durgapur", "Asansol", "Nanded"
]

# Categories of shops
categories = [
    "Fashion", "Electronics", "Grocery", "Jewelry", "Books", "Furniture", "Toys", "Pet Store", "Pharmacy",
    "Automobile", "Sports", "Cafe", "Home Decor", "Gift Store", "Flower Shop", "Footwear", "Optical Store",
    "Fitness", "Hardware", "Beauty & Spa"
]

# Generate 1000 unique shop names randomly
shop_names = []
for _ in range(1000):
    city = random.choice(cities)  # Randomly select a city
    category = random.choice(categories)  # Randomly select a category
    shop_name = f"{category} Hub {city}"
    shop_names.append(f"('{shop_name}', '{city}', '{category}')")

# Create full SQL query
sql_query = """
CREATE DATABASE IF NOT EXISTS shop_database;
USE shop_database;

CREATE TABLE IF NOT EXISTS Shops (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shop_name VARCHAR(255) NOT NULL,
    city VARCHAR(100) NOT NULL,
    category VARCHAR(100) NOT NULL
);

INSERT INTO Shops (shop_name, city, category) VALUES
""" + ",\n".join(shop_names) + ";"

# Save the SQL query to a file
file_path = "C:\\Users\\rakes\\insert_random_shops_mysql.sql"
with open(file_path, "w") as file:
    file.write(sql_query)

print(f"SQL file saved at: {file_path}")
Enter fullscreen mode Exit fullscreen mode

output

Image description
step2
click import in phpmyadmin

Image description

step3 choose sql file in location

file_path = "C:\\Users\\rakes\\insert_random_shops_mysql.sql"
Enter fullscreen mode Exit fullscreen mode

step4: after choose file click import

output

Image description

bike_rental_shops = [
    "MotoShare Rentals",
    "Xpress Bike Rentals",
    "Turbo Ride Rentals",
    "Urban Wheels Bike Rentals",
    "EcoRide Motorbike Rentals",
    "RevvedUp Bike Rentals",
    "GrandTorque Bike Hire",
    "Street Glide Bike Rentals",
    "Rider’s Paradise",
    "Speedy Wheels Rentals",
    "PedalPro Bike Hire",
    "Adrenaline Ride Rentals",
    "Velocity Cycle Rentals",
    "Xtreme Trail Bike Rentals",
    "GearUp Adventure Bikes",
    "RaceReady Bike Rentals",
    "DriftMaster Bike Hire",
    "PowerPedal Bike Rentals",
    "Trailblazer Bike Tours",
    "RapidRide Bike Rentals"
]
Enter fullscreen mode Exit fullscreen mode

Top comments (0)