Getting Started With SQLite Database in Python

In this article I'll walk you through the process of using SQLite database, which is an in-built database for Python. We will start by creating an empty database, we will create an empty table, we will insert some data into it and then finally we will try to retrieve our data back.

Let's start by importing some required packages

Import Required Packages

       
            import sqlite3 as sql      
 

Create Database

We will start by creating an empty database named Sample.db using below line of code:

       
          connection = sql.connect("SampleDB.db")       
 

Above statement will create the database if not already present and then connect it.

Create Table

Once the database is created, next thing we need to do is to create an empty table named Student, which will have 3 columns named Id, StudentName and City. Below is the query to create a table named Student:

       
          connection.execute("CREATE TABLE Student(Id INTEGER NOT NULL PRIMARY KEY, StudentName TEXT, City TEXT);")
 

Insert Data Into A Table

Our empty table is ready, let's insert some rows into it using below Insert statement:

       
           query = "INSERT INTO Student(Id,StudentName,City) VALUES(?,?,?)"
    	   inputs = [(1,"Shweta","Redmond"),(2,"Ad","Bellevue")]
           connection.executemany(query,inputs)       
 

Retrieve Data

We are done with data insertion, let's try to read the data back from out SQLite table:

       
            rows = connection.execute("SELECT * FROM Student")       
 

Complete Code

       
           connection = sql.connect("SampleDB.db")
           with connection:
              connection.execute("CREATE TABLE Student(Id INTEGER NOT NULL PRIMARY KEY, StudentName TEXT, City TEXT);")
              query = "INSERT INTO Student(Id,StudentName,City) VALUES(?,?,?)"
              inputs = [(1,"Shweta","Redmond"),(2,"Ad","Bellevue")]
              connection.executemany(query,inputs)
              rows = connection.execute("SELECT * FROM Student")
              for row in rows:
                  print(row)       
 

If you are using Visual Studio Code, then you will see the output as shown below:

Hope you enjoyed creating your first database :)