6

I'm downloading some data from a SQL Server database through a library that leverages pymssql in the back-end. The result of a curson.execute("""<QUERY BODY>""") is a sqlalchemy.engine.result.ResultProxy object. How can I check if the result of the query was empty, so there are no rows?

cur = ff.sql.create_engine(server=dw.address, db=dw.BI_DW, login=":".join([os.environ["SQL_USER"], os.environ["SQL_PASSWD"]])) for n in range(100): result = cur.execute("""QUERY BODY;""") if result: break

Unfortunately, result will never be None even when no rows were returned by the SQL query.

What's the best way to check for that?

Improve this question
6
  • Can you provide the text of your query? If the only thing you need to do is check whether the amount of fetched rows is zero than there are probably more effective ways to achieve this. Commented Jun 8, 2016 at 11:22
  • The query body is not important. The issue I'm having is that the table in SQL wasn't partitioned with yesterday data, thus didn't return any row. What I need to implement is some logic in my script that understand whether the amount of fetched rows is zero. Commented Jun 8, 2016 at 11:28
  • Why can't you just use COUNT(*) in your query? Commented Jun 8, 2016 at 11:31
  • The for loop is a (admittedly stupid) way to keep querying the database until I get some results. I'm going to refactor that. Commented Jun 8, 2016 at 11:33
  • I don't like the solution of counting the number of rows, because this means I need to run two queries. One to count the number of rows and in case stop the program, and the second one to fetch the results that I'll then process one row at the time. Commented Jun 8, 2016 at 11:35

2 Answers 2

Reset to default
10

The ResultProxy object does not contain any rows yet. Therefore it has no information about the total amount of them, or even whether there are any. ResultProxy is just a "pointer" to the database. You get your rows only when you explicitly fetch them via ResultProxy. You can do that via iteration over this object, or via .first() method, or via .fetchall() method.

Bottom line: you cannot know the amount of fethced rows until you actually fetch all of them and the ResultProxy object is exhausted.

Approach #1

You can fetch all the rows at once and count them and then do whatever you need with them:

rows = result.fetchall() if len(rows): # do something with rows

The downside of this method is that we load all rows into memory at once (rows is a Python list containing all the fetched rows). This may not be desirable if the amount of fetched rows is very large and/or if you only need to iterate over the rows one-by-one independently (usually that's the case).

Approach #2

If loading all fetched rows into memory at once is not acceptable, then we can do this:

rows_amount = 0 for row in result: rows_amount += 1 # do something with row if not rows_amount: print('There were zero rows') else: print('{} rows were fetched and processed'.format(rows_amount))
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I didn't even think about first(). However, I do need to process those rows after this check, so this solution won't work for me.
sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically. I followed your comment fetchone() fetchall nothing
0

SQLAlchemy < 1.2: You can always turn the ResultProxy into an iterator:

res = engine.execute(...) rp_iter = iter(res) row_count = 0 try: row = next(rp_iter) row_count += 1 except StopIteration: # end of data if not row_count: # no rows returned, StopIteration was raised on first attempt

In SQLAlchemy >= 1.2, the ResultProxy implements both .next() and .__next__(), so you do not need to create the iterator:

res = engine.execute() row_count = 0 try: row = next(res) row_count += 1 except StopIteration: ...
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.