How to Insert Data into a Table
How to Insert Data into a Table ๊ด๋ จ
Now that we have our Students table created, itโs time to start inserting data into the database. In this section, weโll cover how to insert both single and multiple records using Python and SQLite, and how to avoid common security issues like SQL injection by using parameterized queries.
How to Insert a Single Record
To insert data into the database, we use the INSERT INTO SQL command. Letโs start by inserting a single record into our Students table.
Hereโs the basic SQL syntax for inserting a single record:
INSERT INTO Students (name, age, email)
VALUES ('John Doe', 20, 'johndoe@example.com');
However, instead of writing SQL directly in our Python script with hardcoded values, weโll use parameterized queries to make our code more secure and flexible. Parameterized queries help prevent SQL injection, a common attack where malicious users can manipulate the SQL query by passing harmful input.
Hereโs how we can insert a single record into the Students table using a parameterized query:
import sqlite3
# Use 'with' to open and close the connection automatically
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# Insert a record into the Students table
insert_query = '''
INSERT INTO Students (name, age, email)
VALUES (?, ?, ?);
'''
student_data = ('Jane Doe', 23, 'jane@example.com')
cursor.execute(insert_query, student_data)
# Commit the changes automatically
connection.commit()
# No need to call connection.close(); it's done automatically!
print("Record inserted successfully!")
The ? placeholders represent the values to be inserted into the table. The actual values are passed as a tuple (student_data) in the cursor.execute() method.
How to Insert Multiple Records
If you want to insert multiple records at once, you can use the executemany() method in Python. This method takes a list of tuples, where each tuple represents one record.
To make our example more dynamic, we can use the Faker library to generate random student data. This is useful for testing and simulating real-world scenarios.
from faker import Faker
import sqlite3
# Initialize Faker
fake = Faker(['en_IN'])
# Use 'with' to open and close the connection automatically
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# Insert a record into the Students table
insert_query = '''
INSERT INTO Students (name, age, email)
VALUES (?, ?, ?);
'''
students_data = [(fake.name(), fake.random_int(
min=18, max=25), fake.email()) for _ in range(5)]
# Execute the query for multiple records
cursor.executemany(insert_query, students_data)
# Commit the changes
connection.commit()
# Print confirmation message
print("Fake student records inserted successfully!")
In this code:
Faker()generates random names, ages, and emails for students. Passing the locale([โen_INโ]) is optional.cursor.executemany(): This method allows us to insert multiple records at once, making the code more efficient.students_data: A list of tuples where each tuple represents one studentโs data.
How to Handle Common Issues: SQL Injection
SQL injection is a security vulnerability where attackers can insert or manipulate SQL queries by providing harmful input. For example, an attacker might try to inject code like '; DROP TABLE Students; -- to delete the table.
By using parameterized queries (as demonstrated above), we avoid this issue. The ? placeholders in parameterized queries ensure that input values are treated as data, not as part of the SQL command. This makes it impossible for malicious code to be executed.