Пример #1
0
def view_student(request):
	if request.POST:
		delete  = request.POST['id']
		cursor = db.cursor()
		cursor.execute("DELETE FROM student_teacher where sd_id = '"+delete+"';")
		cursor = db.cursor()
		cursor.execute("DELETE from student_subject where st_id = '"+delete+"';")
		cursor = db.cursor()
		cursor.execute("select dr.d_id,dr.name from doctor as dr,student as st where dr.d_id = st.dc_id and st.s_id = '"+str(delete)+"';")
		data_d = cursor.fetchall()
		cursor = db.cursor()
		cursor.execute("select ne.n_id,ne.name from nurse as ne,student as st where ne.n_id = st.nu_id and st.s_id = '"+str(delete)+"';")
		data_n = cursor.fetchall()
		cursor = db.cursor()
		if len(data_d)!=0:
			cursor.execute("update doctor set s_a = s_a - 1 where d_id = '"+str(data_d[0][0])+"';")
		cursor = db.cursor()
		if len(data_n)!=0:
			cursor.execute("update nurse set s_a = s_a - 1 where n_id = '"+str(data_n[0][0])+"';")
		cursor = db.cursor()
		cursor.execute("DELETE from student where s_id = '"+delete+"';")
		db.commit()
		messages.success(request,"Student Profile successfully deleted")

	students = []
	cursor = db.cursor()
	cursor.execute("SELECT * from student order by name")
	db.commit()
	details = []
	data = cursor.fetchall()
	if len(students)!=0:
		for i in students:
			details.append(studentClass(i))
	else:
		for i in data:
			cursor = db.cursor()
			cursor.execute("SELECT * from doctor where d_id = '"+str(i[8])+"'")
			db.commit()
			data1 = cursor.fetchall()
			cursor = db.cursor()
			cursor.execute("SELECT * from nurse where n_id = '"+str(i[7])+"'")
			db.commit()
			data2 = cursor.fetchall()
			nurse_name = "None"
			doctor_name = "None"
			if len(data2) != 0:
				nurse_name = str(data2[0][1])
			if len(data1) != 0:
				doctor_name = str(data1[0][1])	
			details.append(studentClass([i[0],i[1],i[2],i[3],i[4],i[5],i[6],doctor_name,nurse_name]))
	#details = {'students':students}
	return render(request,'LS/viewstudents.html',{'students':details})
Пример #2
0
def admin_nsf(request):
	if request.POST:
		delete = request.POST['id']
		if delete == "0":
			name = request.POST['name']
			salary = request.POST['salary']
			start_year = request.POST['start_year']
			contact_no = request.POST['contact_no']
			designation = request.POST['designation']
			image = request.FILES['image'].read()
			isadmin = request.POST['isadmin']
			cursor = db.cursor()
			cursor.execute("SELECT * from nontechnicalstaff where contact_no = '"+contact_no+"' and name = '"+name+"' and salary = '"+salary+"' and start_year = '"+start_year+"';")
			db.commit()
			data = cursor.fetchall()
			if len(data) != 0:
				messages.error(request,"Staff already registered.")
			else:
				cursor.execute("INSERT into nontechnicalstaff(name,salary,start_year,contact_no,designation,isadmin,image) values(%s,%s,%s,%s,%s,%s,%s);",(str(name),str(salary),str(start_year),str(contact_no),str(designation),str(isadmin),str(image) ))
				db.commit()
				try:
					cursor = db.cursor()
					cursor.execute("SELECT * FROM nontechnicalstaff")
					db.commit()
					data = cursor.fetchall()
					write_file(str(image),'./LS/static/temp/nsf_img'+str(data[-1][0])+'.jpg')
				except:
					pass
				messages.success(request,"Staff profile successfully added.")
		else:
			# cursor = db.cursor()
			# cursor.execute("delete from news where nsf_id = '"+delete+"';")
			# db.commit()
			cursor = db.cursor()
			cursor.execute("DELETE FROM nontechnicalstaff WHERE ns_id = '"+delete+"';")
			db.commit()
			messages.success(request,"Staff profile deleted.")


	cursor = db.cursor()
	cursor.execute("SELECT * FROM nontechnicalstaff")
	db.commit()
	data = cursor.fetchall()
	staff = []
	for i in data:
		img = "./temp/teacher_img"+str(i[0])+".jpg"
		staff.append(nstaffClass([i[0],i[1],i[2],i[3],i[4],i[5],i[6],img]))
	details = {'staff':staff}
	#details = {'t_id':data[0], 'name':data[1], 'salary':data[2], 'start_year':data[3], 'contact_no':data[4], 'image':data[5]}
	return render(request, 'LS/nsf.html', details)
Пример #3
0
 def testDatabase(self):
     self.assert_(os.path.exists, self.db)
     if isinstance(self.db_connect, dict):
         db = self.db.connect(**self.db_connect) 
     else:    
         db = self.db.connect(*self.db_connect) 
     try:
         c = db.cursor()
         # Check that data got imported
         c.execute("SELECT count(*) FROM county")
         self.assertEqual(c.fetchone(), (22,))
         c.execute("SELECT * FROM municipal WHERE municipal_id=1103")
         self.assertEqual(list(c.fetchall()), 
                         [(1103, "Stavanger", 11)])
         c.execute("SELECT * FROM postal WHERE postal_no=4042")
         # FIXME: BOOL with mysql might not equal False
         self.assertEqual(c.fetchone(),
                         (4042, "HAFRSFJORD", 1103, False))
         # Check if wtf-8 worked fine. Note that the line
         # -*- coding: utf-8 -*- must be present at the top of
         # this file for the u"Østfold" thingie to work
         c.execute("SELECT county_name FROM county WHERE county_id=1")
         a = c.fetchone()[0]
         if not isinstance(a, unicode):
             a = a.decode("utf8")
         self.assertEqual(a, u"Østfold")
     finally:
         db.rollback()
Пример #4
0
 def convertMySQLToMongo(self, MySQLdb):
     mongodb = MongoUtil()
     cursor = MySQLdb.cursor()
     cursor.execute("select * from wikiGraph")
     for row in cursor:
         mongodb.insertVertex(row[0])
         mongodb.addNeighbor(row[0], row[1])
Пример #5
0
def word_exists(word, pos_id):

    c = MySQLdb.cursor()

    sql = '''select id, word from word where word = '%s' and pos_id = %s''' % (word, pos_id)

    c.execute(sql)

    row = c.fetchone()
    if not row:
        return

    word_id = row[0]
    word = row[1]

    # get the attribute ids, keys, and values for this word
    sql = '''
select a.id, a.attrkey, wa.value
from word_attributes wa, attribute a
where wa.attribute_id = a.id and wa.word_id = %s
''' % word_id

    c.execute(sql)
    d = {}
    for row in c.fetchall():
        attrkey = row[1]
        d[attrkey + '_attribute_key'] = row[2]
        d[attrkey + '_attribute_id'] = row[0]

    d['word'] = word
    d['word_id'] = word_id

    print pprint.pformat(d)

    return d
Пример #6
0
    def addUser(self, id, email=None):
        if not findUser(id):
            raise ScalakError("User with given login doesn't exists.")

        db = openDB()
        c = db.cursor()
        res = c.execute("select user from user_project where user=%s and \
                project=%s limit 1", (id, self.id))
        if res:
            raise ScalakError("User is already project member.")

        c.execute("insert into user_project values (%s, %s)", (id, self.id))

        if not email:
            res = c.execute("select * from users where \
                    login=%s limit 1", (id,))
            if res:
                id, name, last_name, email, passwd, sha_passwd, \
                        note = c.fetchone()

        db.commit()
        c.close()

        for srv in self.getService():
            srv.addUser(id, name, last_name, email, passwd, sha_passwd, note)
Пример #7
0
def getUserRequests(user = None, project = None):
    """Returns 'project join' requests for given data or all requests"""

    db = openDB()
    cur = db.cursor()

    if project and user:
        res = cur.execute('select * from project_requests where user=%s and \
                project=%s limit 1', (user, project))

    elif not project and not user:
        res = cur.execute('select * from project_requests where user=%s \
                limit 1', (id, ))

    elif project and not user:
        res = cur.execute('select * from project_requests where \
                project=%s limit 1', (project, ))

    elif not project and user:
        res = cur.execute('select * from project_requests where user=%s and \
                limit 1', (user, ))

    db.close()

    return cur.fetchall()
Пример #8
0
def news(request):
	if request.POST:
		delete = request.POST['id']
		if delete == "0":
			heading = request.POST['heading']
			link = request.POST['link']
			nsf_id = request.POST['nsf_id']
			#timestamp = datetime.datetime.now()
			tz = pytz.timezone('Asia/Kolkata')
			timestamp = datetime.datetime.now(tz)
			timestamp = str(timestamp)
			timestamp = timestamp[0:timestamp.index('.')]
			cursor = db.cursor()
			cursor.execute("SELECT * from nontechnicalstaff where ns_id = '"+nsf_id+"';")
			db.commit()
			data = cursor.fetchall()
			cursor.execute("SELECT * from news where heading = '"+heading+"' and link = '"+link+"';")
			db.commit()
			data2 = cursor.fetchall()
			if len(data) == 0:
				messages.error(request,"Invalid staff ID.")
			elif len(data2) != 0: 
				messages.error(request,"News item already added.")
			elif data[0][6]==0:
				messages.error(request,"Staff not admin, hence cannot add news item")
			else:
				cursor.execute("INSERT into news(heading,link,timestamp,nsf_id) values('"+heading+"','"+link+"','"+timestamp+"','"+nsf_id+"');")
				db.commit()
				messages.success(request,"News item successfully added.")
		else:
			cursor = db.cursor()
			cursor.execute("DELETE FROM news WHERE ne_id = '"+delete+"';")
			db.commit()
			messages.success(request,"news item deleted.")

	cursor = db.cursor()
	cursor.execute("SELECT ne_id,heading,link,timestamp from news order by timestamp desc")
	db.commit()
	data = cursor.fetchall()
	news = []
	for i in data:
		news.append(newsClass([i[0],i[1],i[2],i[3]]))
	details = {'news':news}
	return render(request, 'LS/news.html',details)
Пример #9
0
def listUsers():
    """Return list of ids for all users"""

    db = openDB()
    c = db.cursor()

    res = c.execute("select login from users")
    db.close()

    return [x[0] for x in c.fetchall()]
Пример #10
0
def addUserRequest(user, project):
    """Add 'project join' requests"""

    db = openDB()
    c = db.cursor()

    c.execute('insert into project_requests values (%s, %s)',
            (user, project))

    db.close()
Пример #11
0
    def setRequest(self, user, accept):
        db = openDB()
        c = db.cursor()
        c.execute("delete from project_requests where project = %s and user = %s",
                (self.id, user))
        db.commit()
        c.close()

        if accept:
            self.addUser(user)
Пример #12
0
def subjects(request):
	# if user is admin, in that case
	if request.POST:
		delete = request.POST['id']
		if delete == "0":
			title = request.POST['title']
			t_id = request.POST['t_id']
			cursor = db.cursor()
			cursor.execute("SELECT * from teacher where t_id = '"+t_id+"';")
			db.commit()
			data = cursor.fetchall()
			cursor.execute("SELECT * from subject where title = '"+title+"';")
			db.commit()
			data2 = cursor.fetchall()
			if len(data) == 0:
				messages.error(request,"Invalid Teacher ID.")
			elif len(data2) != 0: 
				messages.error(request,"Subject already added.")
			else:
				cursor.execute("INSERT into subject(title,tr_id) values('"+title+"','"+t_id+"');")
				db.commit()
				messages.success(request,"Subject successfully added.")
		else:
			cursor = db.cursor()
			cursor.execute("DELETE from student_subject where sb_id = '"+delete+"';")
			db.commit()
			cursor = db.cursor()
			cursor.execute("DELETE FROM subject WHERE su_id = '"+delete+"';")
			db.commit()
			messages.success(request,"Subject Deleted.")

	# retrieving data		
	cursor = db.cursor()
	db.commit()
	cursor.execute("SELECT s.title,t.name,s.su_id FROM subject as s, teacher as t where t.t_id = s.tr_id")
	db.commit()
	subjects = cursor.fetchall()
	data = []
	for subject in subjects:
		data.append(subjectClass([subject[0],subject[1],subject[2]]))
	details = {'subjects':data}
	return render(request, 'LS/subjects.html',details)
Пример #13
0
def getCapabilities(user):

    db = openDB()
    c = db.cursor()

    res = c.execute("select action, scope from capabilities where user = %s",
            (user,))

    db.close()

    return c.fetchall()
Пример #14
0
    def getRequests(self):
        """User may create request to be accepted as a project member"""

        db = openDB()
        c = db.cursor()
        res = c.execute("select user, email from project_requests, users \
                where project=%s and user = login", (self.id,))
        res = c.fetchall()
        c.close()

        return res
Пример #15
0
def userProjects(id):
    """Returns all project for user with given _id_"""

    db = openDB()
    c = db.cursor()

    c.execute('select project from user_project where \
            user=%s', (id,))

    db.close()

    return c.fetchall()
Пример #16
0
def dashboard(request):
	if not request.user.is_authenticated():
		return HttpResponseRedirect(reverse('login'))
	if request.POST:
		pass1 = request.POST['pass1']
		pass2 = request.POST['pass2']
		cursor = db.cursor()
		username = str(request.user.username)
		#print username
		cursor.execute("SELECT * from auth_user where username = '******';")
		db.commit()
		data = cursor.fetchall()
		#print data
		if pass1 != pass2:
			messages.error(request, 'Entered passwords do not match.')
		else:
			cursor = db.cursor()
			#cursor.execute("INSERT into auth_user(username,first_name,last_name,password,email,is_staff,is_active,) values('"+username+"','"+first_name+"','"+last_name+"','"+pass1+"','"+email+"') ")
			user = User.objects.get(username = username)
			user.set_password(str(pass1))
			user.save()
			messages.success(request,"Password changed successfully.")
			return HttpResponseRedirect(reverse('dashboard'))

	cursor = db.cursor()
	username = str(request.user.username)
	cursor.execute("SELECT a.username,a.first_name,a.last_name,a.email,d.ContactNo,d.PAN FROM auth_user as a,donor as d where a.id = d.userid and a.username = '******';")
	db.commit()
	data = cursor.fetchall()
	donors = []
	for i in data:
		donors.append(donorClass([i[0],i[1],i[2],i[3],i[4],i[5]]))
	cursor.execute("SELECT dn.amount,dn.timestamp from donation as dn,donor as dr where dr.userid = dn.dr_id order by dn.timestamp desc")
	db.commit()
	data = cursor.fetchall()
	donations = []
	for i in data:
		donations.append(donationClass([i[0],i[1]]))
	details = {'donors':donors,'donations':donations}
	return render(request, 'LS/dashboard.html',details)
Пример #17
0
    def removeUser(self, id):
        if id == self.admin:
            raise ScalakError("You can't remove project administrator.")

        db = openDB()
        c = db.cursor()
        c.execute("delete from user_project where user=%s and project=%s",
                (id, self.id))
        db.commit()
        c.close()

        for srv in self.getService():
            srv.removeUser(id)
Пример #18
0
    def isMemeber(self, id):
        """Checking whether user with given id is project member"""

        db = openDB()
        c = db.cursor()
        c.execute("select login from user_project where user=%s and \
                project=%s limit 1", (id, self.id))
        db.commit()
        c.close()

        if c.fetchone():
            return True
        return False
Пример #19
0
def donate(request):
	if not request.user.is_authenticated():
		return HttpResponseRedirect(reverse('login'))
	cursor = db.cursor()
	username = str(request.user.username)
	cursor.execute("SELECT a.username,a.first_name,a.last_name,a.email,d.ContactNo,d.PAN FROM auth_user as a,donor as d where a.id = d.userid and a.username = '******';")
	db.commit()
	data = cursor.fetchall()
	donors = []
	for i in data:
		donors.append(donorClass([i[0],i[1],i[2],i[3],i[4],i[5]]))
	details = {'donors':donors}
	return render(request, 'LS/donate.html',details)
Пример #20
0
 def add_user(db):
     c = db.cursor()
     c.execute("delete from session_attribute where sid=%s", (login,))
     c.execute("delete from permission where username=%s", (login,))
     c.execute("insert into session_attribute (sid, authenticated, \
             name, value) values (%s, 1, 'email', %s)", (login, email))
     c.execute("insert into session_attribute (sid, authenticated, \
             name, value) values (%s, 1, 'name', %s)", (login, 
             "{0} {1}".format(name, last_name)))
     c.execute("insert into permission values (%s, \"BROWSER_VIEW\")", \
             (login,))
     db.commit()
     c.close()
def reviewdata_insert(db):
    with open('./../../user.pkl', 'rb') as f:
        user_info = pickle.load(f)
        print(len(user_info))
        for item in user_info:
            result = []
            result.append(
                (item['user_id'], item['name'], item['review_count'],
                 item['yelping_since'], item['fans'], item['average_stars']))
            inesrt_re = "insert into user(user_identifier, user_name, review_count, yelping_since, fans, average_stars) values (%s, %s, %s, %s, %s, %s)"
            cursor = db.cursor()
            cursor.executemany(inesrt_re, result)
            db.commit()
Пример #22
0
def home(request):
	cursor = db.cursor()
	cursor.execute("SELECT ne_id,heading,link,timestamp from news order by timestamp desc;")
	db.commit()
	data = cursor.fetchall()
	if len(data) > 5:
		data = data[:5]
	news = []
	for i in data:
		news.append(newsClass([i[0],i[1],i[2],i[3]]))

	cursor = db.cursor()
	cursor.execute("SELECT d.amount,a.first_name,a.last_name from donation as d,auth_user as a order by d.timestamp desc")
	db.commit()
	data = cursor.fetchall()
	if len(data) > 5:
		data = data[:5]
	donation = []
	for i in data:
		donation.append(donationClass([i[0],i[1],i[2]]))

	details = {'news':news, 'donation':donation}
	return render(request, 'LS/home.html', details)
Пример #23
0
def connect(db=None):
    try:
        sqlconf = __salt__['soy_mysql.setup'](db)
        conn = sql.connect(**sqlconf)
        curs = sql.cursor()
        return conn, curs
    except sql.Error as e:
        warn('mysql error: %s' % e.message, RuntimeWarning)
        return False
    except:
        warn('error while establishing connection', RuntimeWarning)
        return False
    finally:
        if conn:
            conn.close()
Пример #24
0
 def add_user(db):
     c = db.cursor()
     c.execute("delete from session_attribute where sid=%s", (login, ))
     c.execute("delete from permission where username=%s", (login, ))
     c.execute(
         "insert into session_attribute (sid, authenticated, \
             name, value) values (%s, 1, 'email', %s)", (login, email))
     c.execute(
         "insert into session_attribute (sid, authenticated, \
             name, value) values (%s, 1, 'name', %s)",
         (login, "{0} {1}".format(name, last_name)))
     c.execute("insert into permission values (%s, \"BROWSER_VIEW\")", \
             (login,))
     db.commit()
     c.close()
Пример #25
0
def getUserData(id):
    """Get all public user data (without pass hashs)"""

    db = openDB()
    c = db.cursor()

    if not findUser(id):
        raise ScalakError("User doesn't exists")

    c.execute('select login, name, last_name, email, note from users where \
            login=%s limit 1', (id,))

    db.close()

    return c.fetchone()
Пример #26
0
def valid_password(env, username, password):
    """Check given auth data against DB; env may be None"""

    db = openDB()
    c = db.cursor()

    res = c.execute("select password from users where login=%s limit 1",
            (username,))

    if not res:
        return False

    res = c.fetchone()[0]
    db.close()

    return res == htpasswd(password, res[0] + res[1])
Пример #27
0
def updateSQL(query,ipaddress,database,user,password):
    import MySQLdb
    # Open database connection
    db = MySQLdb.connect(ipaddress,user,password,database)

    # prepare a cursor object using cursor() method
    cursor = db.cursor()

    # execute SQL query using execute() method.
    numrows = cursor.execute(query);
    
    db.commit()

    db.close()
    
    return numrows
Пример #28
0
def getAdmin(project):
    """Returns admin for given _project_"""

    db = openDB()
    c = db.cursor()

    res = c.execute('select admin from projects where id=%s limit 1', (project,))

    if not res:
        return False

    res = c.fetchone()[0]

    db.close()

    return res
def prem(db):
    cursor = db.cursor()
    cursor.execute("SELECT VERSION()")
    data = cursor.fetchone()
    print("Database version : %s " % data)
    cursor.execute("DROP TABLE IF EXISTS user")
    sql = """CREATE TABLE user (
             user_id INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
             user_identifier VARCHAR(100),
             user_name VARCHAR(100),
             review_count INT,
             yelping_since VARCHAR(100),
             fans INT,
             average_stars FLOAT,
             PRIMARY KEY (user_id)
             )
             """
    cursor.execute(sql)
Пример #30
0
def findUser(id, project = None):
    """Check if user with given _id_ exists globally or in _project_ if given"""

    db = openDB()
    c = db.cursor()

    if not project:
        res = c.execute("select login from users where login=%s limit 1", (id,))

    else:
        res = c.execute("select login from users, user_project where login=%s \
                and login=user and project=%s limit 1", (id, project))

    db.close()

    if not res:
        return False

    return True
Пример #31
0
def login(request):
	if request.user.is_authenticated():
		return HttpResponseRedirect(reverse('dashboard'))
	if request.POST:
		username = request.POST['username']
		password = request.POST.get('password')
		if username== '':
			return render(request, 'LS/login.html')
		cursor = db.cursor()
		cursor.execute("SELECT * from auth_user where username='******';")
		db.commit()
		data = cursor.fetchall()
		if data is not None:				# Check if user is registered
			user = auth.authenticate(username=username, password=password)			# Authenticates the username and password
			if user is not None:
				auth.login(request, user)											# Logs in
				return HttpResponseRedirect(reverse('dashboard'))
			else:																	# User exists but password is incorrect
				messages.error(request, 'The username and password combination is incorrect.')
		else:
			messages.error(request, 'ID not registered.')
	return render(request, 'LS/login.html')
Пример #32
0
#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as db
import string
from apnsclient import *

db = db.connect(host="myhost", user="******", passwd="psswd", db="base", charset='utf8')

c = db.cursor()
c.execute(""" SELECT  s.* FROM `push`.`push_subscribers` s where device_type = 'apns'   and last_time > unix_timestamp() - 90*24*60*60
  and disable = 'N'  
  or   device_id = '32hex device id ' 
  order by last_time """)

result = c.fetchall()

session = Session()
con = session.get_connection("push_production", cert_file="production_full.pem")
kwargs = { "aps" : {'sound':'default' , 'badge':1 , 'alert': 'test' , 'type':0 , 'id':'6074161'}}

for row in result: 
		  print row[2]
		  message = Message(row[2],payload=kwargs)
		  srv = APNs(con)
		  res = srv.send(message)
            dias = map(int, track.split())

            if isTrueTrack(diasToSsmIds, dias):
                for dia in dias:
                    attributedDias.add(dia)

            track = inf.readline()
            if count % 10000 == 0:
                sys.stderr.write("so far, read %d lines...\n" % count)

    return attributedDias


if __name__ == "__main__":
    db = db.connect(user=DB_USER, passwd=DB_PASS, db=DB_DB)
    curs = db.cursor()

    inFiles = []
    # read each argument. It may be a glob.
    for arg in sys.argv[1:]:
        newInFiles = glob.glob(arg)
        # open each file to make sure it exists.
        for inf in newInFiles:
            sys.stderr.write("Opening %r as input file\n" % inf)
            inFiles.append(file(inf, 'r'))
            sys.stderr.write("Opened it \n")

    # make a diaSource -> ssmId map
    sys.stderr.write("Building map of diaSources -> ssmIds...\n")
    diasToSsmIds = makeDiaToSsmIdMap(curs)
            if isTrueTrack(diasToSsmIds, dias):
                for dia in dias:
                    attributedDias.add(dia)

            track = inf.readline()
            if count % 10000 == 0:
                sys.stderr.write("so far, read %d lines...\n" % count)

    return attributedDias



if __name__=="__main__":
    db = db.connect(user=DB_USER, passwd=DB_PASS, db=DB_DB)
    curs = db.cursor()

    inFiles = []
    # read each argument. It may be a glob. 
    for arg in sys.argv[1:]: 
        newInFiles = glob.glob(arg)
        # open each file to make sure it exists.
        for inf in newInFiles:
            sys.stderr.write("Opening %r as input file\n" % inf)
            inFiles.append(file(inf,'r'))
            sys.stderr.write("Opened it \n")



    # make a diaSource -> ssmId map
    sys.stderr.write("Building map of diaSources -> ssmIds...\n")
Пример #35
0
 def remove_user(db):
     c = db.cursor()
     c.execute("delete from session_attribute where sid=%s", (id,))
     c.execute("delete from permission where username=%s", (id,))
Пример #36
0
    def prepareTables(self):
        # First connect raw
        if isinstance(self.db_connect, dict):
            db = self.db.connect(**self.db_connect) 
        else:    
            db = self.db.connect(*self.db_connect) 
        if self.db_mod == "postgresql":
            db.autocommit(1)
        try:
            c = db.cursor()
            if "sqlite" in str(db).lower(): 
                # Ignore sync-checks for faster import
                c.execute("pragma synchronous = off")
            # Find our root by inspecting our own module
            import testforgetsql2
            root = os.path.dirname(testforgetsql2.__file__)
            file = os.path.join(root, "test-data.sql")
            sql = codecs.open(file, encoding="utf8").read()

            # DROP TABLE
            if self.db_mod == "mysql":
                for table in ("county", "municipal", "postal", "insertion", 
                              "shop", "changed"):
                    c.execute("DROP TABLE IF EXISTS %s" % table)
            elif self.db_mod == "postgresql":
                c.execute("""SELECT tablename FROM pg_catalog.pg_tables 
                                 WHERE schemaname=pg_catalog.current_schema()""")        
                existing = c.fetchall()
                for table in ("county", "municipal", "postal", "insertion", 
                              "shop", "changed"):
                    if (table,) in existing:
                        c.execute("DROP TABLE %s" % table)                  
            elif self.db_mod == "sqlite":            
                # No need to drop tables in sqlite, we blank out the db each
                # time
                pass
            else:
                raise "Unknown db", self.db_mod

            # CREATE TABLE // EXECUTE
            if self.db_mod == "sqlite":
                # We have to fake since sqlite does not support the
                # fancy "bool" type.
                sql = sql.replace("FALSE", "0")
                sql = sql.replace("TRUE", "1")
                c.executescript(sql)
            elif self.db_mod in ("mysql", "postgresql"):
                for statement in sql.split(";"):
                    if not statement.strip():
                        continue # Skip empty lines
                    c.execute(statement.encode("utf8"))

            # Create database specific table "insertion"
            if self.db_mod == "sqlite":
                # This one is seperate because of "AUTOINCREMENT" vs "AUTO_INCREMENT"
                c.execute("""
                    CREATE TABLE insertion (
                      insertion_id INTEGER PRIMARY KEY AUTOINCREMENT,
                      value VARCHAR(15)
                    )""")
            elif self.db_mod == "mysql":                 
                c.execute("""
                    CREATE TABLE insertion (
                      insertion_id INTEGER PRIMARY KEY AUTO_INCREMENT,
                      value VARCHAR(15)
                    )""")
            elif self.db_mod == "postgresql":
                c.execute("""
                    CREATE TABLE insertion (
                      insertion_id SERIAL PRIMARY KEY,
                      value VARCHAR(15)
                    )""")
            else:
                raise "Unknown db", self.db_mod
            db.commit()    
        finally:
            db.rollback()         
    cursor.execute(sql)


def reviewdata_insert(db):
    with open('./../../user.pkl', 'rb') as f:
        user_info = pickle.load(f)
        print(len(user_info))
        for item in user_info:
            result = []
            result.append(
                (item['user_id'], item['name'], item['review_count'],
                 item['yelping_since'], item['fans'], item['average_stars']))
            inesrt_re = "insert into user(user_identifier, user_name, review_count, yelping_since, fans, average_stars) values (%s, %s, %s, %s, %s, %s)"
            cursor = db.cursor()
            cursor.executemany(inesrt_re, result)
            db.commit()


if __name__ == "__main__":
    con = db.connect(user="******", passwd="Zty+19941007"
                     )  # your account and password should be chaned here
    cur = con.cursor()
    cur.execute('CREATE DATABASE yelptest')
    db = pymysql.connect(
        'localhost', 'tyrozty', 'Zty+19941007', 'yelptest',
        charset='utf8')  # your account and password should be chaned here
    cursor = db.cursor()
    prem(db)
    reviewdata_insert(db)
    cursor.close()
Пример #38
0
import MySQLdb

conexao_mysql = MySQLdb(host ='localhost', database='zuplae01', user='******',passwd='luquinhas19')


cursor = conexao_mysql.cursor()

cursor.execute('SELECT * FROM TIPO')

#124 sample
classifier.setdb('test_100.db')

#List operations: for i in length of features classify
##classifier.classify(tweet_features[i])

#single test
location_text = 'Beverly Hills'
classified_zipcode = classifier.classify('Beverly Hills')

zipcode = str(classified_zipcode[0])
confidence = str(classfied_zipcode[1])

#query MYSQL database and append to python list
db = MySQLdb('')
cur = db.cursor()
cur.execute("SELECT latitude,longitude FROM sqlbook.zipcounty WHERE zipcode = %s", zipcode)
rows = cur.fetchall()
latitude = rows[0][0]
longitude = rows[0][1]

likely_zipcode = {"location_text": location_text, "zipcode": zipcode, "latitude": latitude, "longitude": longitude}
weather = get__weather_data.getWeather(likely_zipcode['zipcode'])

likely_zipcode.update(weather)
print likely_zipcode

#if not close enough
#select 10 close locations to the one classified.

cur.execute("""SELECT z.zipcode, z.state, zco.poname, distcirc, population, hh,