top of page

How to Generate Dummy Data in MySQL Using the Python Faker Module

Writer's picture: Siddhesh KadamSiddhesh Kadam

Python faker

In many development and testing scenarios, having a realistic dataset is crucial. Instead of manually entering fake data, Python’s Faker module allows us to generate dummy data effortlessly. In this blog, we’ll explore how to populate a MySQL database with fake employee records using Faker and MySQL Connector.


You can install the required Python packages using pip:


[root@siddhesh ~]# pip3 install faker mysql-connector-python
Collecting faker
  Downloading Faker-35.2.0-py3-none-any.whl.metadata (15 kB)
Collecting mysql-connector-python
  Downloading mysql_connector_python-9.2.0-py2.py3-none-any.whl.metadata (6.0 kB)
Requirement already satisfied: python-dateutil>=2.4 in ./.local/lib/python3.9/site-packages (from faker) (2.8.2)
Requirement already satisfied: typing-extensions in ./.pyenv/versions/3.9.16/lib/python3.9/site-packages (from faker) (4.8.0)
Requirement already satisfied: six>=1.5 in ./.local/lib/python3.9/site-packages (from python-dateutil>=2.4->faker) (1.16.0)
Downloading Faker-35.2.0-py3-none-any.whl (1.9 MB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 1.9/1.9 MB 81.3 MB/s eta 0:00:00
Downloading mysql_connector_python-9.2.0-py2.py3-none-any.whl (398 kB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 398.1/398.1 kB 56.9 MB/s eta 0:00:00
Installing collected packages: mysql-connector-python, faker
Successfully installed faker-35.2.0 mysql-connector-python-9.2.0
[root@atploghost ~]#
 pip3 install faker mysql-connector-python
Collecting faker
  Downloading Faker-35.2.0-py3-none-any.whl.metadata (15 kB)
Collecting mysql-connector-python
  Downloading mysql_connector_python-9.2.0-py2.py3-none-any.whl.metadata (6.0 kB)
Requirement already satisfied: python-dateutil>=2.4 in ./.local/lib/python3.9/site-packages (from faker) (2.8.2)
Requirement already satisfied: typing-extensions in ./.pyenv/versions/3.9.16/lib/python3.9/site-packages (from faker) (4.8.0)
Requirement already satisfied: six>=1.5 in ./.local/lib/python3.9/site-packages (from python-dateutil>=2.4->faker) (1.16.0)
Downloading Faker-35.2.0-py3-none-any.whl (1.9 MB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 1.9/1.9 MB 81.3 MB/s eta 0:00:00
Downloading mysql_connector_python-9.2.0-py2.py3-none-any.whl (398 kB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 398.1/398.1 kB 56.9 MB/s eta 0:00:00
Installing collected packages: mysql-connector-python, faker
Successfully installed faker-35.2.0 mysql-connector-python-9.2.0
[root@siddhesh ~]#

Step 1: Setting Up the MySQL Database


First, log in to MySQL and create a database and table:

MariaDB [(none)]>  CREATE DATABASE dummy_db_records;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use dummy_db_records
Database changed
MariaDB [dummy_db_records]> CREATE TABLE employees (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     first_name VARCHAR(50),
    ->     last_name VARCHAR(50),
    ->     email VARCHAR(100),
    ->     phone VARCHAR(20),
    ->     department VARCHAR(50),
    ->     address VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [dummy_db_records]>

Step 2: Writing the Python Script


Now, let’s write a Python script to generate and insert dummy employee data into the MySQL database.

import mysql.connector
from faker import Faker
fake = Faker()
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="*********",
    database="dummy_db_records"
)
cursor = conn.cursor()
total_records = 10000
for sid in range(total_records):
    first_name = fake.first_name()
    last_name = fake.last_name()
    email = fake.email()
    phone = fake.phone_number()
    department = fake.job()
    address = fake.address()
    cursor.execute(
        "INSERT INTO employees (first_name, last_name, email, phone, department, address) VALUES (%s, %s, %s, %s, %s, %s)",
        (first_name, last_name, email, phone, department, address)
    )
conn.commit()
cursor.close()
conn.close()
print(f"{total_records} dummy employee records inserted successfully!")

This Python script uses Faker to generate and insert 10,000 dummy employee records into a MySQL database, making it useful for testing and development purposes.


Step 3: Verify the Data


Once the script runs successfully, you can verify the data in MySQL using:

[root@siddhesh ~]# python3 fake_employees.py
10000 dummy employee records inserted successfully!
[root@siddhesh ~]#

MariaDB [dummy_db_records]> select count(*) from employees;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

MariaDB [dummy_db_records]>


Python Faker


Conclusion


Using Python’s Faker library, we efficiently generated dummy employee records and populated a MySQL database. This approach is incredibly useful for testing and development purposes, saving time and ensuring a realistic dataset.

Try modifying the script to include additional fields like job titles, or even performance ratings for a more comprehensive dataset!

コメント

5つ星のうち0と評価されています。
まだ評価がありません

評価を追加
bottom of page