Python SQLite - Connecting to Database

In this article, we'll discuss how to connect to an SQLite database in Python using the sqlite3 module, perform basic operations, and handle errors effectively.

Connecting to the Database

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()

The SQL query to be executed can be written in form of a string, and then executed using the cursor.execute() method.

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.

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.
• try-except block ensures that any errors encountered during the connection or query execution are handled gracefully.