In the previous tutorial, we learned how to create tables using Python with SQLite. Now, we’ll take the next step: learning how to insert data into these tables. This process involves adding records or rows to the database. Here's how to do it step by step.

Basic syntax for inserting data

To insert data into a table, use the following commands:

with con:
    cur = con.cursor()
    query = "INSERT INTO Category VALUES ('Romance')"
    cur.execute(query)

Alternatively, you can use a more dynamic and scalable method by passing the values as parameters:

values = ["Comedy"]
with con:
    cur = con.cursor()
    query = "INSERT INTO Category (name) VALUES (?)"
    cur.execute(query, values)

Explanation of the code

  1. name: This represents the column in the Category table where the value will be inserted.
  2. ?: Acts as a placeholder for the value being inserted. The number of placeholders must match the number of specified columns.
  3. cur.execute(query, values): Executes the SQL statement while securely inserting the values to avoid SQL injection.

This approach ensures flexibility and security, especially when dealing with user inputs or large datasets.

Inserting Data into All Tables

Here’s how to insert data into the remaining tables.

1. Inserting Data into the Book Table

values = [
    "The Lost Romance of Romeo and Juliet",
    "John",
    "Publisher-1",
    0,
    15,
    "2020-10-26"
]
with con:
    cur = con.cursor()
    query = """
    INSERT INTO Book (title, actor, publisher, category, copies, added_in)
    VALUES (?, ?, ?, ?, ?, ?)
    """
    cur.execute(query, values)

 

2. Inserting Data into the Member Table

values = [
    "Joao Futi",
    "12345",
    "Address 1",
    "M"
]
with con:
    cur = con.cursor()
    query = """
    INSERT INTO Member (name, tel, address, gender)
    VALUES (?, ?, ?, ?)
    """
    cur.execute(query, values)

 

3. Inserting Data into the Rented_Books Table

values = [
    0,
    0,
    "2020-10-26",
    "2020-10-29"
]
with con:
    cur = con.cursor()
    query = """
    INSERT INTO Rented_Books (book_id, member_id, rented_in, return_date)
    VALUES (?, ?, ?, ?)
    """
    cur.execute(query, values)

 

Key points to remember

  • Always ensure the data types in your values match the corresponding column types in the table schema.
  • Use parameterized queries (?) to prevent SQL injection and ensure data integrity.
  • Check for constraints like FOREIGN KEY relationships when inserting data into linked tables.

By following these steps, you can easily populate your database with data, making it ready for further manipulation and querying. In the next tutorial, we’ll explore how to retrieve and manipulate this data effectively.

 

Copyright statement: Unless otherwise indicated, all articles are original to this site, please cite the source when sharing.

Article link:http://pybeginners.com/sql-tutorial/python-with-sqlite-how-to-insert-data-into-sql-tables-insert/

License agreement:Creative Commons Attribution-NonCommercial 4.0 International License