
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]>

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!
コメント