File I/O Operations in Python
While this chapter will focus on using Python to work with data in SQLite database files, many operations can be done more conveniently using software called the Database Browser for SQLite which is freely available from:
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.
Locate the target: The target is the local database file named music.sqlite.
Make the connection: The connect operation makes a connection to the database stored in music.sqlite. If the file does not exist, it is created.
Obtain a cursor: The program calls cursor() to obtain a cursor, which is like a file handle for performing operations on the database data.
Work 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. |
Common Mistakes
Treating music.sqlite as if it were necessarily stored on a separate database server.
The source explains that the simple examples use a local file in the same directory as the Python code.
Fix:
First identify the setup being discussed. In the simple setup, connect makes a connection to the local music.sqlite file.Using connection and cursor as if they describe the same role.
The 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.
Fix:
Describe the sequence separately: connect to the database file, then call cursor() to obtain the working handle.Assuming an absent music.sqlite file makes the connection impossible.
The source states that if the file does not exist, it will be created.
Fix:
Remember that the described connect operation creates the file when it is absent.Assuming Python is the only convenient way to work with the SQLite file.
The source notes that many operations can be done more conveniently using Database Browser for SQLite.
Fix:
Choose 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.sqlite is 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
Answer: The file is created.
The source states that if music.sqlite does not exist, the connect operation creates it.
Key Takeaways
- In the simple setup, Python works with data stored in the local SQLite database file music.sqlite.
- The connect operation makes a connection to that database file in the current directory.
- If music.sqlite does not exist, the described connect operation creates it.
- A cursor is like a file handle and is used to perform operations on data stored in the database.
- Database Browser for SQLite is another tool that can make many operations more convenient.
Key Takeaways
- 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.