How to effectively work with the database in this code?

Question

Python

There is a code

for post in media:
    id = post['node']['id']
    like = post['node']['edge_liked_by']['count']
    comment = post['node']['edge_media_to_comment']['count']
    photo = post['node']['display_url']
    // ищем посты в базе данных
    post = cursor.execute("SELECT id FROM blog_instagram WHERE id LIKE "+id)
    count = 0
    // если есть такой id то увеличиваем счетчик на 1
    for row in post:
        count += 1
    values = "'" + str(like) + "', '" + str(comment) + "', '" + str(photo) + "', '" + str(id) + "'"
    // если счетчик > 0 то пропускаем т.к. посты должны быть уникальны в базе
    if (count == 0):
        cursor.execute("INSERT INTO blog_instagram VALUES(" + values + ")")
conn.commit()

It seems to me that it is not effective in working with the database, for example, if there are a couple of hundred thousand posts, it will be difficult for the machine to process so much information quickly, how can this be done more efficiently?

Answer 1

Maxim Stikharev ,
what you have written should look something like this (if I understand correctly):

for post in media:
    id = post['node']['id']
    if cursor.execute("SELECT count(id) FROM blog_instagram WHERE id = ?", id):
        continue

    cursor.execute("INSERT INTO blog_instagram(id, like, comment, photo) VALUES(?, ?, ?, ?)", 
        [id,
         post['node']['edge_media_to_comment']['count'],
         post['node']['edge_liked_by']['count'],
         post['node']['display_url']]
    )

but here a lot of questions immediately arise)
1) do you correctly understand the meaning of the id field in a relational database (I ask because it is usually not inserted)
if you mean post_id or something like that, then you have a mess in the names there which confuses the entire community.
2) what is the structure of the table
3) what exactly do you want to achieve
4) usually the uniqueness of the field is guaranteed not by the developer with his curved additional queries, but by the database. read about UNIQUE Constraint