SQLite - 3 - Creating a Database

Start MCQ

Creating a database in SQLite is a fundamental step in working with this lightweight, serverless database management system. In SQLite, a database is essentially a single file on your disk, and you can create a new database by following a few simple steps. This article will walk you through the process of creating a database in SQLite, explain the commands involved, and provide practical examples.

Creating a Database File:

In SQLite, databases are files with a .sqlite, .db, or any other extension you prefer. To create a new SQLite database file, you typically have two main options:

1. Using the Command-Line Interface (CLI):

You can use the sqlite3 command-line tool that comes with SQLite to create a new database. Here’s the basic syntax for creating a new database file:

sqlite3 mydatabase.db

In this example, mydatabase.db is the name of the new SQLite database file. If this file doesn’t exist, SQLite will create it for you. If it already exists, SQLite will open it for further operations.

2. Using an SQLite API in a Programming Language:

Another common way to create an SQLite database is by using an SQLite library or API in your preferred programming language. Popular programming languages like Python, Java, and C/C++ have SQLite bindings that allow you to create and manage databases programmatically.

Here’s an example in Python using the sqlite3 library:

import sqlite3

# Create a new SQLite database (or connect to an existing one)
conn = sqlite3.connect('mydatabase.db')
conn.close()  # Close the database connection when done

This Python code creates a new database file named mydatabase.db.

Creating Tables and Defining Schema:

After creating a database file, you’ll typically want to create tables within it to organize and store your data. SQLite uses SQL (Structured Query Language) for defining table schema and performing database operations.

Here’s an example of creating a simple table to store user information:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT UNIQUE
);

In this SQL statement:

CREATE TABLE is the SQL command for creating a new table called users.
id is defined as an INTEGER column, and it’s set as the primary key, ensuring each user has a unique identifier.
username is a TEXT column that cannot be NULL, meaning every user must have a username.
email is also a TEXT column, but it’s set as UNIQUE, ensuring that each email address is unique within the table.

Executing SQL Statements:

To execute SQL statements like the one shown above, you can use various methods depending on whether you’re using the command-line interface or a programming language.

Command-Line Interface (CLI):

If you’re using the sqlite3 command-line tool, you can enter SQL commands directly within the SQLite prompt after connecting to a database:

sqlite3 mydatabase.db

After connecting to the database, you can enter SQL commands line by line:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT UNIQUE
);

To exit the SQLite prompt, type .exit or press Ctrl+D (or Cmd+D on macOS).

Programming Language API:

When working with an SQLite library in a programming language, you can execute SQL statements using the library’s API. Here’s an example in Python:

import sqlite3

# Create a new SQLite database (or connect to an existing one)
conn = sqlite3.connect(‘mydatabase.db’)

# Create a cursor object to execute SQL statements
cursor = conn.cursor()

# Execute the CREATE TABLE statement
cursor.execute(”’
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL,
email TEXT UNIQUE
)
”’)

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

In this Python code:

connect establishes a connection to the SQLite database.
cursor is used to execute SQL statements.
execute runs the SQL statement to create the users table.
commit saves the changes to the database.
close closes the connection.

Verifying the Database:

To verify that you’ve successfully created the database and tables, you can use the .tables command in the SQLite CLI or equivalent functions provided by your programming language’s SQLite library.

In the SQLite CLI:

sqlite3 mydatabase.db .tables

In Python:

import sqlite3

# Create a new SQLite database (or connect to an existing one)
conn = sqlite3.connect('mydatabase.db')

# Create a cursor object to execute SQL statements
cursor = conn.cursor()

# List tables in the database
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables = cursor.fetchall()
print("Tables in the database:", [table[0] for table in tables])

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

These steps cover the basics of creating an SQLite database, defining table schema, and executing SQL statements to create tables. You can continue to work with the database by inserting data, querying data, and performing other database operations using SQL and the appropriate tools or libraries for your chosen programming language.

SQLite – 3 – Creating a Database