populate mssqldb

populate_mssqldb.py

from datetime import datetime
from faker import Faker
from faker.providers import date_time, address
from random import randint


fake = Faker()
fake.add_provider(date_time)

productsInShops = [
    ["1", "AppleMaster", "500"],
    ["2", "Ashley Group", "10"],
    ["3", "BeforeShock", "20"],
    ["4", "Bennett PLC", "10"],
    ["5", "Chan Group", "100"],
    ["6", "DailyNeeds Ltd", "10"],
    ["7", "Ferguson-Johnson", "10"],
    ["8", "ElectronicMaster", "3000"],
    ["9", "Flores Group", "10"],
    ["10", "Franklin Inc", "10"],
    ["11", "KingOfPhones", "2000"],
    ["12", "KingOfSleepAsia", "10"],
    ["13", "Lopez Ltd", "50"],
    ["14", "NinjaPhone", "1000"],
    ["15", "Phone-Link", "2000"],
    ["16", "PhoneZone", "2000"],
    ["17", "Scott and Sons", "100"],
    ["18", "SkincareSG", "10"],
    ["19", "Tucker Ltd", "1500"],
    ["20", "ZoneX", "3000"],
]

print("INSERT INTO orders VALUES")
for oid in range(49, 70):
    print(
        "("
        + "'" + str(oid) + "',"
        + "'" + fake.date_time_between_dates(
            datetime_start=datetime(2022, 3, 1),
            datetime_end=datetime(2022, 4, 1),
            tzinfo=None,
        ).strftime('%Y-%m-%d %H:%M:%S.%f')[:-3] + "',"
        + "'" + fake.building_number() + " "
        + fake.street_name() + " "
        + fake.name() + "',"
        + "'" + str(randint(10, 29)) + "'"
        + "),"
    )

print("=====================")
print("=====================")
print("=====================")

print("INSERT INTO [products-in-order] VALUES")
opid = 47
for oid in range(49, 70):
    num_diff_products = randint(1, 5)

    for j in range(1, num_diff_products):
        opid = opid + 1  # increment

        productInShop = productsInShops[randint(0, len(productsInShops) - 1)]

        spid = productInShop[0]
        shopName = productInShop[1]
        unit_price = productInShop[2]

        quantity = randint(1, 3)
        price = str(int(unit_price) * quantity)

        print(
            "("
            + "'" + str(opid) + "',"
            + "'" + price + "',"
            + "'" + str(quantity) + "',"
            + "'" + fake.date_time_between_dates(
                datetime_start=datetime(2022, 4, 2),
                datetime_end=datetime(2022, 5, 1),
                tzinfo=None,
            ).strftime('%Y-%m-%d %H:%M:%S.%f')[:-3] + "',"
            + "'" + "In Transit" + "',"
            + "'" + str(oid) + "',"
            + "'" + shopName + "',"
            + "'" + spid + "'"
            + "),"
        )