DEV Community

Ashish Nair
Ashish Nair

Posted on


Sqlite 3 - Python Database

If you were using files to store the program data, here is a better way to store the data,

Using built-in Sqlite 3 database in Python.

So lets start with importing the the Sqlite 3 module in python,

import sqlite3

Next, lets connect the database with sqlite.

conn = sqlite3.connect('Client_data.db')
conn.execute('''CREATE TABLE Client_db
                (NAME            BLOB NOT NULL,
                PASSWORD         BLOB NOT NULL,
                EMAIL            VARCHAR(320) NOT NULL);''')

This creates a sqlite database 'Client_data.db', and creates a table named 'Client_db' with columns Name, password and email.

Adding values to the database

Now that we have created a sqlite database and a table consisting of columns, we can add values to the table.

We can do that like this,

cursor = conn.cursor()
params = (username,password,receiver_mail)
cursor.execute("INSERT INTO Client_db VALUES (?,?,?)",params)
print('User Creation Successful')

The '?' operator is used to pass the values of a variable, sort of like the 'f' string in Python.

Reading from the Sqlite 3 database

To read from the sqlite database there are two methods,

fetchone() : fetches the first row in the sqlite database that matches the passed in filters.

fetchall() : fetches all the rows in the sqlite database that match with the filter.

Take a look at the code,

conn = sqlite3.connect('Client_data.db')
cur = conn.cursor()
cur.execute("SELECT * FROM Client_db WHERE NAME =:NAME",{'NAME':username})
if cur.fetchone()[1] == password:
     print('LogIn Successful')    

This queries the row where the name matches the name passed in, and it fetches the password relating to that username using the fetchone() method from the sqlite database, and matches the password with the one passed into the program and if the passwords match it prints out 'LogIn Successful'.

You can find more on the Sqlite 3 Database in python here.

Peace !

Top comments (0)

Timeless DEV post...

Git Concepts I Wish I Knew Years Ago

The most used technology by developers is not Javascript.

It's not Python or HTML.

It hardly even gets mentioned in interviews or listed as a pre-requisite for jobs.

I'm talking about Git and version control of course.

One does not simply learn git