How to Connect to Sqlite Database in Python 3

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

To connect 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.

Python sqlite3 module will look for the database you specify, if the database is 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" on the current working directory (Since we didn’t specify the absolute path) and connect. If the database "students.db" 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 the 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 does not exist.

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 sqlite cursor use to execute SQL commands.

import sqlite3

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

Now, we can execute 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 commit() function, before close 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 for loop to read data from sqlite3 table.

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

In this tutorial we learned how to connect sqlite3 Database in Python 3 using the sqlite3 module.

We also create tables and insert data using the sqlite cursor object.