🚀 How to Generate a 1-Million-Row SQLite Database with Python and View It in SQLite Studio

Introduction

If you need to generate a large SQLite database for testing, this guide will show you how to create a Python script that inserts 1 million rows of random data into a SQLite database. We’ll use the Faker library to generate realistic names, addresses, and phone numbers. Finally, we’ll open the database in SQLite Studio to view the data.

---

Step 1: Install Dependencies

Before running the script, make sure you have Python installed. You’ll also need the Faker library. You can install it using pip.

Run this command:

pip install faker

---

Step 2: Python Script to Generate the SQLite Database

Now, create a Python script named generate_data.py and paste the following code:

Python Code:

import sqlite3
from faker import Faker

# Initialize Faker for generating random data
fake = Faker()

# Database file
db_file = "people.db"

# Connect to SQLite database (or create it)
conn = sqlite3.connect(db_file)
cursor = conn.cursor()

# Create table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS people (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        first_name TEXT NOT NULL,
        last_name TEXT NOT NULL,
        address TEXT NOT NULL,
        phone TEXT NOT NULL
    )
""")

# Function to insert fake data
def insert_fake_data(num_records=1000000):
    batch_size = 10000  # Insert in batches to optimize performance
    for _ in range(num_records // batch_size):
        data = [(fake.first_name(), fake.last_name(), fake.address(), fake.phone_number()) for _ in range(batch_size)]
        cursor.executemany("INSERT INTO people (first_name, last_name, address, phone) VALUES (?, ?, ?, ?)", data)
        conn.commit()
        print(f"Inserted {_ * batch_size} records...")

# Insert 1 million rows
insert_fake_data(1000000)

# Close connection
conn.close()

print("Database populated successfully!")

---

Step 3: Running the Script

Once you have the script saved as generate_data.py, open a command prompt or terminal and navigate to the directory where the script is saved.

Then, run the script using:

Command:

python generate_data.py

This script will create a file named people.db containing 1 million rows of random data.

---

Step 4: Viewing the Data in SQLite Studio

Now that we have generated the database, let’s open it in SQLite Studio.

Follow these steps:

1. Open SQLite Studio.
2. Click on “Add a database” (green plus sign).
3. Select “Database file”, then browse and select people.db.
4. Click OK to add the database.
5. In the Database Tree, click on the people table.
6. Right-click on people → Choose “Edit Data” to view all inserted rows.

---

Step 5: Running Queries in SQLite Studio

Once inside SQLite Studio, you can run queries in the SQL Editor.

Example Queries

1. Count total rows in the table

SELECT COUNT(*) FROM people;

2. View 10 random entries

SELECT * FROM people ORDER BY RANDOM() LIMIT 10;

3. Search for a specific first name

SELECT * FROM people WHERE first_name = 'John';

---

Conclusion

By following this guide, you’ve successfully: ✅ Installed the required dependencies
✅ Created a Python script to generate 1 million rows in SQLite
✅ Opened the database in SQLite Studio
✅ Ran queries to explore the data

This method is perfect for testing, database performance analysis, and learning SQLite efficiently. 🚀 Let me know if you have any questions in the comments!

---

🔥 Did this guide help you?

If you found this useful, don’t forget to share and subscribe for more database tutorials! 😃

---