Inserting Data

CRUD is a database principle that is crucial for interacting with databases.

• Create: The ability to create tables

• Read: The ability to get data from tables

• Update: The ability to change values within tables

• Delete: The most dangerous ability, the ability delete data, tables, rows, and columns

We have focused a lot on Reading data from tables; this chapter will do basic insertion of data.

SQL Query Format

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

• When using the INSERT query, you must specify a table that exists in your SQL database

• The values that you want to insert should line up with the columns that it should go into

• It is imperative to understand that we are adding a row of data when INSERTING

Example

Examine the following employee table:

id	name	position
1	John Doe	Software Engineer
2	Jane Smith	Product Manager
3	Alice Johnson	UX Designer

Our goal is to insert the following employee:

• ID: 4

• Name: Lil Kid

• Position: Junior Developer

The query would look like:

INSERT INTO employee (id, name, position)
VALUES (4, 'Lil Kid', 'Junior Developer');

In Python, it would look like:

# assuming that cursor object exists already ...
cursor.execute(
    '''
    INSERT INTO employees (id, name, position)
    VALUES (?, ?, ?)
    ''', 
    (4, 'Lil Kid', 'Junior Developer')
)

The execute() method is going to be using two argument inputs.

• The query as a long string

• A tuple that contains the value to be inserted

The reason for using (?, ?, ?) in our query

The question marks will act as a placeholder and they will use the values we provide within our 2nd argument in order.

This is to prevent a topic called "SQL Injection Attacks"