CODEWITHSUNDEEP
pythonmysqlsqldatabase
Lord
Lord

Reputation: 153

SQL insert into.. where (python)

I have the following code:

def create_table():
        c.execute('CREATE TABLE IF NOT EXISTS TEST(SITE TEXT, SPORT TEXT, TOURNAMENT TEXT, TEAM_1 TEXT, TEAM_2 TEXT, DOUBLE_CHANCE_1X TEXT, DOUBLE_CHANCE_X2 TEXT, DOUBLE_CHANCE_12 TEXT, DRAW_1 TEXT, DRAW_2 TEXT DATE_ODDS TEXT, TIME_ODDS TEXT)')

create_table()

def data_entry():
    c.execute("INSERT INTO TEST(SITE, SPORT, TOURNAMENT, TEAM_1, TEAM_2, DOUBLE_CHANCE_1X, DOUBLE_CHANCE_X2, DOUBLE_CHANCE_12, DATE_ODDS, TIME_ODDS) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        (Site, sport.strip(), tournament.strip(), team_1.strip(), team_2.strip(), x_odd.strip(), y_odd.strip(), z_odd.strip(), Date_odds, Time_odds))
    conn.commit()

def double_chance():
    c.execute("UPDATE TEST SET DOUBLE_CHANCE_1X = x_odd, DOUBLE_CHANCE_X2 = y_odd, DOUBLE_CHANCE_12 = z_odd WHERE TOURNAMENT = tournament and TEAM_1 = team_1 and TEAM_2 = team_2 and DATE_ODDS = Date_odds and TIME_ODDS = Time_odds")
    conn.commit()

 driver.get(link)
 Date_odds = time.strftime('%Y-%m-%d')
 Time_odds = time.strftime('%H:%M')
 sport = (driver.find_element_by_xpath(".//*[@id='breadcrumb']/li[2]/a")).text   #example Footbal
 tournament = (driver.find_element_by_xpath(".//*[@id='breadcrumb']/li[4]/a")).text  #example Premier League
      try:
        div = (driver.find_element_by_xpath(".//*[@id='breadcrumb']/li[5]/a")).text    #to find any division if exists
      except NoSuchElementException:
        div = ""
        market = driver.find_element_by_xpath(".//*[contains(@id,'ip_market_name_')]")
        market_name = market.text
        market_num = market.get_attribute('id')[-9:]
        print market_num
        team_1 = (driver.find_element_by_xpath(".//*[@id='ip_marketBody" + market_num + "']/tr/td[1]//*[contains(@id,'name')]")).text
        team_2 = (driver.find_element_by_xpath(".//*[@id='ip_marketBody" + market_num + "']/tr/td[3]//*[contains(@id,'name')]")).text
        print sport, tournament, market_name, team_1, team_2
        data_entry() #first SQL call
        for ip in driver.find_elements_by_xpath(".//*[contains(@id,'ip_market3')]"):
            num = ip.get_attribute('id')[-9:]
            type = (driver.find_element_by_xpath(".//*[contains(@id,'ip_market_name_" + num + "')]")).text
            if type == 'Double Chance':
                print type
                print num
                x_odd = (driver.find_element_by_xpath(".//*[@id='ip_market" + num + "']/table/tbody/tr/td[1]//*[contains(@id,'price')]")).text
                y_odd = (driver.find_element_by_xpath(".//*[@id='ip_market" + num + "']/table/tbody/tr/td[2]//*[contains(@id,'price')]")).text
                z_odd = (driver.find_element_by_xpath(".//*[@id='ip_market" + num + "']/table/tbody/tr/td[3]//*[contains(@id,'price')]")).text
                print x_odd, y_odd, z_odd
                double_chance() #second SQL call

c.close()
conn.close()

Update:

Based on the answer below I updated the code, but I can't make it work.

When I run it, I get the following error:

sqlite3.OperationalError: no such column: x_odd

What should I do?

Update 2:

I found the solution:

I created an unique ID in order to be able to select exactly the row I want when I run the second SQL query. In this case it doesn't modify any other rows:

def double_chance():
    c.execute("UPDATE TEST SET DOUBLE_CHANCE_1X = (?), DOUBLE_CHANCE_X2 = (?), DOUBLE_CHANCE_12 = (?) WHERE ID = (?)",(x_odd, y_odd, z_odd, ID_unique))
    conn.commit()

Now it works perfectly.

Upvotes: 1

Views: 1337

Answers (1)

RajeshM
RajeshM

Reputation: 862

Use the UPDATE statement to update columns in an existing row.

UPDATE TEST SET DRAW_1=value1,DRAW_2=value2 WHERE column3=value3;

If data_entry(1) is always called first, then change the statement in data_entry_2() to UPDATE. If not you will need to check if the row exists in both cases and INSERT or UPDATE accordingly.

Upvotes: 1

Related Questions