# What is a cursor?
A cursor is a structure that allows us to traverse a result set.
When you have a result set loaded into a cursor, you can usually go forward and backward in the results. Cursors help improve performance by not requiring us to fetch all the rows in a result set at once. Instead, we can ask for rows in small groups or one by one.
SQL Databases, such as SQLite and PostgreSQL, can make use of cursors. When you use a database cursor like these, you can execute queries but the results are not actually loaded by the database.
sqlite3 module in Python also uses cursors, but they are different from database cursors. The Python cursors are conceptual objects, used so that programmers can think of the result set as a iterator.
Using cursors means that code like this makes a bit of sense (don't pay attention to the SQL queries, we're going to learn about those in detail over the next few chapters):
connection = sqlite3.connect("data.db") cursor = connection.cursor() cursor.execute("SELECT * FROM users;") for row in cursor: print(row) connection.close()
You can think of the cursor as the arrow that moves from row to row in the animation above. We use it to point at each row as we process it (or in this case, access it).
However it makes less sense with code like this:
connection = sqlite3.connect("data.db") cursor = connection.cursor() cursor.execute("INSERT INTO users ('John Smith', 35);") connection.close()
After all, we're not receiving any results and we don't want to process each row of the results one by one... So why use a cursor at all? This is why many programmers believe that Python's
sqlite3 cursors are a conceptual overhead: not required, but still used.
I believe that this is just to make the code more consistent. Every query uses a cursor, and that's it. Otherwise, you'd need some queries using a cursor and others not using one, and it could be a bit confusing.
# Didn't we skip cursors before?
In the last chapter we wrote code that seemingly didn't use cursors:
def create_table(): connection = sqlite3.connect("data.db") connection.execute("CREATE TABLE entries (content TEXT, date TEXT);") connection.close()
But actually, the
connection.execute method creates a cursor for you, so it really is the same thing as creating the cursor manually. However, it is a little bit shorter so you can use this when you don't need to make it explicit that a cursor is being used.
connection.execute method returns the cursor for you, so you can always use it to go through rows if you were executing a
SELECT statement. We're going to learn how to do that in the coming chapters (after we learn about
SELECT, of course!).