How to create SQLite database in Python

Creating an SQLite database in Python is relatively straightforward using the built-in sqlite3 module. Here’s a step-by-step guide on creating and interacting with an SQLite database.

Import the sqlite3 module.

Start by importing the sqlite3 module, which provides the necessary functions to work with SQLite databases.

import sqlite3

Connect to or Create a Database

You can connect to an existing database or create a new one if it doesn’t exist.

conn = sqlite3.connect('codetryout.db')

Create a Cursor

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

cursor = conn.cursor()

Execute SQL Commands

You can now execute SQL commands to create tables, insert data, retrieve data, etc. Let’s start by creating a simple table.

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

# Commit the changes
conn.commit()

Insert Data

You can insert data into the table using the INSERT command.

cursor.execute('INSERT INTO users (username, email) VALUES (?, ?)', ('Python', '[email protected]'))
conn.commit()

Retrieve Data

You can retrieve data using the SELECT command.

cursor.execute('SELECT * FROM users')
data = cursor.fetchall()

for row in data:
    print(row)

Close the Connection

Always remember to close the database connection when you’re done with it.

conn.close()

Remember that these are essential steps to start creating and interacting with an SQLite database in Python. You can build more complex database interactions by utilizing SQL commands and the functions provided by the sqlite3 module.

Complete Script

import sqlite3

# connect to or Create a Database
conn = sqlite3.connect('codetryout.db')
cursor = conn.cursor()

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

# Commit the changes
conn.commit()

#Insert Data
cursor.execute('INSERT INTO users (username, email) VALUES (?, ?)', ('Python', '[email protected]'))
conn.commit()

# Retrieve Data
cursor.execute('SELECT * FROM users')
data = cursor.fetchall()

for row in data:
    print(row)

# Close the Connection
conn.close()

This example code will create an SQLite Database file named codetryout.db on the location where you ran the script.

Python