Save or update for FK relationship Sqlalchemy

Question

I've googled, but haven't been able to find the answer to this seemingly simple question.

I have two relations, a customer and an order. Each order is associated to a single cusomter, and therefore has a FK relationship to the customer table. The customer relation only stores customer names, and I have set a unique constraint on the customer table barring duplicate names.

Let's say I create a new order instance and set a customer for the order. Something like:

order_instance.customer = Customer("customer name")

When I save the order instance, SqlAlchemy will complain if a customer with this name already exists in the customer table.

How do I specify to SqlAlchemy to insert into the customer table if a customer with this name doesn't already exist, or just ignore (or even update) to the customer relation? I don't really want to have to check each time if a customer with some name already exists...

----Edit--- The Customer table has a unique constraint set on it, such that no two customers can have the same name. Singletoned, using your current implementation, an IntegrityError is getting thrown. If a customer with this name already exists, return that Customer instance. Otherwise, return a new Customer instance.

It seems that SqlAlchemy, no matter if the customer already exists or not, is trying to execute a SQL insert statement into the Customer table. Perhaps I have my cascades set incorrectly?

Answer 1

This is the way I normally do it:

def get_or_create_customer(customer_name):
    customer = session.query(Customer).get(customer_name)
    if not customer:
       customer = Customer(customer_name)
    return customer

order_instance.customer = get_or_create_customer("Jeff Atwood")

You can add this as a method to your class, or you could adapt the __init__ method of your class to do this. However I would recommend against this as it is better to be clear about exactly what you are doing.

Answer 2

I can't comment, but to add to Singletoned's answer.

You must only call the function once per customer to be created. I.e. you can not use it in this way:

.. = get_or_create_customer(order[1].customer_name)
.. = get_or_create_customer(order[2].customer_name)
..
session.commit()

It would fail in the case a customer has made several orders and the customer is not yet in the db, since it would attempt to create two equal customers.

many ways to fix that scenario.. e.g. have an insert_orders(..) which had the get_or_create_customer as a nested function and handle customers using a set()