Unable to insert Unique values in sqlite databse

I want to insert unique value in CULTURES table. but it shows unique constrains failed due to dulplicate values in culs list.Please suggest me how to insert only unique values. I tried with Ignore syntax but because i need the lastrowid. So igore and replace is not a soloution for me.

import urllib.requestCULTURES
    from bs4 import BeautifulSoup
    import itertools
    import sqlite3
    def make_soup(url):

        thepage = urllib.request.urlopen(url)
        soupdata = BeautifulSoup(thepage, "html.parser")
        return soupdata
    soup = make_soup("https://someurl/tapes/tapes_fr_lst_pdt.jsp?sel=_")

    allrecords = soup.findAll('tr')
    recordsLength = len(allrecords)
    conn=sqlite3.connect('D:\drive')
    c=conn.cursor()
    #c.execute('''DROP TABLE IF EXISTS APPS''')
    #c.execute('''CREATE TABLE IF NOT EXISTS APPS (ID INTEGER PRIMARY KEY,APPNO #INTEGER UNIQUE,DOSE_MIN REAL,DOSE_MAX REAL,UNIT TEXT )''')
    c.execute('''DROP TABLE IF EXISTS CULTURES''')
    c.execute(''' CREATE TABLE IF NOT EXISTS CULTURES(ID INTEGER PRIMARY KEY,CULTURE TEXT UNIQUE )''')
    #c.execute('''DROP TABLE IF EXISTS ORGANSIMES_COMBATTUS''')
    #c.execute(''' CREATE TABLE IF NOT EXISTS ORGANSIMES_COMBATTUS(ID INTEGER PRIMARY #KEY,ORGANSIMES_COMBATTU TEXT UNIQUE)''')
    #c.execute('''DROP TABLE IF EXISTS APP_CUL_REL''')
    #c.execute('''CREATE TABLE IF NOT EXISTS 
    #              APP_CUL_REL(APP_ID INTEGER NOT NULL,CUL_ID INTEGER NOT NULL,
    #            PRIMARY KEY(APP_ID,CUL_ID)FOREIGN KEY(APP_ID) REFERENCES APPS(ID))
                ''')
    for index in range(3, recordsLength):

        record = allrecords[index].find_all('a')

        agri= [record[1].get('href')]

        culs=[]

        for url in agri:
            agripage=make_soup(url="https://someurl/"+url)
            #for items  in agripage:
            #alls=agripage.findAll("td")
            for row in agripage.find_all("tr"):

                col = row.find_all("td")
                try:  
                    #apss=[]
                    apps=col[0].text
                    culture=[col[1].text.strip('\xa0')]


                    for item in culture:

                       if item not in culs:
                            culs.append(item)
                            c.execute("INSERT INTO CULTURES VALUES (NULL,?)",[str(culs)])  

                except IndexError:
                    pass
    conn.commit()
    conn.close()
python-3.xlistbeautifulsoup

Answers

answered 3 months ago Gagan Deep Singh #1

I found the solution and insert uniquely just put an try except block as follows

for item in culture:
                    try:
                        c.execute("INSERT  INTO CULTURES VALUES(null,?)",([item]))
                        Cultu_pk=c.lastrowid
                    except Exception:
                        pass

comments powered by Disqus