Creating a Table and Adding Records in Python: A Beginner-Friendly Guide

Working with data is a key skill in programming. One of the simplest ways to store and manipulate data is by using tables. In this article, we’ll learn how to create a table and add 20 records using Python. Don’t worry if you’re new to programming — I’ll explain everything step-by-step with real-world examples.

Step 1: Setting Up the Environment

Before we start, make sure Python is installed on your computer. You can download it from python.org.

Next, install SQLite, a lightweight database that comes pre-installed with Python. We’ll use SQLite to create our table.

pip install sqlite3  # For older Python versions, but usually no need to install.

Step 2: Creating the Table

Now, let’s write a Python script to create a database, define a table, and insert 20 records.

import sqlite3

# Connect to the database (creates a file if it doesn't exist)
connection = sqlite3.connect('company.db')
cursor = connection.cursor()
# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS employees (
                  id INTEGER PRIMARY KEY,
                  name TEXT,
                  age INTEGER,
                  department TEXT)''')
print("Table created successfully!")

Here’s what’s happening:

• sqlite3.connect(): Connects to a database named company.db. If it doesn’t exist, Python creates it.
• cursor(): Allows us to execute SQL commands.
• CREATE TABLE: Creates a table named employees with four columns.

Step 3: Inserting Records

Let’s add 20 sample employee records to the table.

# Sample data
employees = [
    (1, 'Alice Johnson', 25, 'Marketing'),
    (2, 'Bob Smith', 30, 'Sales'),
    (3, 'Charlie Brown', 35, 'IT'),
    (4, 'David White', 28, 'Finance'),
    (5, 'Eve Black', 40, 'HR'),
    (6, 'Frank Green', 45, 'Operations'),
    (7, 'Grace Kelly', 29, 'IT'),
    (8, 'Hank Ford', 50, 'Logistics'),
    (9, 'Ivy Adams', 27, 'Sales'),
    (10, 'Jack Carter', 32, 'Marketing'),
    (11, 'Kim Lee', 33, 'Finance'),
    (12, 'Laura Hill', 31, 'HR'),
    (13, 'Mike Brown', 41, 'Operations'),
    (14, 'Nancy Drew', 29, 'IT'),
    (15, 'Oscar Wilde', 26, 'Logistics'),
    (16, 'Paula Green', 34, 'Marketing'),
    (17, 'Quincy Adams', 37, 'Sales'),
    (18, 'Rachel Black', 30, 'Finance'),
    (19, 'Steve Jobs', 39, 'IT'),
    (20, 'Tom Ford', 28, 'HR')
]


# Insert data into the table
cursor.executemany('INSERT INTO employees VALUES (?, ?, ?, ?)', employees)
# Commit the changes
connection.commit()
print("20 records inserted successfully!")

Explanation:

• executemany(): Adds multiple rows to the table in one go.
• ?: Placeholder for values, making it easier to insert data dynamically.
• commit(): Saves the changes to the database.

Step 4: Viewing the Data

Let’s verify that the records have been added.

# Retrieve and display all records
cursor.execute('SELECT * FROM employees')
rows = cursor.fetchall()

for row in rows:
    print(row)
# Close the connection
connection.close()

This code fetches and prints all records in the table. You should see 20 records displayed in the output.

Real-World Example

Imagine you run a small business, and you need to track your employees. Instead of using spreadsheets, you can use Python and SQLite to create a lightweight database. This approach is more scalable and easier to integrate with web applications.

For instance, you can expand this database to include salary information, performance reviews, or attendance records. You can also connect this database to a web interface for easier management.

Final Thoughts

Creating tables and adding records in Python is a powerful yet beginner-friendly way to handle data. By following the steps above, you now have the foundation to build more advanced applications, whether for personal projects or business needs.

The best way to learn is by doing — so try creating your own tables with different data sets. Who knows? This could be the start of your journey into database management and software development!