Python create SQLite database

Working with SQLite databases using Python involves utilizing the sqlite3 module. This module provides a comprehensive interface for creating, accessing, and manipulating data in SQLite databases. Here’s a step-by-step guide on how to interact with an SQLite database using Python:

Import the sqlite3 module:

import sqlite3

Connect to the SQLite database or create a new one if it doesn’t exist:

# If the database doesn't exist, it will be created
conn = sqlite3.connect("your_database_name.db")

Replace “your_database_name.db” with the desired name for your database. This line of code establishes a connection to the database or creates it if it doesn’t exist.

Create a cursor object:

cursor = conn.cursor()

The cursor object is used to execute SQL commands and interact with the database.

Create tables and define their schema as needed:

# Example: Creating a 'users' table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        username TEXT NOT NULL,
        email TEXT NOT NULL
    )
""")

In this example, we create a table named ‘users’ with columns for ‘id’, ‘username’, and ’email’. Adjust the schema to fit your database structure.

Commit your changes and close the connection:

conn.commit()
conn.close()

It’s important to commit your changes to persist them in the database and then close the connection when you’re done working with it.

Here’s a complete example:

import sqlite3

# Connect to the SQLite database or create a new one
conn = sqlite3.connect("my_database.db")

# Create a cursor object
cursor = conn.cursor()

# Create a 'users' table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        username TEXT NOT NULL,
        email TEXT NOT NULL
    )
""")

# Commit changes and close the connection
conn.commit()
conn.close()

This code will create an SQLite database file named “my_database.db” and define a ‘users’ table within it. You can then perform various database operations, such as inserting, updating, or querying data, using the same sqlite3 library.

This provides a basic overview of working with SQLite databases using Python. The sqlite3 module offers various functions and techniques for more complex operations, such as creating indexes, managing transactions, and handling errors.