def test_dim_course(self):
mydb = connection.con()
mycursor = mydb.cursor()
sql = '''select * from course_details where not exists(select * from test_course_details)'''
res = mycursor.execute(sql)
assert res == None
def getExams(source):
db =connection.con()
cursor=db.cursor()
soup=BeautifulSoup(source.replace("</a></span>","</a>"),'html.parser')
div = soup.findAll('div',{'class':'exam-det'})
# print div
for d in div :
examsoup = BeautifulSoup(str(d),'html.parser')
nametag = examsoup.find('h2')
namesoup = BeautifulSoup(str(nametag),'html.parser')
name=namesoup.find('a').text
course=namesoup.find('span',{'class':'course-box'}).text
course=course.replace('(','').replace(')','')
#print "Name: "+name
# print "Course: "+course
#to get the fullform of exam
#full = ""
full = examsoup.find('p',{"class":""}).text
print full
#full=full.text
# print "Full : "+full
#to get the exam type
lis = examsoup.findAll('p',{'class':'exams-p'})
pattern = lis[0].text
date = lis[1].text
collegessoup= BeautifulSoup(str(lis[2]),'html.parser')
collegelink = collegessoup.find('a').attrs['href']
# print "Patterns : "+ pattern
print "Date : "+ date
lis=[]
try:
lis=getColleges(str(collegelink))
except Exception as x:
print x
sql = """INSERT INTO exams(name,course,fullform,pattern,date,colleges) VALUES ("%s","%s","%s","%s","%s",'%s')"""%(name,course,full,pattern,date,json.dumps(lis))
cursor.execute(sql)
db.commit()
print " \n\n\n======================== \n\n\n "
db.close()
def get():
import connection as c
db,de = c.con()
from IPython.display import display
import pandas as pd
import datetime as dt
import warnings
warnings.filterwarnings("ignore")
import matplotlib.pyplot as plt
import matplotlib
matplotlib.style.use('ggplot')
matplotlib.rcParams['figure.figsize'] = (12.0, 5.0)
import numpy as np
import insertest as insert
import logging as l
l.getLogger("FFC").setLevel(l.ERROR)
return c, db, de, display, pd, dt, plt, np, insert, l
def callFun():
db =connection.con()
cursor=db.cursor()
sql = "TRUNCATE exams"
cursor.execute(sql)
db.commit()
month= int(time.strftime("%m"))
for i in range(month,13):
try:
print "\n Month %s Started \n"%str(i)
urlFun(i)
print "\n Month %s Completed \n"%str(i)
except Exception as x:
print x
print "----"
def __init__(self):
"""Connection with the database is established and the functions for table creation are invoked"""
try:
l = logfile.logger()
mydb = connection.con()
mycursor = mydb.cursor()
self.createSTUDENT_REFERENCE(mydb, mycursor)
self.createFpayment(mydb, mycursor)
self.createFacademics(mydb, mycursor)
self.createFstudent(mydb, mycursor)
self.createFattendence(mydb, mycursor)
self.createFplacement(mydb, mycursor)
self.PaymentDetails(mydb, mycursor)
self.Degree(mydb, mycursor)
self.ExamDetails(mydb, mycursor)
self.GradeDetails(mydb, mycursor)
self.CourseDetails(mydb, mycursor)
self.DepartmentDetails(mydb, mycursor)
self.StaffDetails(mydb, mycursor)
self.CalenderDetails(mydb, mycursor)
self.PlacementDetails(mydb, mycursor)
# self.insertSTUDENT_REFERENCE(mydb, mycursor)
# self.insertFpayment(mydb,mycursor)
# self.insertFacademics(mydb,mycursor)
# self.insertFplacement(mydb,mycursor)
# self.insertFstudent(mydb,mycursor)
# self.insertFattendence(mydb,mycursor)
# self.InsertPaymentDetails(mydb, mycursor)
# self.InserDegree(mydb, mycursor)
# self.InsertExamDetails(mydb, mycursor)
# self.InsertGradeDetails(mydb, mycursor)
# self.InsertCourseDetails(mydb, mycursor)
# self.InsertDeptDetails(mydb, mycursor)
# self.InsertStaffDetails(mydb, mycursor)
# self.InsertCalendarDetails(mydb, mycursor)
# self.InsertPlacementDetails(mydb, mycursor)
except Exception as e:
print("Error:",e)
def test_fact_academics(self):
mydb = connection.con()
mycursor = mydb.cursor()
sql = '''select * from factacademics where not exists(select * from test_factacademics)'''
res = mycursor.execute(sql)
assert res == None
def test_dim_degree(self):
mydb = connection.con()
mycursor = mydb.cursor()
sql = '''select * from degree where not exists(select * from test_degree)'''
res = mycursor.execute(sql)
assert res == None
def test_fact_student(self):
mydb = connection.con()
mycursor = mydb.cursor()
sql = '''select * from factstudent where not exists(select * from test_factstudent)'''
res = mycursor.execute(sql)
assert res == None
def gettitle(d,teams):
import MySQLdb
import urllib2
import connection
db =connection.con()
cursor=db.cursor()
page=urllib2.urlopen(d)
cont=page.read()
soup = BeautifulSoup(cont)
content=str(soup.title.string)
desc = soup.find('meta',{'name':'description'}).attrs['content']
desc=str(desc).replace('&','&')
# print desc
print "teams "+teams
if '(' in str(content):
score=str(content).split('(',1)
score1=score[0].split('>',1)
print score1[0]
score=score[1].split(')',1)
status=['']
try:
status=cont.split('<div class="innings-requirement">',1)
status=status[1].split('</div>',1)
except:
pass
status=status[0].strip()
score=score[0].split(',')
bat=[]
bowl=''
for s in score:
if '/' in s:
# print "bowling : "+str(s)
bowl=str(s)
else:
# print "batting : "+str(s)
bat.append(str(s))
num=d.split('/')
# print len(num)
num=num[len(num)-1]
# print num
cursor.execute("SELECT * FROM cricket WHERE matchno='%s'"%str(num).replace('.html',''))
check=cursor.fetchall()
if check:
# print "entered"
# print score1[0].split()[0]
# print check[0][4]
# print bat
print desc
if (score1[0].split()[0] in check[0][4]) and len(bat)==3:
cursor.execute("UPDATE cricket SET score='%s',teams='%s',overs='%s',bat1='%s',bat2='%s',bowl='%s',status='%s',description='%s' WHERE matchno='%s'"%(score1[0].strip(),teams,bat[0],bat[1],bat[2],bowl,status,desc,str(num).replace('.html','')))
db.commit()
print "updated"
elif (score1[0].split()[0] not in check[0][4]) and len(bat)==3:
cursor.execute("UPDATE cricket SET score='%s',score1='%s',teams='%s',overs='%s',bat1='%s',bat2='%s',bowl='%s',status='%s',description='%s' WHERE matchno='%s'"%(score1[0].strip(),check[0][4],teams,bat[0],bat[1],bat[2],bowl,status,desc,str(num).replace('.html','')))
db.commit()
print "updated1"
elif (score1[0].split()[0] in check[0][4]) and len(bat)==2:
cursor.execute("UPDATE cricket SET score='%s',teams='%s',overs='%s',bat1='%s',bowl='%s',status='%s',description='%s' WHERE matchno='%s'"%(score1[0].strip(),teams,bat[0],bat[1],bowl,status,desc,str(num).replace('.html','')))
db.commit()
print "updated2"
elif (score1[0].split()[0] not in check[0][4]) and len(bat)==2:
cursor.execute("UPDATE cricket SET score='%s',score1='%s',teams='%s',overs='%s',bat1='%s',bowl='%s',status='%s',description='%s' WHERE matchno='%s'"%(score1[0].strip(),check[0][4],teams,bat[0],bat[1],bowl,status,str(num).replace('.html',''),desc))
db.commit()
print "updated3"
# db.commit()
else:
if len(bat)==3:
cursor.execute("INSERT INTO cricket (teams,score,overs,bat1,bat2,bowl,matchno,status,description) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s')"%(teams.replace('&','&'),score1[0],bat[0],bat[1],bat[2],bowl,str(num).replace('.html',''),status,desc))
db.commit()
elif len(bat)==2:
cursor.execute("INSERT INTO cricket (teams,score,overs,bat1,bowl,matchno,status,description) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s')"%(teams.replace('&','&'),score1[0],bat[0],bat[1],bowl,str(num).replace('.html',''),status,desc))
db.commit()
def parse(data):
result = []
parser = HTMLParser()
parser.handle_data = result.append
parser.feed(data)
data= "".join(result)
parser.close()
return data
import urllib2
import re
from HTMLParser import HTMLParser
import MySQLdb
import connection
db =connection.con()
cursor=db.cursor()
page=urllib2.urlopen("http://www.x-rates.com/table/?from=INR&amount=1.00")
data=page.read()
data=data.split("</table>")
data=data[1].split("<tbody>")
data=data[1].split("</tbody>")
tr=re.findall("<tr",data[0])
data="a"+data[0]
data=data.split("a",1)
for t in tr:
a=[]
for i in range(3):
data=data[1].split("<td",1)
data=data[1].split(">",1)
data=data[1].split("</td>",1)
cont=parse(data[0])
a.append(cont)
import connection as c
db,de = c.con()
from IPython.core.display import HTML
def go(name = 'John Doe', age=46, country='China',gender=None):
table = db['user']
# Insert a new record.
table.insert(dict(name=name, age=age, country=country,gender=gender))
return HTML('<h4>Inserted into database succesfully...</h4>')
#df.to_sql("my_table_name", engine)
