from urllib.request import urlopen
from urllib.request import urlretrieve
from urllib.parse import quote
from bs4 import BeautifulSoup
import json
import re
import psycopg2
import time
import random
from contextlib import closing
from lxml.html.soupparser import fromstring

conn=psycopg2.connect(dbname='calority',user='admin',password='228322',host='127.0.0.1',port='')

cursor=conn.cursor()
def create_table():
    cursor.execute("CREATE TABLE IF NOT EXISTS Calority(id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,name text NOT NULL,ccal integer NOT NULL);")
#create_table()


def Parce():
    table=urlopen("http://www.frs24.ru/st/tablica-kalorijnosti-produktov-pitaniya/")
    bs= BeautifulSoup(table, features = "lxml")
    text=getTable(bs)
    return text

def getTable(bs):
    table=bs.findAll("table",{"class":"norm"})
    names=[]
    ckals=[]
    for nam in table:
        name=nam.findAll("tr")
        for namess in name:
            #.find("td")
            name1=namess.find("td")
            names.append(name1.get_text())#.previous
        for ckal in name:
            ck=ckal.find("td")
            ck=ck.find_next_siblings("td")
            ckals.append(ck[0].get_text())
    tables={}
    for val in range(len(names)):
        try:

            tables[names[val]]=int(ckals[val])
            #print(val,names[val],ckals[val],tables[names[val]])
        except:
            pass
    rand=random.randint(0,508)
    print(len(ckals),len(names),ckals[rand],tables[names[rand]])
    return tables
def BaseInt():
    strs='Айва'
    cursor.execute("SELECT ccal FROM calority WHERE name = '"+str(strs)+"';")
    res=cursor.fetchall()
    return res

def WriteTeble(table):
    #cursor.execute("INSERT INTO calority (id,name,ccal) VALUES (%s,%s,%s)",(1,"рис",130))
    c=1
    for k,v in table.items():
        c+=1
        print(k,v)
        cursor.execute("INSERT INTO calority(id,name,ccal) VALUES(%s,%s,%s)",(c,str(k),int(v)))
    cursor.execute("SELECT * FROM calority ;")
    res=cursor.fetchone()
    conn.commit()
    return res
#WriteTeble(Parce())
#print(BaseInt())
conn.close()
#Parce()
