def get_binning_casequery(tabName, varName, lBinEdges, hBinEdges): try: firstBin = str(lBinEdges[0]) case_query = "CASE \n WHEN (" + varName + " < " + firstBin + ") THEN (" + firstBin + "-0.001)\n" ##ADD VALUES TO TAKE CARE OF UNDERFLOW-OVERFLOW... may by call them lowest-0.01 and highest+0.01 for lBin, hBin in zip(lBinEdges, hBinEdges): phrase = ( "WHEN (" + varName + " >= " + str(lBin) + " AND " + varName + " < " + str(hBin) + ") THEN " + str(lBin) + " \n " ) case_query = case_query + phrase case_query += ( "WHEN (" + varName + " > " + str(hBinEdges[len(hBinEdges) - 1]) + ") THEN " + str(hBinEdges[len(hBinEdges) - 1]) + "\n" ) case_query += "END " + varName + "\n" return case_query except Exception as err: print_error(err, "pydatatable.get_binning_casequery")
def refine_date(element): try: myDate = element.split('-') myMnth = '{:02d}'.format(int(month_enum(myDate[1]))) myDay = '{:02d}'.format(int(myDate[0])) myYear = myDate[2] newDate = '-'.join([myYear,myMnth,myDay]) return newDate except Exception as err: print_error(err,'pydan_test1.refine_date')
def get_binning_casequery(tabName,varName,lBinEdges,hBinEdges): try: firstBin = str(lBinEdges[0]) case_query = "CASE \n WHEN ("+varName+' < '+firstBin+') THEN ('+firstBin+'-0.001)\n' ##ADD VALUES TO TAKE CARE OF UNDERFLOW-OVERFLOW... may by call them lowest-0.01 and highest+0.01 for lBin,hBin in zip(lBinEdges,hBinEdges): phrase = 'WHEN ('+varName+' >= '+str(lBin)+' AND '+varName+' < '+str(hBin)+') THEN '+ str(lBin)+' \n ' case_query = case_query+phrase case_query += 'WHEN ('+varName+' > '+str(hBinEdges[len(hBinEdges)-1])+') THEN '+str(hBinEdges[len(hBinEdges)-1])+'\n' case_query += 'END '+varName+'\n' return case_query except Exception as err: print_error(err, 'pydataview.get_binning_casequery')
def get_binning_query(tabName,lowBinDict,hiBinDict,conditions=['1=1'],orderby=[],limit=-1,offset=-1): try: qryVars = [] varNames = lowBinDict.keys() groupby = lowBinDict.keys() for varName in varNames: lBins = lowBinDict[varName] hBins = hiBinDict[varName] qryVars.append(get_binning_casequery(tabName, varName, lBins, hBins)) query = select_data_qry(qryVars,[tabName], conditions = conditions, limit= limit, offset = offset) ##varNames.append('COUNT(*)') ##query = select_data_qry(varNames, ['('+query+')'], conditions = conditions,groupby=groupby) ##print query return query except Exception as err: print_error(err,'pysql.get_binning_query')
def create_table_qry(tabName, varDict={}, uniqueIdFlag=False,uIdName='PYDAN_ROW_NUM'): try: query = ' CREATE TABLE '+tabName+''' ( \n''' if(uniqueIdFlag == True): query = query + uIdName+' VARCHAR,\n ' varterms = [] #for var in varNames: # if var not in varTypes: varTypes[var]='VARCHAR' # if (len(varTypes)==0): # for var in varNames: varTypes[var]='VARCHAR' for varname in varDict: vartype = varDict[varname] varterms.append(' '.join([varname,vartype])) query = query+ ',\n '.join(varterms) query = query+'\n )' return query except Exception as err: print_error(err, 'pysql.create_table_qry')
def create_table_qry(tabName, varDict={}, uniqueIdFlag=False, uIdName="PYDAN_ROW_NUM"): try: query = " CREATE TABLE " + tabName + """ ( \n""" if uniqueIdFlag == True: query = query + uIdName + " VARCHAR,\n " varterms = [] # for var in varNames: # if var not in varTypes: varTypes[var]='VARCHAR' # if (len(varTypes)==0): # for var in varNames: varTypes[var]='VARCHAR' print(varDict) for varname in varDict: vartype = varDict[varname] varterms.append(" ".join([varname, vartype])) query = query + ",\n ".join(varterms) query = query + "\n )" return query except Exception as err: print_error(err, "pysql.create_table_qry")
def to_database(self,tabName,database,varNames=[],varTypes=None,ur=False): '''writes the csv file to a table. ---returns a pydset object associated with table ''' try: pConn = sqlite3.connect(database) pConn.execute('drop table if exists '+tabName) pConn.commit() if len(varNames)==0: varNames = self.pHeader if varTypes == None: varTypes = {} for var in varNames: #print var if var not in varTypes: varTypes[var]='VARCHAR' else: varTypes[var]=varTypes[var].strip() query = create_table_qry(tabName,varDict=varTypes ,uniqueIdFlag=False) manyFlagTemporary = True #print query pConn.execute(query) pConn.commit() line = self.pFile.readline().strip().split(self.pSeparator); uId = 1 manyLines = [] counter = 0; q = ['?']*len(varNames) qr = '('+','.join(q)+')' query = 'insert into '+tabName+'('+','.join(varNames)+') values '+qr ####I AM UGLY CLEAN ME UP... A LOT. ##print varNames pConn.execute('PRAGMA synchronous=OFF'); while True: if not line: break; if (len(line)==1 and line[0]==''): line = self.pFile.readline() continue else: # select,line = self. #print line # if (select): #print line line = [l.replace("\xa0", " ") for l in line] manyLines.append(tuple(line)) if (counter%1)==0: if counter == 0: pass else: ##print ("Stored :"+str(counter)) pConn.cursor().executemany(query,manyLines) manyLines =[] #if(select): counter = counter+1 uId = uId+1 #: This one srews up more than help row = self.pFile.readline().strip() #row = clean_string(row,replaceHyphen=False) ##Put everything as ascii string. #row=row.encode('utf-8').decode('utf-8','ignore').encode("utf-8") ##row = row.encode('ascii','ignore') #row = row.replace('"','') line = row.split(self.pSeparator) ##print line ##line = self.pFile.readline().split(',') pConn.executemany(query,manyLines) ##Move the cursor back to top of the csv file. pConn.commit() pConn.commit() self.pFile.seek(0,0) if (self.pFirstLineIsHeader == True): ##Move the file cursor to second line. self.pFile.readline() ##just throw it away. pConn.execute('PRAGMA synchronous=NORMAL'); pConn.commit() pConn.close() ####This one does not make sense. p = pydset(database,tabName,srcType= ' TABLE ') return p except Exception as err: print_error(err,"pycsv_reader.to_database")
def drop_table_qry(tabName, tabType=' VIEW '): try: return 'DROP '+tabType+' IF EXISTS '+tabName except Exception as err: print_error(err, 'drop_table_qry')
def drop_table_qry(tabName, tabType=" TABLE "): try: return "DROP " + tabType + " IF EXISTS " + tabName except Exception as err: print_error(err, "drop_table_qry")