All Courses

Python SQLite - Establishing Connection

Neha Kumawat

9 months ago

SQLite Establishing connection | insideaiml
Table of Contents
  • Introduction
  • Steps for Connect to SQLite Database in Python
  • Creating a New Database
  • Summary

Introduction

          SQLite is an integrated relational database management system. It is a stand-alone, serverless, configuration-less, transactional SQL database engine. SQLite is free to use for commercial or private purposes.
In other words, "SQLite is an open source, zero-configuration, stand-alone, stand-alone transaction relational database engine designed to be integrated into an application.
SQLite is different from other SQL databases because, unlike Most other SQL databases, SQLite does not have a separate server process. Reads and writes directly to normal disk files. A complete SQL database with multiple tables, indexes, triggers and views is contained in one file disk.

Steps for Connect to SQLite Database in Python

  • Import sqlite3 module : import sqlite3 statement imports the sqlite3 module in the program. Using the classes and methods defined in the sqlite3 module we can communicate with the SQLite database.
  • Use the connect() method : Use the connect() method of the connector class with the database name. To establish a connection to SQLite, you need to pass the database name you want to connect. If you specify the database file name that already presents on the disk, it will connect to it. But if your specified SQLite database file doesn’t exist, SQLite creates a new database for you.This method returns the SQLite Connection Object if the connection is successful.
  • Use the cursor() method : Use the cursor() method of a connection class to create a cursor object to execute SQLite command/queries from Python.
  • Use the execute() method : The execute() methods run the SQL query and return the result.
  • Extract result using fetchall() : Use cursor.fetchall() or fetchone() or fetchmany() to read query result.
  • Close cursor and connection objects : use cursor.clsoe() and connection.clsoe() method to close the cursor and SQLite connections after your work completes
  • Catch database exception if any that may occur during this connection process.
Example
import sqlite3

try:
    con = sqlite3.connect('de.file.db')
    cursor = con.cursor()
    print("Database created and Successfully Connected to SQLite")

    Query = "select sqlite_version();"
    cursor.execute(Query)
    record = cursor.fetchall()
    print("SQLite Database Version is: ", record)
    cursor.close()

except sqlite3.Error as error:
    print("Error while connecting to sqlite", error)
finally:
    if con:
        con.close()
        print("The SQLite connection is closed")
Output
Database created and Successfully Connected to SQLite
SQLite Database Version is:  [('3.22.0',)]
The SQLite connection is closed

Creating a New Database

          When you connect to a SQLite database file that does not exist, SQLite automatically creates the new database for you.
To create a database, you must first create a Connection object that represents the database using the connect() function of the sqlite3 module. 
import sqlite3

conn = None
try:
    conn = sqlite3.connect("db_file.db")
    print(sqlite3.version)
except Error as e:
    print(e)
finally:
        if conn:
            conn.close()
Output
2.6.0
The connect () function opens a connection to an SQLite database. Returns a Connection object representing the database. Using the Connection object, you can perform various database operations.  
If there is an error, we catch it inside the try except block and print the error message. With any luck, let's show the version of the SQLite database. It is good programming practice to always close the connection to the database when using it.  

Summary

          Use the connect() method to establish a connection to the database you want to connect to. If you connect to a database file that does not exist, it automatically creates a new database for you. Use the cursor() method and execute() methods to run the query and return the result. Use these methods to close the cursor and database connections after your work completes. Catch database exception if any that may occur during this connection process. Use connect()() to connect a database to an object representing the database.
    
Like the Blog, then Share it with your friends and colleagues to make this AI community stronger. 
To learn more about nuances of Artificial Intelligence, Python Programming, Deep Learning, Data Science and Machine Learning, visit our insideAIML blog page.
Keep Learning. Keep Growing.

Submit Review