コード例 #1
0
ファイル: pysql.py プロジェクト: AnilSingh79/pydan
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")
コード例 #2
0
ファイル: pydan_test1.py プロジェクト: avdheshmrt/pydan-1
 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')
コード例 #3
0
ファイル: pysql.py プロジェクト: avdheshmrt/pydan-1
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')  
コード例 #4
0
ファイル: pysql.py プロジェクト: avdheshmrt/pydan-1
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')        
コード例 #5
0
ファイル: pysql.py プロジェクト: avdheshmrt/pydan-1
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')        
コード例 #6
0
ファイル: pysql.py プロジェクト: AnilSingh79/pydan
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")
コード例 #7
0
ファイル: pycsv.py プロジェクト: AnilSingh79/pydan
    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")
コード例 #8
0
ファイル: pysql.py プロジェクト: avdheshmrt/pydan-1
def drop_table_qry(tabName, tabType=' VIEW '):
    try:
        return 'DROP '+tabType+' IF EXISTS '+tabName
    except Exception as err:
        print_error(err, 'drop_table_qry')
コード例 #9
0
ファイル: pysql.py プロジェクト: AnilSingh79/pydan
def drop_table_qry(tabName, tabType=" TABLE "):
    try:
        return "DROP " + tabType + " IF EXISTS " + tabName
    except Exception as err:
        print_error(err, "drop_table_qry")