import mysql.connector as sql
import pandas as pd
import re
import numpy as np
from PIL import Image
from wordcloud import WordCloud
yhendus=sql.connect(host='localhost', database='dh17_laulupidu', user='dh17', password='dh17praktika')
df=pd.read_sql("""
     SELECT tabel4.value AS marksona
   FROM efis_keyword_translation AS tabel1 
    JOIN efis_film_keyword AS tabel2 ON tabel1.keyword_id=tabel2.keyword_id
	JOIN efis_film_keyword AS tabel3 ON tabel2.film_id=tabel3.film_id
	JOIN efis_keyword_translation AS tabel4 ON
	     tabel3.keyword_id=tabel4.keyword_id
	 WHERE tabel1.language='et' AND tabel1.value='Üldlaulupeod' AND tabel4.language='et'
	 LIMIT 100;
   """, yhendus)
#print(df)
marksonad=df.marksona.values.tolist()
marksonatekst=" ".join(marksonad)

#genereerin marksonapilve
mask = np.array(Image.open("noot.jpg"))

pilv=WordCloud(background_color="white", collocations=False, mask=mask)
pilv=pilv.generate(marksonatekst)

pilt=pilv.to_image()
pilt.save('/home/annika/public_html/kval_dh/ELU_projekt/koik_marksonad.png')