Data Manipulation through SQL: One Mark Questions, Book Back Answers and Notes

About Data Manipulation through SQL

Medium ~120 min study

Using SQL from a Python Program

In the SQL chapter you typed commands directly. Real programs usually send those same commands from code, with values taken from variables and lists. Python comes with the sqlite3 module for this. SQLite is a small relational database that needs no separate server: the whole database is one file on disk, such as Academy.db.

Every program in this chapter follows the same pattern. Import sqlite3, open a connection with sqlite3.connect(), create a cursor, pass SQL strings to cursor.execute(), call connection.commit() if the data was changed, and finally connection.close(). Once that pattern is clear, the rest is ordinary SQL inside Python strings.

Be ready to write the connection steps from memory, and to explain the cursor, fetchone versus fetchmany, what sqlite_master stores and why commit is needed. The last program in the chapter reads records and saves them to a CSV file, so keep the csv.writer steps from Unit 13 fresh.

What you'll learn

• Write the steps to connect to an SQLite database from Python.
• Create a table with suitable columns and an INTEGER PRIMARY KEY.
• Insert records into a table from values held in a Python list.
• Explain and use fetchone(), fetchmany() and fetchall().
• Filter, group and sort records using SQL clauses and aggregate functions in a Python program.
• Save the result of a query to a CSV file.

Before you start

• SQL commands and clauses from the Structured Query Language chapter.
• Python loops, lists and tuples.
• Reading and writing CSV files with the csv module.

Data Manipulation through SQL explained

Programmatic SQL Operations in Python

Connection and Cursor

After import sqlite3, the statement connection = sqlite3.connect("Academy.db") opens the database file, or creates it if it does not exist. You can give a full path instead of just a file name. Next, cursor = connection.cursor() creates a cursor object. A cursor is a control structure used to go through the records in a database. Every SQL command is sent with cursor.execute(), and the cursor also holds the rows returned by a SELECT query.

Creating a Table and Adding Records

A CREATE TABLE command is written as a string, for example in sql_command, and passed to cursor.execute(). The textbook's Student table has Rollno as INTEGER PRIMARY KEY. If NULL is inserted into such a column, SQLite automatically gives it the next number, so each student gets a unique roll number. Records are added with INSERT INTO statements. To insert many records, the textbook keeps them in a list and builds each INSERT string with format() inside a loop.

Fetching Records

After a SELECT query is executed, the results are read from the cursor. fetchall() returns all remaining rows as a list of tuples. fetchone() returns the next single row, or None when no rows are left. fetchmany(n) returns the next n rows, so cursor.fetchmany(3) gives three records at a time. cursor.description holds details of each column, and the textbook uses it to print the field names.

Clauses, Operators and Aggregate Functions

The same SQL clauses from Unit 12 work here. DISTINCT removes repeated values. WHERE filters rows, and AND, OR and NOT combine conditions. ORDER BY sorts in ascending order by default or in descending order with DESC. GROUP BY with HAVING groups rows and filters the groups. The aggregate functions COUNT(), AVG(), SUM(), MAX() and MIN() each return a single value from a set of rows.

Updating and Deleting Records

UPDATE changes values in the rows that satisfy the WHERE condition, and DELETE removes those rows. After INSERT, UPDATE or DELETE, connection.commit() must be called to save the changes in the database file. Calling close() without committing loses those changes. The table sqlite_master is created automatically and stores details of the database schema, such as the names of the tables, so SELECT name FROM sqlite_master WHERE type='table' lists all tables.

Writing Query Results to a CSV File

Query results can be saved as a CSV file by combining sqlite3 with the csv module. Run the SELECT query, fetch the rows, open a file in write mode, create a csv.writer, write the column names from cursor.description as the header, then write the rows with writerows(). The resulting file opens in any spreadsheet program, so the data can be viewed without SQLite.

Common mistakes to avoid

• Forgetting connection.commit() after INSERT, UPDATE or DELETE. The program runs without error, but the changes are not saved.
• Trying to run SQL without a cursor. Create one with connection.cursor() and send every command through cursor.execute().
• Writing Windows paths with single backslashes, such as "c:\new\data.db", where \n becomes a newline. Use double backslashes or forward slashes.
• Thinking close() saves the data. close() only ends the connection; commit() is what saves the changes.
• Indexing the result of fetchone() without checking it. When no row is left it returns None, and fetchall() returns an empty list, so check before using [0].

Test yourself on these with the practice test, then check the worked reasoning in the solved MCQs.

Frequently asked questions

How do I connect Python to an SQLite database?

Import the sqlite3 module and call sqlite3.connect() with the database file name or its full path, for example sqlite3.connect("Academy.db"). It returns a connection object, and the file is created if it does not already exist.

What is the role of a cursor?

A cursor is a control structure used to go through the records of a database. In Python every SQL command is run with cursor.execute(), and the rows returned by a SELECT query are read from the same cursor.

What is the difference between fetchone() and fetchall()?

fetchone() returns the next row of the result as a tuple, or None when no rows remain. fetchall() returns all the remaining rows together as a list of tuples.

Why is commit() needed?

INSERT, UPDATE and DELETE change the database only within the current transaction. connection.commit() saves those changes in the database file. If the connection is closed without commit, the changes are lost.

Can I fetch only a certain number of records?

Yes. fetchmany(n) returns the next n rows of the result. For example, cursor.fetchmany(3) returns three records, and calling it again gives the next three, which helps when the result is large.

How do I save query results to a CSV file?

Execute the SELECT query and fetch the rows. Open a CSV file in write mode, create a csv.writer, write the column names taken from cursor.description, then write the rows with writerows().

What is sqlite_master?

sqlite_master is a table that SQLite creates and maintains automatically. It stores details of the database schema, such as the type and name of each table and index. SELECT name FROM sqlite_master WHERE type='table' lists all the tables in the database.