Python SQLite – Connecting to Database

To connect an SQLite Database in Python, use the “sqlite3.connect()” method. It provides an API through which you can create the database. It is compliant with Python Database API. Therefore, it does not require any external libraries.

Here are the internal workings of the sqlite3 module, which is used to interact with SQLite databases.

Understanding of how the sqlite3 module works

Here is the step-by-step guide to connecting Python application to the sqlite3 database:

Step 1: Import the sqlite3 package

The first step is to import the sqlite3 package.

import sqlite3

It provides an API that will be needed to create a database.

Step 2: Use the connect() function

Use the connect() function of sqlite3 to create a database. It will create a connection object.

connection = sqlite3.connect('shows.db')

Our database name is “shows.db”. We saved the connection to the connection object.

Next time we run this app.py file, it just connects to the database; if it is not there, it will create one.

Step 3: Create a database table

To create a table in the relation database, we need to use the cursor object. 

To create a cursor object, use a connection.cursor() method.

cursor = connection.cursor()

With this cursor object, we can now execute the commands and queries on the database.

Our first command is to create a Shows table.

Use the cursor.execute() method to write the CREATE TABLE query within triple commas.

cursor.execute('''CREATE TABLE IF NOT EXISTS Shows
       (Title TEXT, Director TEXT, Year INT)''')

We have written the command to create the table with its column names and data types in this code.

Step 4: Commit these changes to the database.

To commit the changes in the database, use a connection.commit() method.

connection.commit()

Step 5: Close the connection.

The last step is to close the connection using the connection.close() function.

connection.close()

Here is the complete code:

import sqlite3

connection = sqlite3.connect('shows.db')
cursor = connection.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS Shows
       (Title TEXT, Director TEXT, Year INT)''')

connection.commit()
connection.close()

Now the only step remaining is to run this app.py file. Type the following command in your terminal.

python3 app.py

After running the file, you will see that in your current project directory, the new file shows.db is created like this in the screenshot below:

Newly created shows.db file

That’s all!

1 thought on “Python SQLite – Connecting to Database”

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.