Python SQLite3 Usage Tutorial

SQLite3 is a lightweight database that is bundled with Python, making it an ideal choice for applications that require a simple and reliable database solution without the need for a full-fledged database server. In this tutorial, we will walk through the basics of using SQLite3 in Python, including how to create a database, execute queries, and handle data.
‌Setting Up SQLite3 in Python‌

To start using SQLite3 in Python, you don’t need to install any additional packages as it comes built-in with Python. You can import it directly into your Python script using:

pythonCopy Code
import sqlite3

‌Creating a Database and Connection‌

To create a new SQLite database or connect to an existing one, you use the connect() method. If the database does not exist, SQLite will automatically create it.

pythonCopy Code
conn = sqlite3.connect('example.db')

This creates a database named example.db if it doesn’t exist, and connects to it if it does.
‌Creating a Cursor Object‌

To execute SQL commands, you need a cursor object. You can create one using the connection object:

pythonCopy Code
cursor = conn.cursor()

‌Creating Tables‌

With the cursor object, you can execute SQL commands. For example, to create a table:

pythonCopy Code
cursor.execute('''CREATE TABLE IF NOT EXISTS inventory
               (item_id INTEGER PRIMARY KEY, item_name TEXT, quantity INTEGER)''')

This creates a table named inventory with three columns: item_id, item_name, and quantity.
‌Inserting Data‌

To insert data into the table, you can use the execute() method again:

pythonCopy Code
cursor.execute("INSERT INTO inventory (item_name, quantity) VALUES ('Apple', 100)")

After executing any INSERT, UPDATE, or DELETE commands, you need to commit the transaction:

pythonCopy Code
conn.commit()

‌Querying Data‌

To query data from the database, you can use the execute() method and then fetch the results:

pythonCopy Code
cursor.execute("SELECT * FROM inventory")
print(cursor.fetchall())

‌Closing the Connection‌

After completing your database operations, it’s important to close the cursor and connection:

pythonCopy Code
cursor.close()
conn.close()

‌Error Handling‌

It’s good practice to handle potential errors that might occur during database operations. You can use try-except blocks to catch and handle exceptions:

pythonCopy Code
try:
    # Database operations
except sqlite3.Error as e:
    print("An error occurred:", e)
finally:
    cursor.close()
    conn.close()

SQLite3 in Python provides a simple yet powerful way to interact with databases, making it suitable for a wide range of applications, from small personal projects to more complex systems where a full database server might be overkill.

[tags]
Python, SQLite3, database, tutorial, beginners, SQL, data handling