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);''')
conn.commit()
conn.close()

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)
conn.commit()
print('User Creation Successful')
conn.close()

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 !