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