def __init__(self, parent=None, textboxtext=None):
super(MainWindow, self).__init__(parent)
self.setupUi(self)
db.dbConnect()
model = QSqlTableModel()
db.readTable(model, textboxtext)
self.listView.setModel(model)
self.listView.setModelColumn(1)
self.textEdit.setText(textboxtext)
def tradeDataStore(): table_name = db_table_name db_conn = db.dbConnect(stock_db) table_create_cmd = "CREATE TABLE " + table_name + " (TIME CHARACTER(20), PRICE FLOAT, VOLUME_INC INT, TRADE_TYPE INT);" db.dbCreateTable(db_conn, table_name, table_create_cmd) tradeItemStore(stock_code, db_conn, table_name) db.dbClose(db_conn) return
def tradeInfoQuery():
db_name = stock_db
db_conn = db.dbConnect(stock_db)
cursor = db_conn.cursor()
cursor.execute("SELECT TIME, PRICE, VOLUME_INC from " + db_table_name)
results = cursor.fetchall()
db.dbClose(db_conn)
for row in results:
print "time: %s, price %f, volume %s" %(row[0], row[1], row[2])
def getServerInfo(selectedHost, variableName):
# get the specifiec server variable and return it
q='show variables'
serverVariables=db.dbConnect(selectedHost,'mysql',q,0)
serverVariable='none'
if variableName:
for thisVariable in serverVariables:
if variableName==thisVariable[0]:
serverVariable=thisVariable[1]
return serverVariable
def testUsers():
users = getTenNewestUsers()
# Not quite as dumb as it looks
times = [[]] * len(users)
totaldebug = [""] * len(users)
for trial in range(10):
for tupIndex in range(len(users)):
(uid, placementID) = users[tupIndex]
placementID += 1
if placementID >= len(raw):
# Right, but, like, are you sure?
cnx = db.dbConnect("linkgame")
cur = cnx.cursor()
cur.execute("SELECT placementID FROM tournament WHERE uid=%s", [uid])
placements_ = cur.fetchall()
cnx.close()
placements = []
for (placement,) in placements_:
placements.append(placement)
for i in range(len(raw)):
if not (i in placements):
placementID = i
users[tupIndex] = (uid, i - 1)
if placementID >= len(raw):
# We've tested all the solutons for this user
continue
if placementID == len(raw) - 1:
# Ahahahahaha have fun
(answer, debug, timeTaken) = runTest(uid, raw[placementID][:3], True)
else:
# 3 tiles * 3 chars/tile = 9 chars (~ 1s)
(answer, debug, timeTaken) = runTest(uid, raw[placementID][:9], True)
# An incorrect answer incurs a 10s penalty.
# This is included in the mean calculation
if len(answer) != 1 or answer[0] != raw[placementID]:
timeTaken += 10000
print "[debug] Answer incorrect or not given", uid, placementID, timeTaken
else:
print "[debug] Answer correct", uid, placementID, timeTaken
# I have to do it this was because Python's lists are pass-by-reference
# Seriously, both 'times[tupIndex].append(...)' and 'times[tupIndex] += [...]' fail!!
times[tupIndex] = times[tupIndex] + [timeTaken]
totaldebug[tupIndex] += debug
totaldebug += ("\n" + ("-"*20) + "\n")
ret = []
for i in range(len(users)):
if times[i] == []:
ret.append((users[i][0], users[i][1] + 1, None, "No data."))
else:
ret.append((users[i][0], users[i][1] + 1, int(sum(times[i])/len(times[i])), totaldebug[i]))
return ret;
def getTenNewestUsers():
cnx = db.dbConnect("linkgame")
cur = cnx.cursor()
cur.execute(("SELECT uid FROM tests WHERE testStatus=1;"))
dbusers = cur.fetchall()
cnx.close()
users = []
for (uid,) in dbusers:
cnx = db.dbConnect("linkgame")
cur = cnx.cursor()
cur.execute("SELECT placementID FROM tournament WHERE uid=%s", [uid])
dbmaxs = cur.fetchall()
cnx.close()
maximum = -1
for (placementID,) in dbmaxs:
if placementID > maximum:
maximum = placementID
users.append((uid, maximum))
return sorted(users, key=lambda x: x[1])[:10]
def createDatabase(self):
database = Migrations.database
conn = dbConnect().getConnection()
mycursor = conn.cursor()
## check table is exists or not
mycursor.execute("SHOW DATABASES")
dbnames = []
for x in mycursor:
dbnames.append(x[0])
###not exist table then create db
if database not in dbnames:
mycursor.execute("CREATE DATABASE " + database)
print(database + " DATABASE CREATED SUCCESSFULLY")
else:
print(database + " DATABASE ALREADY EXISTS")
conn.close()
def createTable(self):
conn = dbConnect().connectDB()
mycursor = conn.cursor()
## check table is exists or not
mycursor.execute("SHOW TABLES")
dbtables = []
for x in mycursor:
dbtables.append(x[0])
###not exist table then create db
if 'random_results' not in dbtables:
mycursor.execute(
"CREATE TABLE random_results (id INT AUTO_INCREMENT PRIMARY KEY, random_number INT(2), result_1 VARCHAR(15), result_2 VARCHAR(15))"
)
print("random_results TABLE CREATED SUCCESSFULLY")
print("Migrations Completed")
else:
print("random_results TABLE ALREADY EXISTS")
print("No Migrations")
conn.close()
else:
print "[debug] Answer correct", uid, placementID, timeTaken
# I have to do it this was because Python's lists are pass-by-reference
# Seriously, both 'times[tupIndex].append(...)' and 'times[tupIndex] += [...]' fail!!
times[tupIndex] = times[tupIndex] + [timeTaken]
totaldebug[tupIndex] += debug
totaldebug += ("\n" + ("-"*20) + "\n")
ret = []
for i in range(len(users)):
if times[i] == []:
ret.append((users[i][0], users[i][1] + 1, None, "No data."))
else:
ret.append((users[i][0], users[i][1] + 1, int(sum(times[i])/len(times[i])), totaldebug[i]))
return ret;
if __name__ == "__main__":
while True:
cnx = db.dbConnect("linkgame")
cur = cnx.cursor()
for (uid, placementID, avgtime, debug) in testUsers():
if (avgtime != None):
cur.execute("INSERT INTO tournament SET uid=%s, placementID=%s, timeTaken=%s, debug=%s;", [uid, placementID, avgtime, debug])
cnx.commit()
cnx.close()
sleep(5)
if setupDB:
if verbose:
print("\n--- Setting up database ...")
# get root password
print("\nEnter mysql password for user 'root'")
while True:
#rootPass = getpass(" ? ")
rootPass = "******"
if not rootPass:
print("You have to type something\nTry again")
else:
break
# connect to database mysql as root
con = dbConnect(dbHost, "mysql", "root", rootPass, verbose)
curs = createCursor(con, verbose)
if createDB(curs, verbose):
if createUser(curs, verbose):
# close cursor and connection to table mysql as root
closeCursor(curs, verbose)
dbDisconnect(con, verbose)
# connect to database collatz as collatz
con = dbConnect(dbHost, dbName, dbUser, dbPass, verbose)
curs = createCursor(con, verbose)
if createTables(curs, verbose):
print("\nEverything setup successfully")
closeCursor(curs, verbose)
def doSql(req,action,cols,idField,dbname,tableName,selectedHost,owner):
# this handles all insert, update, and delete sql
# So, if the user is not logged in or doesn't have permission then this will fail
# the default user (dbname) should only have select permission
# util.redirect(req,"../testValue.py/testvalue?test="+"doSql"+repr(cols))
error=""
insertID=''
config=getConfig(req,req.form['dbname'].value)
data=kooky2.myCookies(req,'get','',dbname,selectedHost)
#~ util.redirect(req,"../testValue.py/testvalue?test="+repr(data))
try:
username=data['username']
userpass=data['userpass']
except:
username=dbname
userpass=dbname
# if not username:
# username=dbname
# if not userpass:
# userpass=dbname
# util.redirect(req,"../testValue.py/testvalue?test="+repr(username)+repr(userpass)+str(dbname))
# get the column names and column types for tableName
fieldInfo=getFieldInfo3(req,selectedHost,dbname,tableName)
fieldNames=fieldInfo['fieldNames']
fieldTypes=fieldInfo['fieldTypes']
# insert a new record
if action=='insert':
# **********************************************************
# INSERT - Since newRec HAS a value then this must be an insert query
# **********************************************************
#
setCols=''
valueTags=''
setValues=[]
# For each colname in this table see
# if the form passed a value - which it
# does for all editable fields (I think).
for colname in cols:
try:
colvalue=cols[colname]
except:
colvalue="skipme"
# if it's a blob field then don't change that colvalue
if 'blob' in fieldTypes[colname.lower()]:
if colvalue:
pass
# if config['convert2']:
# conversion=convertImg(config,colvalue,cols[config['invisible']])
# colvalue=conversion[0]
# error=conversion[1]
else:
colvalue="skipme"
# if it's a set field then change that colvalue
elif 'set(' in fieldTypes[colname.lower()]:
#~ error='set'
if type(colvalue)==type([1]):
colvalue=string.join(colvalue,',')
else:
# Not a blob, so it's not binary.
if colvalue=='None':
colvalue='NULL'
else:
colvalue=quoteHandler(colvalue)
colvalue=colvalue.strip()
# Checking to see if there is a colvalue
# **************************************
# Those values set to 'skipme' will remain what they previousely were.
# Empty values will be skipped because if number fields are empty strings, the query will crash.
# mysql will insert the defined default value for the skipped fields.
if colvalue:
pass
else:
colvalue="skipme"
if colvalue=='skipme':
pass
else:
setCols=setCols+",`"+colname+"`"
valueTags=valueTags+","+"%s"
setValues.append(colvalue)
# if there is an owner field defined insert the username
if owner in fieldNames:
setCols=setCols+",`"+owner+"`"
valueTags=valueTags+","+"%s"
setValues.append(username)
setCols="("+setCols[1:]+")"
valueTags="("+valueTags[1:]+")" #tuple(valueTags)
setValues=tuple(setValues)
########### this works
q='insert into `'+tableName+'` '+ setCols+" "+ 'values '+valueTags
# util.redirect(req,"../testValue.py/testvalue?test="+str(q)+repr(setValues))
try:
dbconnection = MySQLdb.connect(host=selectedHost,user=username,passwd=userpass,db=dbname)
xcursor = dbconnection.cursor()
xcursor.execute(q,(setValues))
xcursor.execute("SELECT LAST_INSERT_ID()")
insertID=xcursor.fetchone()[0]
except:
# qstr=q.replace('%s','value')
error='An INSERT query had failed for '+tableName.upper() +" table and user "+username.upper()+". "
# error=str(q)+" "+str(setValues)
# error=str(q)
try:
xcursor.close()
except:
pass
try:
# for transactional tables like innodb
dbconnection.commit()
except:
pass
try:
dbconnection.close()
except:
pass
########################
elif action=='update':
# *******************************************************************
# UPDATE - Since newRec DOESN'T have a value then this must be an update query
# *******************************************************************
#
# Because a field may need to be updated to NULL, NULL is used instead
# of skipme in cases where that might apply - strickly an update concern.
setCols=''
idValue=''
setValues=[]
# For each colname in this table see
# if the form passed a value - which it
# does for all editable fields (I think).
for colname in fieldNames:
try:
if colname==idField:
colvalue='skipme'
idValue=cols[colname]
else:
colvalue=cols[colname]
except:
colvalue="skipme"
# if it's a blob field then don't change that colvalue
if 'blob' in fieldTypes[colname.lower()]:
if colvalue:
pass
# if config['convert2']:
# conversion=convertImg(config,colvalue,cols[config['invisible']])
# colvalue=conversion[0]
# error=conversion[1]
else:
colvalue="skipme"
# if it's a set field then change that colvalue
elif 'set(' in fieldTypes[colname.lower()]:
if type(colvalue)==type([1]):
colvalue=string.join(colvalue,',')
else:
# Not a blob, so it's not binary.
if colvalue==None:
colvalue=''
else:
colvalue=quoteHandler(colvalue)
colvalue=colvalue.strip()
# Checking to see if there is a colvalue
# **************************************
# Those values set to 'skipme' will remain what they previousely were.
if colvalue=='skipme':
pass
else:
setCols=setCols+",`"+colname+'`=%s'
setValues.append(colvalue)
setCols=setCols[1:]
setValues=tuple(setValues)
# if there is an owner field defined, get the owner and check it against current username
if owner in fieldNames:
q='select `'+owner+'` from `'+tableName+'` where `'+idField+"`='"+str(idValue)+"'"
result=db.dbConnect(selectedHost,dbname,q,1)
#~ util.redirect(req,"../testValue.py/testvalue?test="+repr(result))
try:
if result[0]:
ownerName=result[0]
else:
ownerName=''
except:
ownerName=''
if ownerName.strip()==username.strip():
pass
else:
username=dbname
userpass=dbname
########### this works
q="update `"+tableName+"` set "+str(setCols)+" where `"+idField+"`='"+str(idValue)+"'"
# util.redirect(req,"../testValue.py/testvalue?test="+str(q)+str(setValues))
try:
dbconnection = MySQLdb.connect(host=selectedHost,user=username,passwd=userpass,db=dbname)
cursor = dbconnection.cursor()
cursor.execute(q,setValues)
# util.redirect(req,"../testValue.py/testvalue?test="+repr(q)+str(username+" "+userpass+" "+dbname))
except:
error="An UPDATE query has failed for "+tableName.upper() +" table and user "+username.upper()+". "
# error=str(q)+" "+str(setValues)
try:
xcursor.close()
except:
pass
try:
# for transactional tables like innodb
dbconnection.commit()
except:
pass
try:
dbconnection.close()
except:
pass
########################
elif action=="DELETE":
# *******************************************************************
# DELETE - The current record
# *******************************************************************
#
error=""
q="delete from `"+tableName+"` where `"+tableName+"`.`"+idField+"`="+"'"+str(cols)+"'"
error=""
# util.redirect(req,"../testValue.py/testvalue?test="+repr(q))
try:
dbconnection = MySQLdb.connect(host=selectedHost,user=username,passwd=userpass,db=dbname)
cursor = dbconnection.cursor()
cursor.execute(q)
except:
error="A DELETE query had faile for "+tableName.upper() +" table and user "+username.upper()+". "
try:
xcursor.close()
except:
pass
try:
# for transactional tables like innodb
dbconnection.commit()
except:
pass
try:
dbconnection.close()
except:
pass
########################
if error:
error=error+"<BR>-----<BR>"+\
"It could be that the logged-in user doesn't have UPDATE privileges for this record."+\
"<BR>-----<BR>"+\
"It could be that the data you are submitting is not valid IAW the database design (ie date format was not yyyy-mm-dd)."+\
"<BR>-----<BR>"+\
"It could be that you are trying to save an image that is too big to save, try saving without an image first."+\
"<BR>-----<BR>"
return (error,insertID)
def support(req):
# util.redirect(req,"../testValue.py/testvalue?test="+repr(req.form.list))
try:
dbname=req.form['dbname'].value
except:
dbname=req.form['dbname']
config=getConfig(req,dbname)
supportTableName=req.form['supportTableName']
config['supportBlob']='image'
fieldInfo=getFieldInfo3(req,config['selectedHost'],config['dbname'],supportTableName)
fieldNames=fieldInfo['fieldNames']
# get the column names and values from the form submission
# I've changed the method here (only), I parse the form data without regard to the table fieldnames
# in the other functions (item, cat, and media) I parse using the fieldnames as dic keys
# I had to do it this way because I couldn't get html multiple select values the other way
# I'm storing these multi values as a space seperated string, meaning in this case that column names can't have spaces in them.
# This is all needed just for the _config table which uses multiple select html for column names
# It could work better for all functions however
cols={}
formKeys=req.form.keys()
for thisKey in formKeys:
if thisKey in fieldNames:
try:
cols[thisKey]=req.form[thisKey].value
except:
formString=''
for thisValue in req.form[thisKey]:
formString=formString+" "+(str(thisValue))
cols[thisKey]=formString
# util.redirect(req,"../testValue.py/testvalue?test="+repr(cols)+"---")
if 'filename' in fieldNames:
try:
filename=req.form[config['supportBlob']].filename
except:
pass
# if no filename was passed use the old filename (if there is one) because the file hasn't changed
if filename=="":
try:
q="select `"+config['invisible']+"` from `"+supportTableName+\
'` where `'+supportTableName+'`.`_id`'+'="'+req.form['supportID'].value+'"'
qresult=db.dbConnect(config['selectedHost'],config['dbname'],q,1)
cols[config['invisible']]=qresult[0]
except:
cols[config['invisible']]=""
else:
cols[config['invisible']]=filename
try: # buttons pass xy locations, just test for one
action=req.form['savebutton.x']
action='insert'
except:
try:
action=req.form['updatebutton.x']
action='update'
cols[fieldInfo['idField']]=req.form['supportID'].value
except:
try:
formValues=req.form['newConfig']
if 'newConfig' in cols:
del cols['newConfig']
if 'supportTableName' in cols:
del cols['supportTableName']
action='insert'
except:
action='cancel'
# util.redirect(req,"../testValue.py/testvalue?test="+repr(action)+"---"+str(cols)+'___'+repr(req.form.list))
if action !='cancel':
what=doSql(req,action,cols,fieldInfo['idField'],dbname,supportTableName,config['selectedHost'],"")
# util.redirect(req,"../testValue.py/testvalue?test="+repr(what)+" "+str(config['dbname']))
if what[0]:
parameter="?popup=93&errorMessage="+what[0]
else:
if supportTableName=='_config':
parameter="?action=20"
elif supportTableName=='_category':
parameter="?action=23&supportTableName="+supportTableName
else:
parameter="?action=23&supportTableName="+supportTableName
else:
if supportTableName=='_config':
parameter="?action=20"
else:
parameter="?action=23&supportTableName="+supportTableName
util.redirect(req,"../index.py"+parameter)
def Imedia(req):
config=getConfig(req,req.form['dbname'].value)
# util.redirect(req,"../testValue.py/testvalue?test="+repr(req.form.list))
# get the column names and column types for tableName
cols={}
filename=''
q=''
fieldInfo=getFieldInfo3(req,config['selectedHost'],config['dbname'],config['mediaTable'])
fieldNames=fieldInfo['fieldNames']
for col in fieldNames:
if col != fieldInfo['idField']:
try:
cols[col]=req.form[col].value
except:
try:
cols[col]=req.form[col]
except:
pass
if 'filename' in fieldNames:
try:
filename=req.form[config['mediaBlob']].filename
except:
pass
# if no filename was passed use the old filename (if there is one) because the file hasn't changed
if filename=="":
try:
q="select "+config['invisible']+" from `"+config['mediaTable']+\
'` where `'+config['mediaTable']+'`.`'+config["mediaIDfield"]+'`'+'="'+req.form['mediaID'][1:]+'"'
qresult=db.dbConnect(config['selectedHost'],config['dbname'],q,1)
cols[config['invisible']]=qresult[0]
except:
cols[config['invisible']]=""
else:
cols[config['invisible']]=filename
# util.redirect(req,"../testValue.py/testvalue?test="+repr(cols)+repr(filename)+str(q))
try: # SAVE clicked
action=req.form['savebutton.x']
action='insert'
# what table and record to relate to
cols[config['itemIDfield']]=req.form['itemID'].value
except:
try: # UPDATE clicked
action=req.form['updatebutton.x']
action='update'
# filter out the item tag
if req.form['mediaID'][0]=='I':
cols[config['mediaIDfield']]=req.form['mediaID'][1:]
else:
cols[config['mediaIDfield']]=req.form['mediaID'].value
except:
action='cancel'
tableID="I"+req.form['itemID']
if action!='cancel':
# util.redirect(req,"../testValue.py/testvalue?test="+repr(cols))##+repr(cols[config['mediaIDfield']]))
what=doSql(req,action,cols,config['mediaIDfield'],config['dbname'],config['mediaTable'],config['selectedHost'],config['owner'])
cleanTmp(config)
# util.redirect(req,"../testValue.py/testvalue?test="+repr(what))
if what[0]:
parameter="?popup=93&errorMessage="+what[0]
else:
# insertID=what[1]
parameter="?media="+str(tableID)
else:
parameter="?action=3"
util.redirect(req,"../index.py"+parameter)
def media(req):
config=getConfig(req,req.form['dbname'].value)
# get the column names and column types for tableName
cols={}
filename=''
q=''
fieldInfo=getFieldInfo3(req,config['selectedHost'],config['dbname'],config['mediaTable'])
fieldNames=fieldInfo['fieldNames']
test=[]
for col in fieldNames:
try:
cols[col]=req.form[col].value
except:
try:
cols[col]=req.form[col]
except:
pass
if 'filename' in fieldNames:
try:
filename=req.form[config['mediaBlob']].filename
except:
pass
# if no filename was passed use the old filename (if there is one) because the file hasn't changed
if filename=="":
try:
q="select `"+config['invisible']+"` from `"+config['mediaTable']+\
'` where `'+config['mediaTable']+'`.`'+config["mediaIDfield"]+'`'+'="'+req.form['mediaID'].value+'"'
qresult=db.dbConnect(config['selectedHost'],config['dbname'],q,1)
cols[config['invisible']]=qresult[0]
except:
cols[config['invisible']]=""
else:
cols[config['invisible']]=filename
try: # SAVE clicked
action=req.form['savebutton.x']
action='insert'
# what table and record to relate to
cols[config['catIDfield']]=req.form['catID'].value
except:
try: # UPDATE clicked
action=req.form['updatebutton.x']
action='update'
cols[config['mediaIDfield']]=req.form['mediaID'].value
except:
action='cancel'
# set the realted table, so this returns to the correct data
tableID=req.form['catID']
# util.redirect(req,"../testValue.py/testvalue?test="+repr(cols)+repr(filename))
if action!='cancel':
what=doSql(req,action,cols,config['mediaIDfield'],config['dbname'],config['mediaTable'],config['selectedHost'],config['owner'])
cleanTmp(config)
# util.redirect(req,"../testValue.py/testvalue?test="+repr(what))
if what[0]:
parameter="?popup=93&errorMessage="+what[0]
else:
# insertID=what[1]
parameter="?media="+str(tableID)
else:
# try:
# cancelAction=req.form['cancelAction']
# except:
# cancelAction="7"
# parameter="?action="+cancelAction
parameter="?media="+str(tableID)
util.redirect(req,"../index.py"+parameter)
def getFieldInfo3(req,selectedHost,dbname,tableName):
# get the field names, field types for this table
# preload some variables
fieldInfo={}
fieldTypes={}
fieldDefaults={}
fieldNames=[]
fieldInfo['idField']=''
fieldInfo['idLoc']=0
# this function will only work if the table structure
# used by mysql for the column definition
# is exactly this (from version 4.0.17):
# Field - Type - Null - Key - Default - Extra
# I don't know of a way to verify this or even
# obtain this information, so I assume it.
q="show columns from `"+str(tableName)+"`"
qresult=db.dbConnect(selectedHost,dbname,q,0)
if qresult>0:
for thisFieldDefinition in qresult:
# dictionary of fieldname and fieldTypes
fieldName=thisFieldDefinition[0]
fieldNames.append(fieldName)
fType=thisFieldDefinition[1]
fDefault=thisFieldDefinition[4]
# this only recognizes ONE PRI ID field,
# the last one encountered?
#
if 'PRI' in thisFieldDefinition:
#~ if fieldName[0]=='_':
fieldInfo['idField']=fieldName
# why do I lower the field name?
# for matching purposes
fieldTypes[fieldName.lower()]=fType
fieldDefaults[fieldName.lower()]=fDefault
fieldInfo['fieldNames']=fieldNames
# else:
# msg.append('Query Failed')
# msg.append(repr(q))
# msg.append('ID=2 for dbname='+str(dbname))
# errorHandler(req,msg,1)
if fieldInfo['idField']:
try:
idLoc=fieldInfo['fieldNames'].index(fieldInfo['idField'])
fieldInfo['idLoc']=idLoc
except:
pass
fieldInfo['idField']=fieldInfo['idField']
fieldInfo['fieldTypes']=fieldTypes
fieldInfo['fieldDefaults']=fieldDefaults
# return:
# fieldInfo['fieldNames'] - a list of the field names for tableName
# fieldInfo['idField'] - the PRI id field in tableName
# fieldInfo['fieldTypes'] - dictionary, fieldTypes keyed by field names.
# fieldInfo['fieldDefaults'] - dictionary, fieldDefaults keyed by field names.
# fieldInfo['idLoc'] - the list index of the pri field?
return fieldInfo
def getConfig(req,dbname):
config={}
config['configError']=''
config['dbname']=dbname
config['selectedHost']='localhost'
config['configTable']='_config'
config['docTable']='_doc'
config['categoryTable']='_category'
config['login']=''
config['theme']=''
config['displayname']=''
config['displaynamelocation']=''
config['displaylogo']=''
config['popupbackground']=''
config['itemListColumns']=[]
config['catSortColumn']=''
config['catSearchColumns']=[]
config['catInfoColumn']=''
config['itemColumns']=[]
config['emailcontact']=''
config['lastupdate']=''
config['owner']=''
primaries=[]
listCols=[]
tableCols=[]
searchCols=[]
apacheConfig=req.get_config()
rootPath=apacheConfig['PythonPath'][11:-2]
# util.redirect(req,"testValue.py/testvalue?test="+repr(config))
try:
try:
# get the stored config values from the _config table
q="select * from `"+config['dbname']+"`.`"+config['configTable']+"`"
configValues=db.dbConnect(config['selectedHost'],dbname,q,1)
if configValues==None:
# no record present, this will require a user/pass that has insert privilegesd
# the standard user/pass would be dbname/dbname and by default would only have select privileges.
# so, this is a bit of a problem requiring the config table to be initialized with one record
# or be able to login before having a config. Consequently, this will fail as will any attempt
# to edit the _config table or any other table util a valid login is accomplished.
insertq="insert into `"+config['configTable']+"` (`"+dbname+"`) values ('"+dbname+"')"
recordInsert=db.dbConnect(config['selectedHost'],dbname,insertq,0)
# util.redirect(req,"testValue.py/testvalue?test="+repr(insertq))
q="show columns from `"+dbname+"`.`_config`"
configCols=db.dbConnect(config['selectedHost'],dbname,q,0)
for col in range(1,len(configCols)):
if configValues[col]==None:
value=""
else:
value=configValues[col]
config[configCols[col][0].strip()]=value.strip()
except:
config['configError']='Query failed: '+q
# util.redirect(req,"testValue.py/testvalue?test="+repr(q))
# set the table names, item table is same as dbname, cat table will have 2 id columns, media table will have 3 id cols
try:
config['itemTable']=config['dbname']
q="show tables from `"+config['dbname']+"`"
showTables=db.dbConnect(config['selectedHost'],config['dbname'],q,0)
for thisTable in showTables:
q1="show columns from `"+thisTable[0]+"`"
qresult=db.dbConnect(config['selectedHost'],config['dbname'],q1,0)
idColCount=0
for thisCol in qresult:
if thisCol[0][0]=="_": # and thisCol[0]!=config['categoryTable']:
idColCount=idColCount+1
if idColCount==3:
config['mediaTable']=thisTable[0]
elif idColCount==2:
config['catTable']=thisTable[0]
except:
config['configError']='Query failed: '+q+ 'Query failed: '+q1
# util.redirect(req,"testValue.py/testvalue?test="+repr(q1))
# query mysql for it's values for the itemTable
try:
itemCols=[]
charCols=[]
q="show columns from `"+config['itemTable']+"`"
qresult=db.dbConnect(config['selectedHost'],config['dbname'],q,0)
for thisCol in qresult:
if 'PRI' in thisCol[3]:
config['itemIDfield']=thisCol[0]
primaries.append(thisCol[0])
elif 'blob' in thisCol[1]:
config['itemIMGfield']=thisCol[0]
elif thisCol[0][0]!='_':
itemCols.append(thisCol[0])
if 'char' in thisCol[1]:
charCols.append(thisCol[0])
config['itemShowColumns']=itemCols
# if not specified use charCols for default itemlist
if len(charCols)>=3:
listCols=charCols[:2]
tableCols=charCols[:4]
else:
listCols=charCols
tableCols=charCols
except:
config['configError']='Item Query failed: '+q
# util.redirect(req,"testValue.py/testvalue?test="+repr(charCols)+'___'+repr(itemCols))
# query mysql for it's values for the catTable
try:
catCols=[]
charCols=[]
q="show columns from `"+config['catTable']+"`"
qresult=db.dbConnect(config['selectedHost'],config['dbname'],q,0)
for thisCol in qresult:
if 'PRI' in thisCol[3]:
config['catIDfield']=thisCol[0]
primaries.append(thisCol[0])
elif 'text' in thisCol[1]:
config['catNoteField']=thisCol[0]
elif thisCol[0][-1]=="_":
config['catInfoColumn']=thisCol[0]
catCols.append(thisCol[0])
elif thisCol[0][0]!='_':
# or thisCol[0]==config['categoryTable']:
catCols.append(thisCol[0])
if 'char' in thisCol[1]:
charCols.append(thisCol[0])
config['catShowColumns']=catCols
# if not specified use charCols for default list
if len(charCols)>=3:
searchCols=charCols[:3]
else:
searchCols=charCols
except:
config['configError']='Cat Query failed: '+q
# util.redirect(req,"testValue.py/testvalue?test="+repr(charCols)+repr(catCols))
# query mysql for it's values for the mediaTable
try:
mediaCols=[]
q="show columns from `"+config['mediaTable']+"`"
qresult=db.dbConnect(config['selectedHost'],config['dbname'],q,0)
for thisCol in qresult:
if 'PRI' in thisCol[3]:
config['mediaIDfield']=thisCol[0]
primaries.append(thisCol[0])
elif "blob" in thisCol[1]:
config['mediaBlob']=thisCol[0]
elif thisCol[0][0]!='_':
mediaCols.append(thisCol[0])
config['mediaShowColumns']=mediaCols
except:
config['configError']='Media Query failed: '+q
# util.redirect(req,"testValue.py/testvalue?test="+repr(config)+"before")
# populate the supportTables and tableNames config variables
try:
config['supportTables']=[]
config['tableNames']=[]
for thisTable in showTables:
config['supportTables'].append(thisTable[0])
config['tableNames'].append(thisTable[0])
config['supportTables'].remove(config['itemTable'])
config['supportTables'].remove(config['catTable'])
config['supportTables'].remove(config['mediaTable'])
config['supportTables'].remove('_kooky')
config['supportTables'].remove('_config')
config['supportTables'].remove('_category')
config['supportTables'].remove('_doc')
config['tableNames'].remove('_kooky')
config['tableNames'].remove('_config')
config['tableNames'].remove('_category')
config['tableNames'].remove('_doc')
except:
config['configError']='supportTable list failed: '
# util.redirect(req,"testValue.py/testvalue?test="+repr(config)+"before")
try:
config['invisible']='filename'
config['rootPath']=rootPath
config['primaries']=primaries
config['dbImagePath']=rootPath+"/images/"
config['defaultImagePath']=rootPath+"/images/defaults"
config['catImagePath']=rootPath+"/catimages/"
config['itemImagePath']=rootPath+"/itemimages/"
config['mediaPath']=rootPath+"/tmp/"
config['itemListColumns']=config['itemListColumns'].split()
config['itemColumns']=config['itemColumns'].split(" ")
config['catSearchColumns']=config['catSearchColumns'].split()
config['owner']='owner'
except:
config['configError']='Assignments failed'
# util.redirect(req,"testValue.py/testvalue?test="+repr(config)+"after")
# config defaults
if not config['displayname']:
config['displayname']=config['dbname']
if not config['displaynamelocation']:
config['displaynamelocation']='MIDDLE'
if not config['displaylogo']:
config['displaylogo']='defaultlogo.png'
if not config['popupbackground']:
config['popupbackground']=config['displaylogo']
if not config['selectedHost']:
config['selectedHost']='localhost'
if not config['catSortColumn']:
config['catSortColumn']=config['catIDfield']
if not config['catInfoColumn']:
config['catInfoColumn']=charCols[0]
if not config['itemListColumns']:
config['itemListColumns']=listCols
if not config['itemColumns']:
config['itemColumns']=tableCols
if not config['catSearchColumns']:
config['catSearchColumns']=searchCols
if not config['emailcontact']:
config['emailcontact']='root@localhost'
if not config['lastupdate']:
config['lastupdate']='NO'
if not config['theme']:
config['theme']='default'
# util.redirect(req,"testValue.py/testvalue?test="+repr(config)+"after2")
except:
config['configError']="configError"
return config
import datetime
import db
import parsec
dbc = db.dbConnect('localhost', 'root', 'atlas', 'parsec')
cursor = db.getCursor(dbc)
db.createTable(cursor, 'reports')
db.createTable(cursor, 'companies')
print ''
print ''
print '********** PARSEC **********'
print '****************************'
print ''
valid = 0
total = 0
info = {}
info['start'] = datetime.datetime.now()
completed = db.countReports(dbc, cursor)
print str(completed['count']) + ' Existing Reports'
print ''
for year in range(2016, 1994, -1):
for qtr in range(4, 0, -1):
index = parsec.getIndex(year, qtr)
for report in index:
except getopt.GetoptError as e:
onError(1, str(e))
# if no options passed, then exit
#if len(sys.argv) == 1: # no options passed
# onError(2, 2)
# interpret options and arguments
for option, argument in myopts:
if option in ('-v', '--verbose'): # verbose output
verbose = True
elif option in ('-h', '--help'): # display help text
usage(0)
# connect to database
con = dbConnect(dbHost, dbName, dbUser, dbPass, verbose)
curs = createCursor(con, verbose)
# find lowest number not used
number = findLowestNumber(curs, verbose)
while True:
# find lowest number not used
number = findLowestNumber(curs, verbose)
if number > 10000:
break
highestNumber, steps, numberList = collatz(curs, number, verbose)
writeToDb(number, highestNumber, steps, "C", verbose)
def myCookies(req,action,data,dbname,selectedHost):
# here I manage the browser cookies as well as the mysql kooky table
# the browser stores an id and a dbanme
# the kooky table has records storing lots of data identified by the cookieID
# this enables each host to have their own information and thus multiple hosts may use the same installation
# ONLY ONE DB CAN BE ACCESSED PER BROWSER/HOST OR KOOKY DATA WILL BE MIXED UP
#
# the username and password for accessing the kookyDB will be the dbname
# a user by that name with that password must have insert,update privileges to the kooky db
# util.redirect(req,"testValue.py/testvalue?test=action:"+str(action)+" dbname:"+str(dbname))
#~ action="save"
cookieID=''
kookyDB=''
cookieData={}
qupdate="no"
kookyTable='_kooky'
qinsert="no"
# assign a name for the cookie
# the name of the cookie is the
# name of the web dir + '_id'
apacheConfig=req.get_config()
rootPath=apacheConfig['PythonPath'][11:-2]
cookieName=rootPath.split("/")[-1]+'_id'
remoteHost=req.get_remote_host()
# get the browser cookie
getCookie=Cookie.get_cookies(req)
try:
# get the current kooky values
cookieID,kookyDB=getCookie[cookieName].value.split()
# if I passed a dbname update the data only
if dbname:
newCookie = Cookie.Cookie(cookieName, cookieID+' '+dbname)
newCookie.expires = time.time() + 31449600 # extend expires by one year
Cookie.add_cookie(req, newCookie)
kookyDB=dbname
# if no name passed then just get the stored data
else:
cookieData['kookyID']=cookieID
cookieData['kookyDB']=kookyDB
# util.redirect(req,"testValue.py/testvalue?test="+repr(kookyDB))
except:
# no cookie found so create one
for i in time.localtime()[:6]:
if len(str(i))==1:
digit='0'+str(i)
else:
digit=str(i)
cookieID=cookieID+digit
newCookie = Cookie.Cookie(cookieName, cookieID+' '+dbname)
newCookie.expires = time.time() + 31449600 # one year
Cookie.add_cookie(req, newCookie)
# util.redirect(req,"testValue.py/testvalue?test=kookyDB:"+str(kookyDB)+" dbname:"+str(dbname))
# above is all about browser cookies
#*********************************************
#*********************************************
# below is all about the 3t kooky table
if action=='':
# just used for initial startup
# just return the kookyID
kookyData=cookieID
elif action=='db':
# just return the dbname for getting the config
kookyData=kookyDB #kookyData['kookyDB']
#
#*********************************************
#*********************************************
#
# Insert or Update kooky data
elif action=='save':
# see if a kooky is already stored
#
q='select `_kooky`.`kookyData` from `'+str(dbname)+"`.`_kooky`" \
' where `_kooky`.`_kookyID`="'+str(cookieID)+'"'
try:
kookyData=db.dbConnect(selectedHost,kookyDB,q,1)
except:
kookyData=''
# d=data['results']
# d3=d[0]
# util.redirect(req,"testValue.py/testvalue?test="+repr((d3))+'---'+str(kookyData))
##+'___'+str(kookyData))
# pickle the kooky data
#~ pData=cPickle.dumps(data)
pData=pickle.dumps(data)
# Here I have to remove all the " characters
# in the qtext variable or the query that
# saves qtext wont work. I put them back in
# when I retrieve qtext for use.
pData=string.replace(pData,'"','*****')
pData=repr(pData)
pData=pData[1:-1]
# Update the kooky
if kookyData:
# when a prev kooky is found update it
q='update '+str(kookyTable)+' set \
kookyData="%s",remoteHost="%s" where _kookyID=%s'%(pData,remoteHost,cookieID)
#~ util.redirect(req,"testValue.py/testvalue?test="+"kooky"+repr(q))
qupdate=db.dbConnect(selectedHost,kookyDB,q,-1)
# Insert new kooky
else:
q='insert into `'+str(dbname)+'`.`_kooky` \
(_kookyID,kookyData,remoteHost) values (%s,"%s","%s")'%(cookieID,pData,remoteHost)
#~ util.redirect(req,"testValue.py/testvalue?test="+"kooky"+repr(kookyDB))
qinsert=db.dbConnect(selectedHost,dbname,q,-1)
# util.redirect(req,"testValue.py/testvalue?test="+"kooky2"+repr(qinsert))
#
#
#*********************************************
#*********************************************
#
# Get the kooky data from the mysql db
elif action=='get':
# see if a kooky is already stored
#
q='select kookyData from '+str(kookyTable)+\
' where _kookyID="'+cookieID+'"' ##%s'%(kookyID)
#~ util.redirect(req,"testValue.py/testvalue?test="+repr(q))
kookyData=db.dbConnect(selectedHost,kookyDB,q,1)
#~ util.redirect(req,"testValue.py/testvalue?test="+repr(kookyData)+'---'+q)
if kookyData:
kookyData=kookyData[0]
# I had to remove all " characters in the qtext
# variable before I could save it to the db.
# So, here I put them back where they belong.
kookyData=string.replace(kookyData,'*****','"')
#~ kookyData=cPickle.loads(kookyData)
kookyData=pickle.loads(kookyData)
kooky=kookyData
else:
kooky=0
#
#
#*********************************************
#*********************************************
#
return kookyData
