How to add postgresql using psycopg2



  • There's a table blacklist:

    Column      Type                   Modifiers 
    id          integer                not null default nextval('blacklist_id_seg, regclass) 
    name        character varying(255) not null
    camp_id     integer                not null 
    

    Indexes:
    "blacklist_pkey" PRIMARY KEY, btree (id)
    "blacklist_68a30fb" btree (camp_id)
    Foreign-key constraints:
    "blacklist camp_id_42440e51_fk_campaign id. FOREIGN KEY (camp_id) REFERENCES campaign(id) DEFERRABLE INITIALLY DEFERRED

    Table blacklist has an external key to the table campaign.

    I'm gettin'. camp_id:

    cursor.execute("INSERT INTO campaign (camp_id, timestamp) VALUES (%s, %s) RETURNING id", (campaign_number, datetime.datetime.now()))
    connect.commit()
    camp_id = int(cursor.fetchone()[0])

    Next, I'm trying to make a blacklist.

    cursor.execute("INSERT INTO blacklist (camp_id, name) VALUES (%d, %s)", (camp_id, site))
    connect.commit()

    There's a record in the campaign and I get it, but I can't make it in blacklist. What's the problem?



  • First of all, I don't understand why you need to get the camp_id value from the cadet when you're already sending it on the request.

    Second, when the hierarchical changes in the OBD system are transmitted, it is better to do so by doing one transaction. Your code would look like:

    # вставляем данные в campaign, только нужные поля, ключевые и так сгенерятся из sequence
    cursor.execute("INSERT INTO campaign (timestamp) VALUES (%s) RETURNING camp_id", [datetime.datetime.now()])
    # смотрим результат вставки, должно быть 'INSERT 0 1'
    print(cursor.statusmessage)
    camp_id = cursor.fetchone()[0]  # получаем новый идентификатор
    # вставляем наконец данные в зависимую таблицу
    cursor.execute("INSERT INTO blacklist (camp_id, name) VALUES (%s, %s)", (camp_id, site))
    # все манипуляции с данными закончены, можно подтвердить изменения
    connection.commit()
    

    And thirdly, why give the fields a table of names from the reserved words. So you can evade very strange mistakes in the most unexpected places.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2