使用Python时，SQL.fetchall()失败，并显示SQLState 24000 (游标状态无效
目前，我成功地连接到一个数据库，然后执行一些sql语句，但是当我尝试检索查询结果时，我遇到了以下raise ProgrammingError(state,err_text) pypyodbc.ProgrammingError: ('24000', '[24000] [Microsoft][ODBC SQL Server Driver]Invalid cursor state')。
这是我的代码：
# Connection to MSSQL
import pypyodbc
connection_live_db = pypyodbc.connect(driver="{SQL Server}", server="xxx.xxx.xxx.xxx", uid="test",
pwd="xxxx", Trusted_Connection="No")
credit_hold_co = connection_live_db.cursor()
# read the sql file in buffer and close
read_sql_file = open('C:\\Users\\userX\\Documents\\Scripts\\credit_hold_co.sql','r')
sql_file = read_sql_file.read()
read_sql_file.close()
# split each sql statement by ;
sqlCommands = sql_file.split(';')
results_sql = ""
# iterate over each command and execute it
for command in sqlCommands:
try:
credit_hold_co.execute(command)
except ValueError:
print(command)
# retrieve results
results = results + str(credit_hold_co.fetchall())
# close sql connection
credit_hold_co.close()这是我尝试执行的sql命令的一个示例：
-- credit_hold_co
if OBJECT_ID('tempdb..#Credit_Hold_CO') is not NULL
drop table #Credit_Hold_CO;
create table #Credit_Hold_CO
([co_num] varchar(30),
[credit_hold] char(1),
[credit_hold_reason] char(5),
['Type of credit hold reason'] varchar(20),
[credit_hold_date] datetime );
insert into #Credit_Hold_CO([co_num],[credit_hold],[credit_hold_reason],['Type of credit hold reason'],[credit_hold_date])
select distinct co_num, credit_hold, credit_hold_reason,
(case
when credit_hold_reason = 'PD' then 'Past due payments'
when credit_hold_reason = 'BR' then 'Bankruptcy'
end) as 'Type of credit hold reason',
credit_hold_date
FROM [Database].[dbo].[co] where credit_hold = '1';
select * from #Credit_Hold_CO
order by orig_site;
drop table #Credit_Hold_CO;我找到了一些指向当前问题的链接，例如pypyodbc invalid cursor name和SQL statement invalid cursor state 24000，它们都建议创建另一个游标，以避免第一个游标的结果无效，但没有提供更多详细信息。
问答更新于7月17日13:31格林尼治标准时间：
我如何才能正确地检索sql语句的结果？
- 请参阅来自query.
的答案我非常需要另一个游标，或者是否有更好的方法来解决此问题？否，另一个游标不是解决方案，而是从SQL query.中删除临时表。
回答 1
Stack Overflow用户
发布于 2018-07-18 02:37:15
我和Alejandro BR继续在StackOverflow聊天中发表了广泛的评论。经过一些调试之后，它变成了两层：
-The文件应该是ANSI编码的，或者如果是UTF-8，则按如下方式读取它：open("your location","r", encoding="utf-8", errors="replace")
用下面的方法-Simplify Python，让它离开地面，然后继续循环它。每个SELECT都需要一个光标
在下面找到更新后的查询：
# Connection to MSSQL
import pypyodbc
connection_live_db = pypyodbc.connect(driver="{SQL Server}", server="xxx.xxx.xxx.xxx", uid="test",
pwd="xxxx", Trusted_Connection="No")
credit_hold_co = connection_live_db.cursor()
# read the .txt file that contains the query
# Avoid strange characters by saving the file in ANSI
# or use the following code from below if you save in UTF-8
read_sql_file = open('C:\\Users\\userX\\Documents\\Scripts\\credit_hold_co.txt','r', encoding="utf-8", errors="replace")
sql_file = read_sql_file.read()
read_sql_file.close()
# Execute the file that contains the sql query
credit_hold_customer_orders.execute(sql_file)
# store the results in variable
results = credit_hold_customer_orders.fetchall()
# close cursor
credit_hold_customer_orders.close()
print(results)此外，我们还必须从txt文件中更新sql查询：
select distinct co_num, credit_hold, credit_hold_reason,
(case
when credit_hold_reason = 'PD' then 'Past due payments'
when credit_hold_reason = 'BR' then 'Bankruptcy'
end) as 'Type of credit hold reason',
credit_hold_date
FROM [Database1].[dbo].[co] where credit_hold = '1'
union all
select distinct co_num, credit_hold, credit_hold_reason,
(case
when credit_hold_reason = 'PD' then 'Past due payments'
when credit_hold_reason = 'BR' then 'Bankruptcy'
end) as 'Type of credit hold reason',
credit_hold_date
FROM [Database2].[dbo].[co] where credit_hold = '1'https://stackoverflow.com/questions/51386475
复制相似问题