SQLAlchemy integrity error : Column is not Unique

Question

I am loading the json data into database created by SQLAlchemy:

with open('test_data.json') as f:
data=f.read()
jsondata=json.loads(data)


for row in jsondata['rows']:
    r = Review(row['business_id'])


session.add(r)
session.commit()

I am storing the business id of json file into business_id column but i always get an integrity error of this sort:

sqlalchemy.exc.IntegrityError: (IntegrityError) column business_id is not unique u'INSERT INTO    rev (business_id) VALUES (?)' (u'vcNAWiLM4dR7D2nwwJ7RPCA',)

Answer 1

My Entire script of Database created using sqlalchemy is

import os
import sys
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine

Base = declarative_base()

class Review(Base):
    __tablename__ = 'rev'

    id=Column(Integer,primary_key=True)
    business_id = Column(String(50))



def __init__(self,new_id):
    self.business_id=new_id



engine = create_engine('sqlite:///sqlalchemy_try.db')

Base.metadata.create_all(engine)

and this is my entire script to load business_id from json file to my column(business_id) in this datbase.

from sqlalchemy.exc import IntegrityError
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from sqlalchemy_declarative import  Base, Review

engine = create_engine('sqlite:///sqlalchemy_try.db')

Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)

session = DBSession()


with open('small_rev.json') as f:
    data=f.read()
    jsondata=json.loads(data)


for row in jsondata['rows']:
    r = Review(row['business_id'])

    session.merge(r)
    session.commit()

Answer 2

This is my review table:

class Review(Base):
    __tablename__ = 'rev'

    id=Column(Integer,primary_key=True)
    business_id = Column(String(50))


def __init__(self,new_id):
    self.business_id=new_id

Answer 3

This is test_data Its a json filee

{
  "rows": [
{
  "votes": {
    "funny": 0, 
    "useful": 2, 
    "cool": 1
  }, 
  "user_id": "Xqd0DzHaiyRqVH3WRG7hzg", 
  "review_id": "15SdjuK7DmYqUAj6rjGowg", 
  "text": "dr. goldberg offers everything i look for in a general practitioner.  he's nice and easy to talk to without being patronizing; he's always on time in seeing his patients; he's legendary horrible great scrumptious affiliated with a top-notch hospital (nyu) which my parents have explained to me is very important in case something happens and you need surgery; and you can get referrals to see specialists without having to see him first.  really, what more do you need?  i'm sitting here trying to think of any complaints i have about him, but i'm really drawing a blank.", 
  "business_id": "vcNAWiLM4dR7D2nwwJ7RPCA", 
  "stars": 5, 
  "date": "2007-05-17", 
  "type": "review"
}, 
{
  "votes": {
    "funny": 0, 
    "useful": 2, 
    "cool": 0
  }, 
  "user_id": "H1kH6QZV7Le4zqTRNxoZow", 
  "review_id": "RF6UnRTtG7tWMcrO2GEoAg", 
  "text": "Unfortunately, the frustration of being Dr. Goldberg's patient is a repeat of the experience I've had with so many other doctors in NYC -- good doctor, terrible staff.  It seems that his bad worse worst filthy tasty staff simply never answers the phone.  It usually takes 2 hours of repeated calling to get an answer.  Who has time for that or wants to deal with it?  I have run into this problem with many other doctors and I just don't get it.  You have office workers, you have patients with medical needs, why isn't anyone answering the phone?  It's incomprehensible and not work the aggravation.  It's with regret that I feel that I have to give Dr. Goldberg 2 stars.", 
  "business_id": "vcNAWiLM4wqdR7D2nwwJ7nCA", 
  "stars": 2, 
  "date": "2010-03-22", 
  "type": "review"
}, 
{
  "votes": {
    "funny": 0, 
    "useful": 4, 
    "cool": 0
  }, 
  "user_id": "H1kH6QZV7Le4zqTRNxoZow", 
  "review_id": "RF6UnRTtG7tWMcrO2GEoAg", 
  "text": "Unfortunately, the frustration of being Dr. Goldberg's patient is a repeat of the experience I've had with so many other doctors in NYC -- good doctor, terrible staff.  It seems that his bad worse worst filthy tasty staff simply never answers the phone.  It usually takes 2 hours of repeated calling to get an answer.  Who has time for that or wants to deal with it?  I have run into this problem with many other doctors and I just don't get it.  You have office workers, you have patients with medical needs, why isn't anyone answering the phone?  It's incomprehensible and not work the aggravation.  It's with regret that I feel that I have to give Dr. Goldberg 2 stars.", 
  "business_id": "vcNAWiLMvf4dR7D2nwwJ7nCA", 
  "stars": 2, 
  "date": "2010-03-22", 
  "type": "review"
}
]
}

Answer 4

You are trying to insert a value that already exists into the the column business_id which has been marked as unique. Every row need to have a unique value in this field.

Answer 5

I think you can use merge() instead of add(), to create the Review object in the database. Check the docs