File I/O Operations in Python
File I/O is the point where a program works with data outside its immediate instructions.
From Python Program to Stored Data
File I/O is the point where a program works with data outside its immediate instructions. In this chapter, the storage target is a SQLite database file. The central question is not only where the data is stored, but also how Python reaches that stored data and performs operations on it.
The SQLite File as Storage
A SQLite database file is the storage location for the database and the data held in it. In the simple examples described by this chapter, the file is local: it is in the same directory as the Python code being run. The named file used in the source material is music.sqlite.
Connection Before Operations
The connect operation makes a connection to the database stored in the file music.sqlite in the current directory. If that file does not exist, it will be created.
Think of the connection as establishing which database file the Python program will work with. The connection is associated with music.sqlite. If Python does not find that file in the current directory, the source material states that the file is created. This gives the program a database file as its local storage target.
Cursor as the Working Handle
A cursor is like a file handle that can be used to perform operations on the data stored in the database. Calling cursor() is conceptually similar to calling open() when dealing with text files.
The connection identifies the database file, while the cursor provides the working access used for operations on the data in that database. This distinction is useful because it separates two stages: first connect to the database file, then obtain a cursor for working with the stored data.
Tracing a local database task
A Python program needs to work with data in music.sqlite, located in the current directory.
- 1Locate the target
The target is the local database file named
music.sqlite. - 2Make the connection
The connect operation makes a connection to the database stored in
music.sqlite. If the file does not exist, it is created. - 3Obtain a cursor
The program calls
cursor()to obtain a cursor, which is like a file handle for performing operations on the database data. - 4Work with the data
The cursor is the working access point for operations on data stored in the database.
The data path is: Python program, connection to music.sqlite, cursor, and operations on data stored in the database file.
Python or Database Browser
Python is one way to work with data in SQLite database files. The source material also identifies Database Browser for SQLite as software that can perform many operations more conveniently. Both are presented as ways to work with the SQLite database file, but they serve different working styles: Python represents programmatic work, while Database Browser represents a dedicated software tool.
| Approach | What the source establishes | When it may be convenient |
|---|---|---|
| Python | Python can be used to work with data in SQLite database files. | When the work is being carried out from a Python program. |
| Database Browser for SQLite | Many operations can be performed more conveniently with this software. | When a dedicated database-browser tool is more convenient for the operation. |
High-level comparison of the two tools described in the source material.
Common Mistakes
Common mistake Treating music.sqlite as if it were necessarily stored on a separate database server.
What it looks likeAssuming that the word connection means the simple example must use a remote system.
Why it failsThe source explains that the simple examples use a local file in the same directory as the Python code.
Do this insteadFirst identify the setup being discussed. In the simple setup, connect makes a connection to the local music.sqlite file.
Common mistake Using connection and cursor as if they describe the same role.
What it looks likeDescribing the connection as the file handle used for database operations.
Why it failsThe connection is made to the database stored in the file, while the cursor is like a file handle used to perform operations on the stored data.
Do this insteadDescribe the sequence separately: connect to the database file, then call cursor() to obtain the working handle.
Common mistake Assuming an absent music.sqlite file makes the connection impossible.
What it looks likeExpecting the file to have been created before the connect operation can be used.
Why it failsThe source states that if the file does not exist, it will be created.
Do this insteadRemember that the described connect operation creates the file when it is absent.
Common mistake Assuming Python is the only convenient way to work with the SQLite file.
What it looks likeIgnoring Database Browser for SQLite when a dedicated tool would be more convenient.
Why it failsThe source notes that many operations can be done more conveniently using Database Browser for SQLite.
Do this insteadChoose between Python and Database Browser according to the working context described in the chapter.
Practice the Data Path
A learner says: “The cursor connects Python to the remote database server, and music.sqlite must already exist.” Identify the two inaccurate parts of this statement and replace them with a source-grounded description of the simple local setup.
Hints
- Separate the role of connect from the role of
cursor(). - Recall where
music.sqliteis located in the simple examples. - Recall what happens if the file does not exist.
What do you think happens?
In the simple setup, what happens when the connect operation targets music.sqlite in the current directory and that file is absent?
Reveal answer
The file is created.
The source states that if music.sqlite does not exist, the connect operation creates it.
Key Takeaways
- 1In the simple setup, Python works with data stored in the local SQLite database file
music.sqlite. - 2The connect operation makes a connection to that database file in the current directory.
- 3If
music.sqlitedoes not exist, the described connect operation creates it. - 4A cursor is like a file handle and is used to perform operations on data stored in the database.
- 5Database Browser for SQLite is another tool that can make many operations more convenient.
At a glance
- A SQLite database file is the local storage target in the simple Python examples.
- Connecting identifies the database stored in
music.sqlite, and the file is created if it is absent. - The cursor provides file-handle-like access for operations on the database data.
- Python and Database Browser for SQLite are two ways to work with the same kind of database file.