e Learning

How to Connect to SQLite Database in Python 3

In this tutorial, we are going to learn how to connect to a sqlite3 database in python 3 programming.

connect sqlite database in python 3

To connect an SQLite database, first, we need to import the sqlite3 module. Then, we create the connection to the SQLite database file using sqlite3.connect() function. Finally, once we are done, we should close the connection using sqlite3.close() function.

The Python sqlite3 module will look for the database you specify. If the database does not exist, Python will create a new database.

Example 1

import sqlite3

connection = sqlite3.connect('students.db')
connection.close()

As per the above example, python will look for the database file “students.db” in the current working directory (since we didn’t specify the absolute path) and connect. If the database “students.db” does not exist, then python will create the “students.db” in the current working directory.

Example 2

import sqlite3

connection = sqlite3.connect("/var/sqlite3/students.db")
connection.close()

Similar to Example 1, but this time we specified the absolute path to the database file. So python will look inside the /var/sqlite3 folder for the “students.db” which will be created if it does not exist.

If you are on Windows, put r before the path to avoid unicodeescape error, as shown in the following example:

import sqlite3


connection = sqlite3.connect(r"C:\Users\user1\CodeBase\python\sqlite\dbs\students.db")
connection.close()

Create SQLite Table in Python 3

To create SQLite tables, we need to execute SQL commands. To execute SQL commands, we need to add a cursor to our connection. In Python, the SQLite cursor is used to execute SQL commands.

import sqlite3

connection = sqlite3.connect("students.db")
cursor = connection.cursor()

connection.close()

Now, we can execute the create table statement using the cursor.

import sqlite3

connection = sqlite3.connect("students.db")
cursor = connection.cursor()

cursor.execute("CREATE TABLE student_details(id INTEGER, name TEXT)")

connection.close()

Insert Data to SQLite Table in Python 3

Same way using the python cursor, we can add data to the SQLite database table.

import sqlite3

connection = sqlite3.connect("students.db")
cursor = connection.cursor()

cursor.execute("INSERT INTO student_details(id, name) VALUES(1, 'student 1')")
cursor.execute("INSERT INTO student_details(id, name) VALUES(2, 'student 2')")

Once we insert the data, we need to save changes to the database file using the commit() function before closing the DB connection.

import sqlite3

connection = sqlite3.connect("students.db")
cursor = connection.cursor()

cursor.execute("INSERT INTO student_details(id, name) VALUES(1, 'student 1')")
cursor.execute("INSERT INTO student_details(id, name) VALUES(2, 'student 2')")

connection.commit()
connection.close()

Read Data from SQLite Table

In python 3, we can use a for loop to read data from sqlite3 tables.

import sqlite3

connection = sqlite3.connect("students.db")
cursor = connection.cursor()

select = "SELECT * FROM student_details"

for raw in cursor.execute(select):
    print(raw)

Above code will output:

(1, 'student 1')
(2, 'student 2')

Summary