Photo by Jan Antonin Kolar on Unsplash

Using SQLite with Python (Part 1)

In this article, we are going to see how we can connect SQLite with python, create a table in our SQLite database, and also how we can view the data inside our SQLite database DB Browser for SQLite.

Creating a connection and table

Let’s first create .py file and begin with importing SQLite.

from sqlite3 import connect

We don’t need to install sqlite3 here since python provides us with sqlite3 package.
Now, let’s try to use connect.

def main():
   conn = connect('mydb.sqlite3') 
   session = conn.cursor() 

Inside our main function, we will create a file named mydb.sqlite3 . This file will contain all the data present in our database.
In order to execute any SQL commands, we need a session, hence we are creating a session using conn.cursor() and storing it in a variable.

Now, let’s write a simple SQL command to create a table inside our main()

sql_cmd = '''
  create table contacts(
  id integer primary key autoincrement,
  name varchar(50) not null,
  email varchar(50) not null,
  phone varchar(10) unique,
  city varchar(20) not null default 'Bangalore'
  )
'''

Now, to execute this command, we will make use of the session variable.

try:
  session.execute(sql_cmd)
  print('Table created')
except Exception as ex:
  print('There was an exception:', ex)

and finally, let’s call our main()

if __name__ == "__main__":
  main()

Now, if we execute the file, we can see our Table created print statement.
Also, a file named mydb.sqlite3 will be generated. If you try to view that file, you won’t be able to see our table since it’s a binary file.

Viewing tables and content

To view our content inside our mydb.sqlite3 file, we need to download an application named DB Browser for SQLite. Download the application as per your OS.
Once the downloading is complete, install the application and open it.
You should see a window like this.

DB browser for SQLite

Click on Browse Data tab on the top and select our mydb.sqlite3 file.

sqlite data table

You can see that our contacts table is successfully created and also the columns are added to it.

In Part 2 of this post, we will be performing CRUD operations on our table i.e we will see how we can add, fetch, update, delete data from our contacts table.