SQLite Python: Creating a New Database

Summary: In this tutorial, you will learn how to create a new SQLite database file from Python using the sqlite3 module.

Creating an SQLite database file from Python

To create an SQLite database, you follow these steps:

First, import the built-in sqlite3 module:

import sqlite3Code language: Python (python)

Second, call the connect() function from the sqlite3 module to create a new SQLite database:

conn = sqlite3.connect(database_file)Code language: Python (python)

The connect() function accepts the database_file argument that specifies the location of the SQLite database.

It returns an instance of the Connection object, allowing you to perform database operations.

Third, close the database connection once it is no longer in use by calling the close() method of the Connection object:

conn.close()Code language: Python (python)

In practice, you’ll use the with statement to automatically close the connection:

import sqlite3

with sqlite3.connect("my.db") as conn:
    # interact with database
    passCode language: Python (python)

To catch an error if it occurs during database creation, you can wrap the code inside a try...except statement as follows:

import sqlite3

try:
    with sqlite3.connect("my.db") as conn:
        # interact with database
        pass
except sqlite3.OperationalError as e:
    print("Failed to open database:", e)Code language: Python (python)

Program to create a new SQLite database

Step 1. Create a new directory to store Python code:

mkdir sqlite-python
cd sqlite-pythonCode language: Python (python)

Step 2. Open the project directory in your favorite code editor.

Step 3. Create a Python file called connect.py with the following code:

import sqlite3

try:
    with sqlite3.connect("my.db") as conn:
        print(f"Opened SQLite database with version {sqlite3.sqlite_version} successfully.")

except sqlite3.OperationalError as e:
    print("Failed to open database:", e)Code language: Python (python)

Step 4. Open your terminal and run the connect.py file:

python connect.pyCode language: Python (python)

The script should create a new SQLite database within your project directory and return the following output:

Opened SQLite database with version 3.45.3 successfully.Code language: Python (python)

Note that you may see a higher version depending on the Python version on your computer.

If the SQLite database file already exists, the connect() function opens a connection to the database instead of creating a new one.

Creating an SQLite database in the memory

When you pass the literal string ':memory:' to the connect() function of the sqlite3 module, it will create a new database that resides in the memory.

Please note that the in-memory SQLite database will last as long as the program ends.

For example, the following Python program creates an SQLite database in the memory:

import sqlite3

try:
    with sqlite3.connect(':memory:') as conn:
        # interact with database
        pass
except sqlite3.OperationalError as e:
    print("Failed to open database:", e)Code language: Python (python)

Summary

Use sqlite3’s connect() function to create a new SQLite database file on disk or in the memory.

Was this tutorial helpful ?