Блог

Изначально я запустил это для импорта в базу данных NoSQL, которая работает нормально, но не смог найти правильные инструменты для написания отчета. Итак, я переключаюсь на SQL Server, чтобы я мог использовать SSRS. Но у меня возникают проблемы с импортом данных в таблицы в моей базе данных SQL Server

 importncdrdata = "INSERT INTO ncdr.dbo.ncdrdatatest (Hospital, YrQTRid, MetricKey, LineText, Year, Quarter, YrQtrRange, qtrnum, qtrden, qtrpercent, subgroup) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
importvolgroup = "INSERT INTO ncdr.dbo.ncdrvoltest (Hospital, YrQTRid, MetricKey, LineText, Year, Quarter, YrQtrRange, rqtrnum, rqtrden, rqtrpercent, volnum, volpercent, subgroup) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

def importncdrfile():
        # Open the workbook and select the first worksheet
        wb = xlrd.open_workbook(self.filename)
        sh = wb.sheet_by_index(1)
        # Function to import main set of data
        if yearqtrid.get() == "2019Q3":
            def excelToDict(hospital, metricKey, lineText, year, quarter, yrQtrRange, num, den, percent):
                for rownum in range(12, sh.nrows):
                    ncdr = OrderedDict()
                    row_values = sh.row_values(rownum)
                    ncdr['Hospital'] = hospitalchosen.get()
                    ncdr['YrQTRid'] = yearqtrid.get()
                    ncdr['MetricKey'] = row_values[metricKey]
                    ncdr['LineText'] = row_values[lineText]
                    ncdr['Year'] = year
                    ncdr['Quarter'] = quarter
                    ncdr['YrQtrRange'] = yrQtrRange
                    ncdr['qtrnum'] = row_values[num]
                    ncdr['qtrden'] = row_values[den]
                    ncdr['qtrpercent'] = row_values[percent]
                    ncdr['SubGroup'] = 'holder'
                    ncdr_list.append(ncdr)

            # List to hold dictionaries
            ncdr_list = []
            # Imports data into MongoDB takes the aboce code and then runs the function against each set of data and then repeats for all 4 Quarters
            excelToDict(n,0, 1, 2018, 4, 20184, 2, 4, 6)
            excelToDict(n,0, 1, 2019, 1, 20191, 8, 10, 12)
            excelToDict(n,0, 1, 2019, 2, 20192, 14, 16, 18)
            excelToDict(n,0, 1, 2019, 3, 20193, 20, 22, 24)
            cursor.executemany(importncdrdata, ncdr_list)
            #x = importncdrdata.insert_many(ncdr_list)
            # function to import volume group and rolling quarters
            def excelToDictvol(hospital, metricKey, lineText, year, quarter, yrQtrRange, rqtrnum, rqtrden, rqtrpercent, volnum, volpercent):
                for rownum in range(12, sh.nrows):
                    ncdrvol = OrderedDict()
                    row_values = sh.row_values(rownum)
                    ncdrvol['Hospital'] = hospitalchosen.get()
                    ncdrvol['YrQTRid'] = yearqtrid.get()
                    ncdrvol['MetricKey'] = row_values[metricKey]
                    ncdrvol['LineText'] = row_values[lineText]
                    ncdrvol['Year'] = year
                    ncdrvol['Quarter'] = quarter
                    ncdrvol['YrQtrRange'] = yrQtrRange
                    ncdrvol['rqtrnum'] = row_values[rqtrnum]
                    ncdrvol['rqtrden'] = row_values[rqtrden]
                    ncdrvol['rqtrpercent'] = row_values[rqtrpercent]
                    ncdrvol['volnum'] = row_values[volnum]
                    ncdrvol['volpercent'] = row_values[volpercent]
                    ncdrvol['SubGroup'] = 'holder'
                    ncdr_vollist.append(ncdrvol)

            ncdr_vollist = []
            excelToDictvol(n,0, 1, 2019, 3, 20193, 26, 28, 30, 32, 34)
            #v = importvolgroup.insert_many(ncdr_vollist)
            cursor.executemany(importvolgroup, ncdr_vollist)
  

Ошибка, которую я получаю, это

Ошибка типа: (‘Параметры должны быть в списке, кортеже или строке’, ‘HY000’)

Но я не уверен, как перенести мои данные в список, кортеж или строку для импорта в мои таблицы SQL Server

редактировать Я только что закончил редактирование его в кортеж

             if yearqtrid.get() == "2019Q3":
            def excelToTuple(hospital, metricKey, lineText, year, quarter, yrQtrRange, num, den, percent):
                for rownum in range(12, sh.nrows):
                    ncdr = tuple(['Hospital','YrQTRid','Metrickey','LineText','Year','Quarter','YrQtrRange','qtrnum','qtrden','qtrpercent','SubGroup'])
                    row_values = sh.row_values(rownum)
                    if isinstance(row_values[metricKey], str) :
                         subgroup = (row_values[metricKey], str)
                    ncdr = (hospitalchosen.get(),yearqtrid.get(),row_values[metricKey],row_values[lineText],year,quarter,yrQtrRange,row_values[num],row_values[den],row_values[percent],subgroup)
                    ncdr_tuple.append(ncdr)