Python3 Programming - Exercise 25 - SQLite
Tags: python3, programming, beginners, tutorial
Published: 2021-04-13 12:03:23

## Python SQLite

In the previous exercise. `Exercise 24 (SQL)` we discussed SQL and used it to write to and read from the database. In this exercise, we shall make use of a built-in database know as `sqlite3`. Read more about [sqlite3][sqlite3-python-site].

## Create database and table with

We do believe [SQLite Browser][sqlitebrowser-site] has been installed. We shall create a database, `sample.db` and save it into a folder, we shall use, `Sample` as the folder name.

Create a table using this script.

```SQL
CREATE TABLE `profile` (
    `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `name` TEXT,
    `job` TEXT,
    `skill` TEXT,
    `salary` INTEGER
);
```

## Connect to the database

Before we use the `sqlite3` database, we must first `import` it, then connect to it.

```Python
import sqlite3

DATABASE_NAME = 'sample.db'

# create connection
connection = sqlite3.connect(DATABASE_NAME)

```

## Cursor Object

After we create the connection to the database, we then make use of its cursor object to read and write to the database.

```Python
# cursor object
cursor = connection.cursor()

```

## Execute

We pass an SQL query and some parameters to the `execute` method after we have created the `cursor` object.

### SQL query

For the SQL query, it is recommended to use placeholders instead of passing the actual values directly into the query.

```Python
# consider some arbitrary query

# don't do this
sql_query = "SELECT * some_tb WHERE `some_field` = 2"

# do this instead
sql_query = "SELECT * some_tb WHERE `some_field` = ?"

# the `?` is a placeholder

```

### Execute method

```Python
# profile -> id:int, name:str, job:str, skill:str, salary:int
# `id` is a primary key and auto increments so we shall ignore it

name = "John Doe"
job = "Software Engineer"
skill = "Python Developer"
salary = 3000

# writing and updating has the same effect of
# affectting some rows, else rowcount is -1
# reading rather returns an iterable (a row - tuple)
sql_query = "INSERT INTO `profile` ( `name` , `job` , `skill` , `salary` ) VALUES(?, ?, ?, ?)"

# the second argument is of the form, *parameters,
# remember `*arg`
# there would be a change in the database, so get 
# the number of affected rows with `rowcount` attribute
num_affected_row = cursor.execute(sql_query, name, job, skill, salary).rowcount

# do something if num_affected_row > 0

# consider select query
# for more than one row return, we can use fetchone() to 
# get one row and fetchall to return all rows
sql_query = "SELECT * FROM `profile` "
row_profiles = cursor.execute(sql_query).fetchall()

# do something with row_profiles

```

## Commit

Sure commit here sound familiar, from `exercise 23 (Git)`. Commit means save/write changes made to the database permanently. So after an `insert, update or delete` you have to commit.

```Python
connetion.commit()

```

## Close cursor and connection

After everything, we must close the cursor and close the database. This is done so that the database isn't blocked.

```Python
cursor.close()
connection.close()

```

## Full code

```Python
import sqlite3

DATABASE_NAME = 'sample.db'

# create connection
connection = sqlite3.connect(DATABASE_NAME)

# cursor object
cursor = connection.cursor()

# profile -> id:int, name:str, job:str, skill:str, salary:int
# `id` is a primary key and auto increments so we shall
# ignore it

name = "John Doe"
job = "Software Engineer"
skill = "Python Developer"
salary = 3000

# insert/write to database
sql_query = "INSERT INTO `profile` ( `name` , `job` , `skill` , `salary` ) VALUES(?, ?, ?, ?)"

# check if there is a change in the database
num_affected_row = cursor.execute(sql_query, name, job, skill, salary).rowcount

if num_affected_row:
    print("profile written to database successful")
else:
    print("profile writing to database unsuccessful")

# save the changes
connetion.commit()

# close cursor and connection
cursor.close()
connection.close()

```

## Reading

```Python
import sqlite3

DATABASE_NAME = 'sample.db'

# create connection
connection = sqlite3.connect(DATABASE_NAME)

# cursor object
cursor = connection.cursor()

# read data
sql_query = "SELECT * FROM `profile` "

# check if there is a change in the database
rows = cursor.execute(sql_query).fetchall()

# every row is like a tuple - integer indexed
# if rows will do, since an empty row is `False`
if rows > 0:
    for row in rows:
        id = row[0]
        name = row[1]
        job = row[3]
        skill = row[4]
        salary = row[5]

        print(
            f"ID: {id} - {name} is a(n) {job} specialized in {skill} and earns {salary}")
else:
    print("profile writing to database unsuccessful")

# there is no need to commit here because no changes are made to the database

# close cursor and connection
cursor.close()
connection.close()

```

## Practicals

> Use a class if possible

- Write a script that returns the number of characters in the entire file and the number of characters on each line. Save these two into a database with the name of the file.
- Write a script that returns the document statistics of a given file. The document statistics are the number of lines, number of words number of characters with space and without space.

  ```
  file name
  ---------
  Lines      - 8
  Words      - 71
  Char (ws)  - 403
  Char (wos) - 337
  ```

  Write these into a database

- Write a script that backs the content of a file up. Save the back up in the database.

## Summary

The concept or steps behind the use of `sqlite3` is quite simple.

- `sqlite3` is a built-in lightweight database
- `connect` to the database
- create a `cursor` object
- `execute` some queries
- `commit` the changes
- `close` cursor and connection

#

[sqlite3-python-site]: https://docs.python.org/3.7/library/sqlite3.html
[sqlitebrowser-site]: https://sqlitebrowser.org/dl/