[sqlite3] Using SQLite in IDEs

1. Using SQLite in Python

1.1 What is sqlite3 module? 

SQLite is a C library that provides a light weight disk-based database that doesn't require a separate server process and allows accessing file database using a nonstandard variant of the SQL query language.

 

The sqlite3 module provides an SQL interface compliant with the DB-API 2.0.

 

1.2 How to use sqlite3 module?

Step 1 : Import module

The sqlite module must be used to use the database table inside Python.

import sqlite3

 

Step 2 : Declare connection object

After importing the module, use the connect function to connect to the databse. The function receives a single parameter of the database to be linked. The connect function outputs a connection instance to maintain the connection of the database. Connection instance play a role in preventing database files from being accessed by others.

 

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

 

Step 3 : Declare cursor instance

In order to execute the query sentence, the query sentence is input as string data through the Cursor class. The Cursor class performs the following roles :

 

• Perform query statements in the database
• Separate results from the database
• Switches results from Python objects
• Store cursors instance results in variable

First, create a cursor instance connected to the database to use the query statement using the cursor method.

 

Step 4 : Write query to execute

Afterwards, write the query statement that you want to execute in the query variable and execute it by putting it in the exeute method.

 

query = "SELECT * FROM table ..." 
cursor.execute(query)

 

Step 5 : Get result using fetch method

There are fetchall, fetchone, and fetchmany methods as the method of outputting the results. The fetchall method stores all rows in tuple form. The fetchone method stores a single row in tuple form according to the number of inputs. fetchmany stores a plurality of rows in a tuple form.

 

results = cursor.fetchall()    # return all rows 
results = cursor.fetchone()    # return row one 
results = cursor.fetchone()    # return row two
results = cursor.fetchmany(n)  # return row three to n

 

Step 6 : Close connection object

Since the connection of sqlite3 is limited, it must terminate the connection of the database before other users can access it. This operation is carried out through the close method of the sqlite3 module.

 

conn = sqlite3.connect('db_name.db')
...
conn.close()

 

1.3 Usecase of sqlite3

import sqlite3

conn = sqlite3.connect('jobs2.db')
cursor = conn.cursor()

query = "SELECT Major FROM recent_grads ORDER BY Major DESC"
reverse_alphabetical = cursor.execute(query).fetchall()
print(reverse_alphabetical[:2])
conn.close()

 

2. Using SQLite in Shell

We can execute SQLite on shell using sqlite3 [db_name].db command. We can use dot commands for view options and using shell commands. Below is kinds of dot commands :

 

• .headers on : Show columns name
• .mode column : Return tidy table
• .help : Print all dot commands
• tables : Print all tables and views in database
• .shell [command] : We can execute shell commands
• .quit : quit SQLite Shell
• .schema [table_name] : Check schema of table

 

3. Table Normalization

The method of storing data in one table has the following vulnerabilities.

 

• Data Duplication
• Data Modification
• Data Integrity

 

To avoid this problem, we use a method called database normalization.

 

Data normalization is the process of structing data within minimal redundancy in the design of relational databases. The goal of database regularization is to create small, well-organized relationships by reconstructing abnormal relationships. Normalization generally involves dividing large, poorly organized tables and relationships into small, well-organized tables and relationships.

 

 

Source from :

• https://docs.python.org/3/library/sqlite3.html