在我构建的Python程序中,以下代码有问题。该程序从数据库中提取数据,并根据它们输入的参考号列表'GrantRefNumber'
将其放入Excel电子表格中。你知道吗
它可以工作,但由于某些原因,只有第一个引用号应用了'a.reporting_period_id like 'none-'
,其余的没有。你知道吗
我在SQL代码中使用一个变量替换,将引用号列表放入字符串中。你知道吗
任何帮助都将不胜感激!你知道吗
SQL:
"SELECT a.fa_reference as [GrantRefNumber],
a.fa_name as [Award Title],
a.location as [RO],
ISNULL(cat.grant_department_name, '') as [Department],
a.funding_start_date as [Start Date],
a.funding_end_date as [End Date],
a.[pi] as [PI ID],
a.pi_initials as [PI Name],
a.pi_surname as [PI Surname],
r1_2 as [Type],
DATEADD(s, cast(last_submitted_date as int), '1970-01-01 00:00:00') as [Submitted Date]
from keywordagreements a inner join entries_publications p on a.id =
p.agreement_id left outer join mrc_categories cat on a.origid = cat.id and cat.centre not in ('2')
where a.[pi] NOT LIKE 'S%' and response_code not like 'Test' and Closed is null
and a.reporting_period_id like 'none-'
and a.funding_organisation not like '%UKSA%' and {}'
Order by [RO], [PI ID], [GrantRefNumber]".format(finalList)
finalList(变量替换):
finalList是一个引用号列表,我从Python中的用户那里得到,名为“items”
items = dfCall['GrantRefNumber'].values.tolist()
refList = " OR ".join(["a.fa_reference LIKE '%s'" % num for num in items])
finalList = refList[:-1]
我正在使用PYODBC提取数据。你知道吗
我的代码中的SQL如下所示(我在前面的代码中删除了引号(和一些列),以使其更易于阅读):
stringQ = "SELECT a.fa_reference as [GrantRefNumber], a.fa_name as [Award Title]," \
" a.location as [RO], ISNULL(cat.grant_department_name, '') as [Department]," \
" a.funding_start_date as [Start Date], a.funding_end_date as [End Date]," \
" a.[pi] as [PI ID], a.pi_initials as [PI Name]," \
" a.pi_surname as [PI Surname], " \
" r1_2 as [Type], ISNULL(r1_2_1, '') as [PubMed ID]," \
" r1_2_2 as [Author], r1_2_3 as [Publication], ISNULL(r1_2_4, '') as [Journal]," \
" ISNULL(r1_2_8, '') as [Month], ISNULL(r1_2_9, '') as [Year], ISNULL(r1_2_4_1, '') as [Conference]," \
" ISNULL(r1_2_36, '') as [PubMed Central ID], ISNULL (r1_2_19, '') as [DOI]," \
" case when nullif(r1_2_1,'') is not null then 'http://europepmc.org/abstract/MED/' + r1_2_1 else case when" \
" nullif(r1_4,'') is not null then r1_4 else case when nullif(r1_2_19,'') is not null then" \
" 'http://dx.doi.org/' + r1_2_19 else isnull(r1_2_1,'') end end end as [URL], ISNULL(r1_2_21, '') " \
"as [ISBN]," \
" ISNULL(r1_2_30, '') as [ISBN (Electronic)], " \
" ISNULL(r1_2_25, '') as [Chapter Number], " \
"ISNULL(r1_2_26, '')" \
" as [Chapter Title], ISNULL(r1_2_27, '') as [Chapter Author]," \
" ISNULL(r1_2_29, '') as [ISSN (Print)], ISNULL(r1_2_32, '') as [ISSN (Digital)], " \
"ISNULL(r1_2_31, '') as [Web of Science ID], ISNULL(r1_2_34, '') as [Scopus ID], " \
"ISNULL(r1_2_35, '') as [arXiv DepositID]," \
" ISNULL(r1_2_38, '') as [Bibcode], ISNULL(r1_2_39, '') as [Ethos], ISNULL(r1_2_43, '') as [NASA-ADS ID]," \
" ISNULL(r1_2_46, '') as [Inspire], ISNULL(r1_2_40, '') as [PMC Manuscript ID], ISNULL(r1_2_45, '')" \
" as [ORCID Work Putcode]," \
" ISNULL(r1_2_61, '') as [OpenAire Access License], ISNULL(r1_2_52, '') " \
"as [In EPMC?], ISNULL(r1_2_53, '') as [In PMC?]," \
" ISNULL(r1_2_51, '') as [EPMC Open Access], " \
" DATEADD(s, cast(last_submitted_date as int), '1970-01-01 00:00:00') as [Submitted Date] " \
"from keywordagreements a inner join entries_publications p on a.id = " \
"p.agreement_id left outer join mrc_categories cat " \
"on a.origid = cat.id and cat.centre not in ('2') where a.[pi] NOT LIKE 'S%' and " \
"response_code not like 'Test' and Closed is null " \
"and a.reporting_period_id like 'none-' " \
"and a.funding_organisation not like '%UKSA%' and {}' " \
"Order by [RO], [PI ID], [GrantRefNumber]".format(finalList)
我不知道你的
format
命令替代了什么。下面是format命令的几个示例:输出
This is a formatted string.
您需要修改SQL以将
finalList
插入到SQL中。由于我们看不到您的全部代码,因此您可能希望使用三重引号格式来支持多行:要非常小心,在任何SQL查询中使用字符串替换时,都会出现SQL注入。祝你好运!你知道吗
好的,我想我已经修好了。我将
{}'
放在方括号中,即({}')
,因为它将其他引用号视为一个单独的语句,因为OR命令。你知道吗相关问题 更多 >
编程相关推荐