def studentData():
con = sqlite3.connect("student.db")
cur.execute(
"CREATE TABLE IF NOT EXISTS student(ID INTEGER PRIMARY KEY, STUDENT_ID text, FIRST_NAME text, LAST_NAME text, DATE_OF_BIRTH text, AGE text,GENDER text, ADDRESS text,MOBILE_NO text )"
)
con.commit()
con.close()
def viewData():
con = sqlite3.connect("student.db")
cur = con.cursor('*')
cur.execute("SELECT * FROM student")
row = cur.fetchall()
con.close()
return row
def addstdRec(STUDENT_ID, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, AGE, GENDER,
ADDRESS, MOBILE_NO):
con = sqlite3.connect("student.db")
cur = con.cursor('*')
cur.execute("INSERT INTO student VALUES(NULL, ?,?,?,?,?,?,?,?)",
STUDENT_ID, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, AGE, GENDER,
ADDRESS, MOBILE_NO)
con.commit()
con.close()
def dataUpdate(id,
STUDENT_ID="",
FIRST_NAME="",
LAST_NAME="",
DATE_OF_BIRTH="",
AGE="",
GENDER="",
ADDRESS="",
MOBILE_NO=""):
con = sqlite3.connect("student.db")
cur.execute("UPDATE student SET STUDENT_ID=? OR FIRST_NAME=? OR LAST_NAME=? OR DATE_OF_BIRTH=? OR AGE= ? OR GENDER=? OR ADDRESS=? OR MOBILE_NO=?, WHERE id=?",\
(STUDENT_ID , FIRST_NAME , LAST_NAME , DATE_OF_BIRTH , AGE ,GENDER , ADDRESS ,MOBILE_NO, id))
con.commit()
con.close()
def searchData(STUDENT_ID="",
FIRST_NAME="",
LAST_NAME="",
DATE_OF_BIRTH="",
AGE="",
GENDER="",
ADDRESS="",
MOBILE_NO=""):
con = sqlite3.connect("student.db")
cur = con.cursor()
cur.execute(
"SELECT * FROM student WHERE STUDENT_ID=? OR FIRST_NAME=? OR LAST_NAME=? OR DATE_OF_BIRTH=? OR AGE= ? OR GENDER=? OR ADDRESS=? OR MOBILE_NO=?",
(STUDENT_ID, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, AGE, GENDER,
ADDRESS, MOBILE_NO))
rows = cur.fetchall()
con.close()
return rows
def register():
try:
form = RegistrationForm(request.form)
if request.method == 'POST' and form.validate():
title = form.title.data
username = form.username.data
email = form.email.data
password = sha256_crypt.encrypt(str(form.password.data))
real_name = form.name.data
db, cur = get_db()
x = cur.execute('SELECT * FROM user WHERE username = ?',
[username])
print(x)
if x is not None and x.fetchall():
flash("That username is already taken, please choose another")
return render_template('register.html', form=form)
else:
cur.execute(
"INSERT INTO user (title, username, email, password, real_name) VALUES(?,?,?,?,?)",
[title, username, email, password, real_name])
db.commit()
flash("Thanks for registering!")
cur.close()
db.close()
gc.collect() # collect garbage
session['logged_in'] = True
session['username'] = username
return redirect(url_for('home'))
return render_template('register.html', form=form)
except Exception as e:
return str(e)
def InsertInto():
UsrInf = UserInfo()
print('Connecting to database...')
cur = genDBCursor()
print('Creating Table')
SQL = "CREATE TABLE UserCountState_Stg( \
UserId DECIMAL(18, 0),\
Count DECIMAL(18, 0)\
);"
cur.execute(SQL)
print('Done Creating table')
print('Inserting Data')
for i in range(len(UsrInf)):
searchTerms = { 'User': UsrInf[i][0], \
'Count': UsrInf[i][1] \
}
queryBld = f"INSERT INTO UserCountState_Stg VALUES ({searchTerms['User']},{searchTerms['Count']})"
cur.execute(queryBld)
print('Done Data')
SQL2 = "CREATE TABLE FinalUser_Stg( \
Count DECIMAL(18, 0),\
UserId DECIMAL(18, 0),\
States varchar(10) \
);"
cur.execute(SQL2)
SQL3 = "select USERID, COUNT\
from AOL_SCHEMA.USERCOUNTSTATE_STG;"
smt = cur.execute(SQL3)
users = []
for row in smt:
users.append((row[0], row[1]))
print("ahora viene el mierdero")
for x in range(len(users)):
KeyTerms = { 'User': users[x][0], \
'Count': users[x][1] \
}
Item = KeyTerms['User']
Item = str(Item)
print(Item)
Query = "SELECT COUNT(*) conteo ,FACTS.ANONID usr,STATES.STATE_ABBR ste \
FROM FACTS \
JOIN QUERYDIM on QUERYDIM.ID = FACTS.QUERYID \
JOIN STATES on STATES.ID=QUERYDIM.STATE_ID \
WHERE (QUERYDIM.QUERY LIKE '%1040%' \
OR QUERYDIM.QUERY LIKE '%tax forms%' \
OR QUERYDIM.QUERY LIKE '% irs %' \
OR QUERYDIM.QUERY LIKE '% dmv %' \
OR QUERYDIM.QUERY LIKE '% DOT %' \
OR QUERYDIM.QUERY LIKE 'irs %' \
OR QUERYDIM.QUERY LIKE '%tax%' \
OR QUERYDIM.QUERY LIKE '% elementary school %' \
OR QUERYDIM.QUERY LIKE '% middle school %') \
and FACTS.ANONID = " + Item + " \
GROUP BY FACTS.ANONID, STATES.STATE_ABBR \
HAVING count(*) = ( \
SELECT max(t1.conteo) \
FROM ( \
SELECT \
COUNT(*) conteo ,FACTS.ANONID usr,STATES.STATE_ABBR ste \
FROM FACTS \
JOIN QUERYDIM on QUERYDIM.ID = FACTS.QUERYID \
JOIN STATES on STATES.ID=QUERYDIM.STATE_ID \
WHERE (QUERYDIM.QUERY LIKE '%1040%' \
OR QUERYDIM.QUERY LIKE '%tax forms%' \
OR QUERYDIM.QUERY LIKE '% irs %' \
OR QUERYDIM.QUERY LIKE '% dmv %' \
OR QUERYDIM.QUERY LIKE '% DOT %' \
OR QUERYDIM.QUERY LIKE 'irs %' \
OR QUERYDIM.QUERY LIKE '%tax%' \
OR QUERYDIM.QUERY LIKE '% elementary school %' \
OR QUERYDIM.QUERY LIKE '% middle school %') \
and FACTS.ANONID = " + Item + " \
GROUP BY FACTS.ANONID, STATES.STATE_ABBR) t1);"
print(Query)
smt2 = cur.execute(Query)
final = []
for row in smt2:
final.append((row[0], row[1], row[2]))
for n in range(len(final)):
Terms = { 'cnt': final[n][0], \
'usr': final[n][1], \
'st': final[n][2] \
}
print(Terms['cnt'])
print(Terms['usr'])
print(Terms['st'])
queryIns = f"INSERT INTO FinalUser_Stg VALUES (" + str(
Terms['cnt']) + "," + str(Terms['usr']) + "," + "'" + str(
Terms['st']) + "'" + ")"
print(queryIns)
cur.execute(queryIns)
print("si llega hasta aca funciono")
def deleteRec():
con = sqlite3.connect("student.db")
cur = con.cursor('*')
cur.execute("DELETE FROM FROM student WHERE id=?", (id, ))
con.commit()
con.close()