Title: How to INSERT into a database using JOIN
Tags: python-3.x;postgresql;peewee
Question: I'm currently using PeeWee together with Python and I have managed to create a cool application
CREATE TABLE stores (
id SERIAL PRIMARY KEY,
store_name TEXT
CREATE TABLE products (
id SERIAL,
store_id INTEGER NOT NULL,
title TEXT,
image TEXT,
url TEXT UNIQUE,
added_date timestamp without time zone NOT NULL DEFAULT NOW(),
PRIMARY KEY(id, store_id)
ALTER TABLE products
ADD  CONSTRAINT &quot;FK_products_stores&quot; FOREIGN KEY (&quot;store_id&quot;)
REFERENCES stores (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE RESTRICT;
which has been converted to peewee by following code:
# ------------------------------------------------------------------------------- #
class Stores(Model):
id = IntegerField(column_name='id')
store_name = TextField(column_name='store_name')
class Meta:
database = postgres_pool
db_table = &quot;stores&quot;
@classmethod
def get_all(cls):
try:
return cls.select(cls.id, cls.store_name).order_by(cls.store)
except Stores.IntegrityError:
return None
# ------------------------------------------------------------------------------- #
class Products(Model):
id = IntegerField(column_name='id')
title = TextField(column_name='title')
url = TextField(column_name='url')
image = TextField(column_name='image')
store = ForeignKeyField(Stores, backref='products')
class Meta:
database = postgres_pool
db_table = &quot;products&quot;
@classmethod
def add_product(cls, pageData, store_name):
&quot;&quot;&quot;
INSERT
INTO
public.products(store_id, title, image, url)
VALUES((SELECT id FROM stores WHERE store_name = 'footish'), 'Teva Flatform Universal Pride',
'https://www.footish.se/sneakers/teva-flatform-universal-pride-t1116376',
'https://www.footish.se/pub_images/large/teva-flatform-universal-pride-t1116376-p77148.jpg?timestamp=1623417840')
&quot;&quot;&quot;
try:
return cls.insert(
store_id=cls.select(cls.store.id).join(Stores).where(cls.store.store_name == store_name).get().store.id,
title=pageData.title,
url=pageData.url,
image=pageData.image,
).execute()
except Products.DoesNotExist:
return None
However I have realized that working with id's is quite faster than working with text and I have an issue where I am trying to figure out what would be the best way to insert the ID. I did get a comment regarding my code as for today:
your insert isn't' referencing &quot;stores&quot; at all so not sure what your hoping to get from that since you have a sub query there
I am a bit confused what that means however my question is that I would like to know which approach is the correct way to insert
Is it better on start of application, to store the id as a variable and pass the variable into a insert function (argument)
Or to call store_id=cls.select(cls.store.id).join(Stores).where(cls.store.store_name == store_name).get().store.id where I instead pass the store_name and then it would return the correct id?
My first thought is that by doing the number 2, that is like doing 2 queries instead of one? but I might be wrong. Looking forward to know!

Here is the accepted answer: This is quite incorrect:
# Wrong
store_id=cls.select(cls.store.id).join(Stores).where(cls.store.store_name == store_name).get().store.id,
Correct:
store = Stores.select().where(Stores.name == store_name).get()
except Stores.DoesNotExist:
# the store name does not exist. do whatever?
return
Products.insert(store=store, ...rest-of-fields...).execute()
Comment for this answer: Just use the store id if you have it.
Comment for this answer: I strongly suggest you read the docs instead of posting more questions. These are very basic concepts and are covered in depth in the docs
Comment for this answer: Oh I see! That is true and a lot better too. But what do you prefer if I might ask, is it better to beginning of the script to get the store_id and save it as a variable and whenever I want to insert, pass the store_id variable as a argument to the function where I insert or do I want to call the `store = Stores.select().where(Stores.name == store_name).get()` everytime I want to insert?
Comment for this answer: Right, how would the peewee query be in that case if I have the store_id set to e.g. 1?
Comment for this answer: Hi agan :) Its been 2 days so I have been able to improve myself since then thanks to you :D I think my issue is that I want too much to fast before reading :)


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2021:00:08/13/21', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<python-3.x><postgresql><peewee>', 'Title': 'How to INSERT into a database using JOIN', 'ViewCount': 84, 'id': 68769050, 'user_AboutMe': '', 'user_AccountId': 17918885.0, 'user_DisplayName': 'PythonNewbie', 'user_Reputation': 825.0, 'user_Views': 163.0, 'user_id': 13019246.0}}