Python SQLite - Connecting to Database

An SQLite database can be connected to a Python program using the sqlite3.connect() method. It establishes a connection by opening the specified database file and creates the file if it does not already exist.

Establishing Database Connection

To interact with an SQLite database, we first need to establish a connection to it using the connect() method. If the specified database file doesn't exist, SQLite will create it automatically.

sqliteConnection = sqlite3.connect('database_name.db')

After establishing the connection, we need to create a cursor object to execute SQL queries on the database.

cursor = sqliteConnection.cursor()

SQL query to be executed can be written in form of a string, and then executed using the cursor.execute() method. and the results can be fetched from the server by using the fetchall() method.

Syntax:

query = 'SQL query;'
cursor.execute(query)
result = cursor.fetchall()
print('SQLite Version is {}'.format(result))

Example: Connecting to SQLite and Querying the Version

Below is an example that connects to an SQLite database, runs a simple query to retrieve the version of SQLite, and handles any potential errors during the process.

Python

import sqlite3

try:
    # Connect to SQLite Database and create a cursor
    sqliteConnection = sqlite3.connect('sql.db')
    cursor = sqliteConnection.cursor()
    print('DB Init')

    # Execute a query to get the SQLite version
    query = 'SELECT sqlite_version();'
    cursor.execute(query)

    # Fetch and print the result
    result = cursor.fetchall()
    print('SQLite Version is {}'.format(result[0][0]))

    # Close the cursor after use
    cursor.close()

except sqlite3.Error as error:
    print('Error occurred -', error)

finally:
    # Ensure the database connection is closed
    if sqliteConnection:
        sqliteConnection.close()
        print('SQLite Connection closed')

Output

Explanation:

sqlite3.connect('sql.db') establishes a connection to the SQLite database named sql.db. If the database doesn't exist, it will be created automatically.
cursor = sqliteConnection.cursor() creates a cursor object, which is used to interact with the database and execute SQL queries.
query SELECT sqlite_version(): fetches the SQLite version being used.
cursor.execute() method runs the query, and cursor.fetchall() retrieves the result.
except sqlite3.Error as error: block catches SQLite-related errors (e.g., connection or query issues).
error variable shows the specific problem, allowing the program to handle it without crashing.

Python SQLite - Connecting to Database

Establishing Database Connection

Syntax:

Example: Connecting to SQLite and Querying the Version

Explore