Module 10

Table of Contents

Database

10.1 SQLite

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language.

Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

SQLite is a database, which is zero-configured, which means like other databases you do not need to configure it in your system.

SQLite3

You do not need to install this module separately because it is shipped by default along with Python version 2.5.x onwards.

Create a Python file named database.py and write the following.

import sqlite3

conn = sqlite3.connect('student.db')

Running the above code should create a file student.db in the project folder.

Here, the connect() function is used to create a connection object that represents the database.

You can also supply database name as the special name :memory: to create a database in RAM.

import sqlite3

conn = sqlite3.connect(':memory:')

Optionally, you can create a cursor object, which will help you in executing all the SQL statements.

c = conn.cursor()

Note: Cursor objects allow you to keep track of which result set is which, since it’s possible to run multiple queries before you’re done fetching the results of the first. The examples to follow will make it clear.

Executing Queries

We use execute() function to execute an SQL statement. Let us run an SQL query to create a table.

c.execute("""CREATE TABLE student (
            id integer
            first_name text,
            last_name text,
            batch integer
    )""")

conn.commit()

conn.close()

Here:

• the statement passed as the argument of the execute() function is an SQL statement to create a table student.
• we must commit the changes we made by running conn.commit(). This action is necessary in execute so that the changes you made is persistent and is available in subsequent sessions.
• finally, you must close the database connection with conn.close().

Note: close() this does not automatically call commit(). If you just close your database connection without calling commit() first, your changes will be lost!

Storage Classes and Datatypes

In the above SQL statement integer and text are keywords denoting respective datatypes of the columns in the table.

Each value stored in an SQLite database has one of the following storage classes:

• NULL. The value is a NULL value.
• INTEGER. The value is a signed integer value.
• REAL. The value is a floating point value.
• TEXT. The value is a text string.
• BLOB. The value is a blob of data, stored exactly as it was input.

Create a table

We can create tables in the database by running the following program.

import sqlite3

conn = sqlite3.connect('student.db')

c = conn.cursor()

c.execute("""CREATE TABLE student (
            id integer,
            first_name text,
            last_name text,
            batch integer
    )""")

conn.commit()

conn.close()

INSERT Operation

We can insert values in the tables by running the following program. Comment the c.execute() function in the above example and add the following.

c.execute("""INSERT INTO student VALUES (
        001, 'John', 'Doe', 2021
    )""")
c.execute("""INSERT INTO student VALUES (
        002, 'Jane', 'Smith', 2020
    )""")

SELECT operation

We can fetch and display records from a table by running the following program. Comment the c.execute() function in the above example and add the following.

result = c.execute("""SELECT id, first_name, last_name, batch from student""")

for row in result:
   print ("ID = ", row[0])
   print ("NAME = {} {}".format(row[1], row[2]))
   print ("BATCH = ", row[3], "\n")

The execution of a SELECT operation returns a list of rows from the table. If the query returns 0 rows then ‘None’ is returned.

UPDATE operation

We can update a row in a table by running the following program.

c.execute("""UPDATE student SET batch = 2021 WHERE ID = 2""")
conn.commit()

print ("Total number of rows updated :", conn.total_changes)

result = c.execute("""SELECT id, first_name, last_name, batch from student""")

for row in result:
   print ("ID = ", row[0])
   print ("NAME = {} {}".format(row[1], row[2]))
   print ("BATCH = ", row[3], "\n")

Here, conn.total_changes returns the total number of database rows that have been modified, inserted, or deleted since the database connection was opened.

DELETE operation

We can delete a row in a table by running the following program.

c.execute("""DELETE FROM student WHERE ID = 2""")
conn.commit()

print ("Total number of rows updated :", conn.total_changes)

result = c.execute("""SELECT id, first_name, last_name, batch from student""")

for row in result:
   print ("ID = ", row[0])
   print ("NAME = {} {}".format(row[1], row[2]))
   print ("BATCH = ", row[3], "\n")

SQLite operations

Following are important sqlite3 module operations.

« Prev Next »