entityMap|blocks|key|5nvi2|text|通常设置为0。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|6pic6|但是，如果您运行的语句将|bpdfj|从不|2ka99|返回结果集(如|8t1vn|没有|4c82j|，或者|b49gq|)，则不需要调用|8qavi|；这样的语句不会有结果集。呼叫|b8a24|足以运行该语句。|glck|请注意，还允许数据库适配器将行数设置为|dupi1|如果数据库适配器无法确定确切的受影响计数。请参阅|dp349|PEP+249|c9ar1|规格|4bfte|该属性为|8169u|如果没有|433j3|已在游标上执行，或者接口无法确定最后一次操作的行数。|f6edu|c05q6|The+The+The|3c3i9|库|3518l|很容易这样做。在所有此类情况下，如果必须预先知道受影响的行数，请执行|8gcj3|首先在同一事务中选择。^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|1F|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|1G|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|1H|9|@]|A|@]|B|$]]|$2|G|4|H|6|7|8|1I|9|@]|A|@]|B|$]]|$2|I|4|J|6|7|8|1J|9|@]|A|@]|B|$]]|$2|K|4|L|6|7|8|1K|9|@]|A|@]|B|$]]|$2|M|4|N|6|7|8|1L|9|@]|A|@]|B|$]]|$2|O|4|P|6|7|8|1M|9|@]|A|@]|B|$]]|$2|Q|4|R|6|7|8|1N|9|@]|A|@]|B|$]]|$2|S|4|T|6|7|8|1O|9|@]|A|@]|B|$]]|$2|U|4|V|6|7|8|1P|9|@]|A|@]|B|$]]|$2|W|4|X|6|7|8|1Q|9|@]|A|@]|B|$]]|$2|Y|4|Z|6|7|8|1R|9|@]|A|@]|B|$]]|$2|10|4|11|6|7|8|1S|9|@]|A|@]|B|$]]|$2|12|4|13|6|7|8|1T|9|@]|A|@]|B|$]]|$2|14|4|15|6|7|8|1U|9|@]|A|@]|B|$]]|$2|16|4|-4|6|7|8|1V|9|@]|A|@]|B|$]]|$2|17|4|18|6|7|8|1W|9|@]|A|@]|B|$]]|$2|19|4|1A|6|7|8|1X|9|@]|A|@]|B|$]]|$2|1B|4|1C|6|7|8|1Y|9|@]|A|@]|B|$]]|$2|1D|4|1E|6|7|8|1Z|9|@]|A|@]|B|$]]]]

<code>cursor.rowcount</code> will usually be set to 0.

If, however, you are running a statement that would never return a result set (such as <code>INSERT</code> without <code>RETURNING</code>, or <code>SELECT ... INTO</code>), then you do not need to call <code>.fetchall()</code>; there won't be a result set for such statements. Calling <code>.execute()</code> is enough to run the statement.

<hr>

Note that database adapters are also allowed to set the rowcount to <code>-1</code> if the database adapter can't determine the exact affected count. See the <a href="https://www.python.org/dev/peps/pep-0249/#rowcount" rel="noreferrer">PEP 249 <code>Cursor.rowcount</code> specification</a>:

<blockquote>
 The attribute is <code>-1</code> in case no <code>.execute*()</code> has been performed on the cursor or the rowcount of the last operation is cannot be determined by the interface.
</blockquote>

The <a href="https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.rowcount" rel="noreferrer"><code>sqlite3</code> library</a> is prone to doing this. In all such cases, if you must know the affected rowcount up front, execute a <code>COUNT()</code> select in the same transaction first.

entityMap|blocks|key|4mg9q|text|我遇到了行数总是返回-1的问题，无论我尝试什么解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|d3e0s|我发现以下代码是检查null结果的一个很好的替代方法。|7qrcs|c.execute("SELECT+*+FROM+users+WHERE+id=?",+(id_num,))
row+=+c.fetchone()
if+row+==+None:
+++print("There+are+no+results+for+this+query")|code-block|syntax|javascript^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|J|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|K|9|@]|A|@]|B|$]]|$2|E|4|F|6|G|8|L|9|@]|A|@]|B|$H|I]]]]

I had issues with rowcount always returning -1 no matter what solution I tried.

I found the following a good replacement to check for a null result.

<pre><code>c.execute("SELECT * FROM users WHERE id=?", (id_num,))
row = c.fetchone()
if row == None:
 print("There are no results for this query")
</code></pre>

entityMap|blocks|key|4fcae|text|如果结果集为空，MySQLdb不会引发异常。此外，cursor.execute()函数将返回一个长值，即读取结果集中的行数。因此，如果要检查空结果，可以将代码重写为|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1o3kq|rows_count+=+cursor.execute(query_sql)
if+rows_count+>+0:
+++++rs+=+cursor.fetchall()
else:
+++++//+handle+empty+result+set|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

MySQLdb will not raise an exception if the result set is empty. Additionally cursor.execute() function will return a long value which is number of rows in the fetched result set. So if you want to check for empty results, your code can be re-written as

<pre><code>rows_count = cursor.execute(query_sql)
if rows_count &gt; 0:
 rs = cursor.fetchall()
else:
 // handle empty result set
</code></pre>

entityMap|blocks|key|51gm0|text|注意：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|90qic|这是Python语言中的MySQLdb模块。|41pd0|对于|ev9na|语句，则不应存在空记录集的异常。只有一个空列表(|1t3rv|)用于|8sbim|和|5u1ph|dl3jt|..。|5hj95|对于任何其他语句，例如|d26jq|或者|5u759|，它不返回记录集，因此您既不能调用|39rv1|也不是|i45i|在光标上。否则，将引发异常。|53qog|有一种方法可以区分上述两种类型的游标：|9g63c|def+yield_data(cursor):
++++while+True:
++++++++if+cursor.description+is+None:
++++++++++++#+No+recordset+for+INSERT,+UPDATE,+CREATE,+etc
++++++++++++pass
++++++++else:
++++++++++++#+Recordset+for+SELECT,+yield+data
++++++++++++yield+cursor.fetchall()
++++++++++++#+Or+yield+column+names+with
++++++++++++#+yield+[col[0]+for+col+in+cursor.description]

++++++++#+Go+to+the+next+recordset
++++++++if+not+cursor.nextset():
++++++++++++#+End+of+recordsets
++++++++++++return|code-block|syntax|javascript^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|16|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|17|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|18|9|@]|A|@]|B|$]]|$2|G|4|H|6|7|8|19|9|@]|A|@]|B|$]]|$2|I|4|J|6|7|8|1A|9|@]|A|@]|B|$]]|$2|K|4|L|6|7|8|1B|9|@]|A|@]|B|$]]|$2|M|4|F|6|7|8|1C|9|@]|A|@]|B|$]]|$2|N|4|O|6|7|8|1D|9|@]|A|@]|B|$]]|$2|P|4|Q|6|7|8|1E|9|@]|A|@]|B|$]]|$2|R|4|S|6|7|8|1F|9|@]|A|@]|B|$]]|$2|T|4|U|6|7|8|1G|9|@]|A|@]|B|$]]|$2|V|4|W|6|7|8|1H|9|@]|A|@]|B|$]]|$2|X|4|Y|6|7|8|1I|9|@]|A|@]|B|$]]|$2|Z|4|10|6|7|8|1J|9|@]|A|@]|B|$]]|$2|11|4|12|6|13|8|1K|9|@]|A|@]|B|$14|15]]]]

Notice: This is for MySQLdb module in Python.

For a <code>SELECT</code> statement, there shouldn't be an exception for an empty recordset. Just an empty list (<code>[]</code>) for <code>cursor.fetchall()</code> and <code>None</code> for <code>cursor.fetchone()</code>.

For any other statement, e.g. <code>INSERT</code> or <code>UPDATE</code>, that doesn't return a recordset, you can neither call <code>fetchall()</code> nor <code>fetchone()</code> on the cursor. Otherwise, an exception will be raised.

There's one way to distinguish between the above two types of cursors:

<pre><code>def yield_data(cursor):
 while True:
 if cursor.description is None:
 # No recordset for INSERT, UPDATE, CREATE, etc
 pass
 else:
 # Recordset for SELECT, yield data
 yield cursor.fetchall()
 # Or yield column names with
 # yield [col[0] for col in cursor.description]

 # Go to the next recordset
 if not cursor.nextset():
 # End of recordsets
 return
</code></pre>

entityMap|blocks|key|2ajj7|text|当我需要进行多个sql查询时，我遇到了类似的问题。问题是有些查询没有返回结果，而我想打印该结果。这是一个错误。如前所述，有几种解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|dbt8v|if+cursor.description+is+None:
++++#+No+recordset+for+INSERT,+UPDATE,+CREATE,+etc
++++pass
else:
++++#+Recordset+for+SELECT|code-block|syntax|javascript|bjmn0|以及：|ejh76|exist+=+cursor.fetchone()
if+exist+is+None:
++...+#+does+not+exist
else:
++...+#+exists|2iqla|其中一个解决方案是：|23mlt|The+The+The|1jc8m|和|eptem|块允许您处理|24mi6|/|34uah|..。The+The+The|2bbqi|块允许您执行代码，而不管|2sn40|dscgh|块。因此，所提出的问题可以通过使用它来解决。|86gki|s+=+"""+set+current+query+acceleration+=+enable;
++++++++set+current+GET_ACCEL_ARCHIVE+=+yes;
++++++++SELECT+*+FROM+TABLE_NAME;"""

query_sqls+=+[i.strip()+%2B+";"+for+i+in+filter(None,+s.split(';'))]
for+sql+in+query_sqls:
++++print(f"Executing+SQL+statements+====>+{sql}+<=====")
++++cursor.execute(sql)
++++print(f"SQL+====>+{sql}+<=====+was+executed+successfully")
++++try:
++++++++print("\n******************+RESULT+***********************")
++++++++for+result+in+cursor.fetchall():
++++++++++++print(result)
++++++++print("******************+END+RESULT+***********************\n")
++++except+Exception+as+e:
++++++++print(f"SQL:+====>+{sql}+<====+doesn't+have+output!\n")
++++++++#+print(str(e))|5hoeu|输出：|s4qc|Executing+SQL+statements+====>+set+current+query+acceleration+=+enable;+<=====
SQL:+====>+set+current+query+acceleration+=+enable;+<====+doesn't+have+output!

Executing+SQL+statements+====>+set+current+GET_ACCEL_ARCHIVE+=+yes;+<=====
SQL:+====>+set+current+GET_ACCEL_ARCHIVE+=+yes;+<====+doesn't+have+output!

Executing+SQL+statements+====>+SELECT+*+FROM+TABLE_NAME;+<=====

******************+RESULT+***********************

+++++++----------+++DATA+++----------

******************+END+RESULT+***********************|niv9|上面的例子只提供了一个简单的用法，作为一个想法可以帮助你的解决方案。当然，您还应该注意其他错误，如查询的正确性等。^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|1A|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|1B|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|1C|9|@]|A|@]|B|$]]|$2|J|4|K|6|E|8|1D|9|@]|A|@]|B|$F|G]]|$2|L|4|M|6|7|8|1E|9|@]|A|@]|B|$]]|$2|N|4|O|6|7|8|1F|9|@]|A|@]|B|$]]|$2|P|4|Q|6|7|8|1G|9|@]|A|@]|B|$]]|$2|R|4|S|6|7|8|1H|9|@]|A|@]|B|$]]|$2|T|4|U|6|7|8|1I|9|@]|A|@]|B|$]]|$2|V|4|W|6|7|8|1J|9|@]|A|@]|B|$]]|$2|X|4|Y|6|7|8|1K|9|@]|A|@]|B|$]]|$2|Z|4|Q|6|7|8|1L|9|@]|A|@]|B|$]]|$2|10|4|11|6|7|8|1M|9|@]|A|@]|B|$]]|$2|12|4|13|6|E|8|1N|9|@]|A|@]|B|$F|G]]|$2|14|4|15|6|7|8|1O|9|@]|A|@]|B|$]]|$2|16|4|17|6|E|8|1P|9|@]|A|@]|B|$F|G]]|$2|18|4|19|6|7|8|1Q|9|@]|A|@]|B|$]]]]

I had a similar problem when I needed to make multiple sql queries.
The problem was that some queries did not return the result and I wanted to print that result. And there was a mistake. As already written, there are several solutions.
<pre class="lang-py prettyprint-override"><code>if cursor.description is None:
 # No recordset for INSERT, UPDATE, CREATE, etc
 pass
else:
 # Recordset for SELECT
</code></pre>
As well as:
<pre class="lang-py prettyprint-override"><code>exist = cursor.fetchone()
if exist is None:
 ... # does not exist
else:
 ... # exists
</code></pre>
One of the solutions is:
The <code>try</code> and <code>except</code> block lets you handle the <code>error</code>/<code>exceptions</code>. The <code>finally</code> block lets you execute code, regardless of the result of the <code>try</code> and <code>except</code> blocks.
So the presented problem can be solved by using it.
<pre class="lang-py prettyprint-override"><code>s = &quot;&quot;&quot; set current query acceleration = enable;
 set current GET_ACCEL_ARCHIVE = yes;
 SELECT * FROM TABLE_NAME;&quot;&quot;&quot;

query_sqls = [i.strip() + &quot;;&quot; for i in filter(None, s.split(';'))]
for sql in query_sqls:
 print(f&quot;Executing SQL statements ====&gt; {sql} &lt;=====&quot;)
 cursor.execute(sql)
 print(f&quot;SQL ====&gt; {sql} &lt;===== was executed successfully&quot;)
 try:
 print(&quot;\n****************** RESULT ***********************&quot;)
 for result in cursor.fetchall():
 print(result)
 print(&quot;****************** END RESULT ***********************\n&quot;)
 except Exception as e:
 print(f&quot;SQL: ====&gt; {sql} &lt;==== doesn't have output!\n&quot;)
 # print(str(e))
</code></pre>
output:
<pre class="lang-sh prettyprint-override"><code>Executing SQL statements ====&gt; set current query acceleration = enable; &lt;=====
SQL: ====&gt; set current query acceleration = enable; &lt;==== doesn't have output!

Executing SQL statements ====&gt; set current GET_ACCEL_ARCHIVE = yes; &lt;=====
SQL: ====&gt; set current GET_ACCEL_ARCHIVE = yes; &lt;==== doesn't have output!

Executing SQL statements ====&gt; SELECT * FROM TABLE_NAME; &lt;=====

****************** RESULT ***********************

 ---------- DATA ----------

****************** END RESULT ***********************
</code></pre>
The example above only presents a simple use as an idea that could help with your solution. Of course, you should also pay attention to other errors, such as the correctness of the query, etc.

entityMap|blocks|key|fq70p|text|如果您连接到postgres数据库，则可以执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|cnq2a|result+=+cursor.execute(query)

if+result.returns_rows:
++++#+we+got+rows!
++++return+[{k:v+for+k,v+in+zip(result.keys(),+r)}+for+r+in+result.rows]
else:
++++return+None|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

if you're connecting to a postgres database, the following works:

<pre><code>result = cursor.execute(query)

if result.returns_rows:
 # we got rows!
 return [{k:v for k,v in zip(result.keys(), r)} for r in result.rows]
else:
 return None
</code></pre>

entityMap|blocks|key|e1inf|text|你可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|b4p3v|count+=+0
cnxn+=+pyodbc.connect("Driver={SQL+Server+Native+Client+11.0};"
++++++++++++++++++++++"Server=serverName;"
++++++++++++++++++++++"Trusted_Connection=yes;")
cursor+=+cnxn.cursor()
cursor.execute(SQL+query)
for+row+in+cursor:
++++count+=+1
++++if+true+condition:
++++++++print("True")
++++else:
++++++++print("False")
if+count+==+0:
++++print("No+Result")|code-block|syntax|javascript|asfsg|谢谢:)^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|J|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|K|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|L|9|@]|A|@]|B|$]]]]

You can do like this :

<pre><code>count = 0
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
 "Server=serverName;"
 "Trusted_Connection=yes;")
cursor = cnxn.cursor()
cursor.execute(SQL query)
for row in cursor:
 count = 1
 if true condition:
 print("True")
 else:
 print("False")
if count == 0:
 print("No Result")
</code></pre>

Thanks :)

entityMap|blocks|key|cai2n|text|作为参考，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3vtpr|将仅在|8pa0f|68v4v|和|8vi2f|声明：|7buof|%7C++rowcount
+%7C++++++This+read-only+attribute+specifies+the+number+of+rows+the+last+DML+statement
+%7C++++++(INSERT,+UPDATE,+DELETE)+affected.++This+is+set+to+-1+for+SELECT+statements.|code-block|syntax|javascript^0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|O|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|P|9|@]|A|@]|B|$]]|$2|E|4|-4|6|7|8|Q|9|@]|A|@]|B|$]]|$2|F|4|G|6|7|8|R|9|@]|A|@]|B|$]]|$2|H|4|I|6|7|8|S|9|@]|A|@]|B|$]]|$2|J|4|K|6|L|8|T|9|@]|A|@]|B|$M|N]]]]

For reference, <code>cursor.rowcount</code> will only return on <code>CREATE</code>, <code>UPDATE</code> and <code>DELETE</code> statements:

<pre><code> | rowcount
 | This read-only attribute specifies the number of rows the last DML statement
 | (INSERT, UPDATE, DELETE) affected. This is set to -1 for SELECT statements.
</code></pre>

I have a sql statement that returns no hits. For example, <code>'select * from TAB where 1 = 2'</code>.

I want to check how many rows are returned, 

<pre><code>cursor.execute(query_sql)

rs = cursor.fetchall()
</code></pre>

Here I get already exception: "(0, 'No result set')" 

How can I prevend this exception, check whether the result set is empty?

How to check if a result set is empty?

SQL Server

数据库

Python

 我有一条sql语句，它不返回任何命中结果。例如， ..。 我想检查返回了多少行， cursor.execute(query_sql)rs = cursor.fetchall() 这里我得到了一个异常："(0，'No result set')“ 如何防止这个异常，检查结果集是否为空？

问如何检查结果集是否为空？
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何检查结果集是否为空？EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何检查结果集是否为空？
EN