Inserting and updating records in the database using python and sqlalchemy

Today we are going to learn how to insert and update records in the database using python and sqlalchemy. Before we learned how to search the database, well now we need to know how to insert and update.

Again if you have not already setup a postgresql database, please do so now. Also we need to install the python packages if you have not done that already either. Run the following to install the required packages:

pip install sqlalchemy psycopg2

Now that we have the proper packages installed we can move forward with using our existing model from this post . So check back there for setting up the database file and model file before moving forward.

First off we need to build a dictionary that contains key value pairs, where the keys are the same name as the columns of the table name in the database. The values in the dictionary will be the values we want inserted for each column in the table name. In our example we will be using the following dictionary:

columns_with_values = {
    "make": "toyota",
    "model": "4-Runner",
    "engine": "v6"
}

Now we can take this dictionary and add a new record if one is not found based on the criteria provided or update the existing record in the db.

car = db.session \
    .query(models.Cars) \
    .filter_by(id = 1) \
    .first()
if car is None:
    car = models.Cars(**columns_with_values)
    db.session.add(car)
    db.session.commit()
else:
    car = update(models.Cars)
    car = car.values(**columns_with_values)
    car = car.where(models.Cars.id == 1)
    db.session.execute(car)

First we are only dealing with the possibility of one item ever being inserted into the table, based on calling .filter_by(id = 1). Normally you would filter on different criteria. So we check to see if the car with an id of 1 is in the table. If it is not then we create a new car model object with the columns_with_values passed in. Then we call the add method on the db session object and pass the car object as an argument to insert the new car data into the table. If there is an existing car record in the table that matches our search criteria, then reach the else clause. Here we call update method from sqlalchemy package. So we will need to import that method from the sqlalchemy package:

from sqlalchemy import update

Be sure to add that import statement to the top of your file where the direct above code to insert or update the car model into the table is. The update method takes the cars model object as an argument. The important step is next, we have to assign the data to the car model object, by calling the values method with the columns_with_values as an argument. Then we look up the car model id where the id matches the one we are trying to update. Finally we call the execute method on the db.session object and pass in the car object as an argument. This is where the key values pairs in the columns_with_values dictionary, update or add to the existing columns in the table, based on what is in the table for this particular record.

That is how you would add a new car record to the cars table or update an existing car record in the cars table.

Thank you for reading this post. Please stay tuned for more content.