The Complete Python/PostgreSQL Course 2.0

# How to insert data into SQLite with Python

Let's start inserting data into our database. A few things we must remember:

  • We can use the connection to execute a query.
  • We can use the context manager to automatically commit the changes when we're done.
  • We need to be able to tell the query what data we want to insert in the database.

First things first: how to execute an INSERT query:

connection.execute("INSERT INTO entries VALUES ('This is some test content', '01-01-2020');")

That would put into our database a new entry. The content of the entry would be "This is some test content" and the date of the entry would be "01-01-2020".

We must remember to use the context manager so that this insertion is saved into the database:

with connection:
    connection.execute("INSERT INTO entries VALUES ('This is some test content', '01-01-2020');")

# Passing arguments to a query

We don't want to always insert the same entry. We want to allow the user to tell us what they want to put in. That's why we need to be able to give the query some data that we want to insert. We do this using the question mark character with sqlite3.

with connection:
    connection.execute("INSERT INTO entries VALUES (?, ?);", ("This is some test content", "01-01-2020"))

Note the query now is just this: "INSERT INTO entries VALUES (?, ?);". We've also passed a second argument to connection.execute which is a tuple of two values, one for each question mark.

The first element of the tuple will be inserted into the query as the first question mark, and the second one will go in the second question mark.

When we used Python lists, we had a function that looked like this:

def add_entry(content, date):
    entries.append({"content": entry_content, "date": entry_date})

We're now going to make use of our new knowledge to put data into SQLite:

def add_entry(content, date):
    with connection:
        connection.execute("INSERT INTO entries VALUES (?, ?);", (content, date))

DANGER

Do not do this:

def add_entry(content, date):
    with connection:
        connection.execute(f"INSERT INTO entries VALUES ({content}, {date});")

If you do that, you are opening yourself up to a SQL Injection Attack[1].

# Wrapping up

That's really everything though! Our database.py function was so simple and encapsulated (its logic hidden from other parts of the app) that we have to change absolutely nothing inside app.py to make this work!

Try running the app and inserting some data. Then, open your database file with DB Browser and take a look. Make sure it works!

Next up, we will find out how to retrieve data from the database.

Enjoyed this article?
You'll love the complete video course!

  • Complete video lessons for each topic
  • Get the most out of your learning
  • Master Advanced PostgreSQL
  • Get one-on-one help from a real person

Get the video course

  1. What is a SQL Injection Attack? ↩ī¸Ž

← SELECT: retrieve data →