SQLite3 Database
- What and Why SQLite3?
What is SQlite3 and Why we need to this, what are the benefits of using SQLite3.
- SQLite3 API's.
To communicate between Python and Database, we need to call predefined SQLite3 database API's, as per the operation requirement.
HOW to interface SQLite3 database with Python
- Importing of SQLite3
- To use SQLite3 database in Python, the first step is to import it to your current module.
- As mentioned under What and Why SQLite3 section, it is zero configurable, means its already comes within Pyhton
- No additional setup is required to use it.
# program to import SQLite3 database Module
case 1 : import sqlite3
case 2 : import sqlite3 as sql
Here in Case 1 : we will be using sqlite3 to create further connection with database.
Here in Case 2 ; we will be using sql (alial of sqlite3) to creatr further connection with database
***** ***** *****
- Creation of Connection Object
This step creates a connection with Database, which further allows us to do operation on SQLite3 database using SQLites3 API's
Syntax of connection API :
conn = sqlite3.connect(database name to create and get connection)
- The connect function will check for the data base in the path provided and returns connection object.
- If database not present, it will create a database and return connection.
- database can have extension as .db or .sqlite.
- It will not create database again if its already present, it will check presence and return connection object.
- Close will close the connection to database, it’s a good practice to close connection after use.
# program to import SQLite3 database and create database and database connection
import sqlite3 as sql
conn = sql.connect("mydatabase.db") # creating connection with database
print("successfully created connection with mydatabase.db at ", conn)
print("type(conn) : ", type(conn))
conn.close()
#Output
successfully created connection with mydatabase.db at <sqlite3.Connection object at 0x000001A4462A7730>
type(conn) : <class 'sqlite3.Connection'>
***** ***** *****
- Creation of Cursor Object
Cursor is required to call certain SQLite3 API's to perform specific operations
- Connection created in above section, will help you to get cursor object.
- Cursor object further helps you to do operation on database
- creating table in database
- entering new data in table inside database
- update old data in table inside database
- read present data in table inside database
- delete some data in table inside database
# program to import SQLite3 database and craete cursor object to database
import sqlite3 as sql
conn = sql.connect("mydatabase.db") # creating connection with database
print("successfully created connection with mydatabase.db at ", conn)
curs = conn.cursor() # created cursor object to database to perform certain operations
print("successfully created cursor with connection at ", curs)
print("type(curs) : ", type(curs))
conn.close()
#Output
successfully created connection with mydatabase.db at <sqlite3.Connection object at 0x0000015BFB7E7730>
successfully created cursor with connection at <sqlite3.Cursor object at 0x0000015BFB7AF810>
type(curs) : <class 'sqlite3.Cursor'>
NOTE : cursor is optional, not mandatory while creating table and inserting data, but mandatory for reading, updating and deleting data.
***** ***** *****
- Interaction with Database (CURD concept)
Lets learn how to access database to do following operations, for each operation we have a separate query to follow
***** ***** *****
- Query(s) in SQLite Database
To perform operation via API's on SQLite3 database, certain query are defined to follow for certain operation.
- Every Query has its own meaning, which helps DATABASE to understand the operation needs to be executed.
- Wrong Qurey script will throw error and will not allow that operation to perform on database.
To view database file we need database Browser, Please follow the links to download the DB_Browser
Windows Linux MacOS
