Sqlite3 and Python – How to … Database – Part 1

Another series of tutorial about sqlite3 with Python. Let’s take a look at this new prject.

Databases are really important, useful and make your work much easier, so it is very important to understand how to use them. The little effort that will take to learn to use them will be rewarned.

This time we are going to:

• create a database
• create a table
• write some data
• retrieve the data

app.table(“tablename”)	create a table
app.add_row(“tablename”, data) *data is a tuple with all the data	insert data in a row

app.query()	Look for data

Create a database

    def open(self):
        self.conn = sqlite3.connect("database.db")
        self.c = self.conn.cursor()
        print(self.conn)

This code will create a database or open an existing one, if it was created yet.

The cursor object is needed before the execute command.

Create a table

This command is dome with the table method.

        self.c.execute(f'''CREATE TABLE {name}(
                     date text,
                     account text,
                     debit real,
                     credit real,
                     diff real
        )''')

Insert data in a table

This command is executed with the add_row method

self.c.execute(f"INSERT INTO {tablename} VALUES {data}")

Retrieve data

Use the query method for this.

            for row in self.c.execute(f'SELECT * FROM {tablename} ORDER BY {column}'):
                print(row)

import sqlite3
import os

class Database:
    def open(self):
        self.conn = sqlite3.connect("database.db")
        self.c = self.conn.cursor()
        print(self.conn)

    def close(self):
        self.conn.close()

    def table(self, name):
        "Create a table"
        self.c.execute(f'''CREATE TABLE {name}(
                     date text,
                     account text,
                     debit real,
                     credit real,
                     diff real
        )''')

    def add_row(self, tablename, data):
        " Insert a row of data"
        self.c.execute(f"INSERT INTO {tablename} VALUES {data}")

    def query(self, tablename, column=""):
        print(column)
        if column == "":
            for row in self.c.execute(f'SELECT * FROM {tablename}'):
                print(row)
        else:
            for row in self.c.execute(f'SELECT * FROM {tablename} ORDER BY {column}'):
                print(row)

    def commit(self):
        self.conn.commit()



app = Database()
app.open()

def create_table():
    app.table("journal")

def day_1():
    "2021.02.01"
    app.add_row("journal", ('2021-02-01','Cash',100,0,100))
    app.add_row("journal", ('2021-02-01','Cash',200,0,200))
    app.add_row("journal", ('2021-02-01','Cash',0,50,-50))
    app.commit()

# day_1()
app.query("journal", "diff")
app.close()


# os.startfile(".")

<sqlite3.Connection object at 0x00000257238375D0>
diff
('2021-02-01', 'Cash', 0.0, 50.0, -50.0)
('2021-02-01', 'Cash', 100.0, 0.0, 100.0)
('2021-02-01', 'Cash', 200.0, 0.0, 200.0)
>>> 

The one above is the output of the code

• first I create an istance of the Database class that contains the methods to do everything (app = Database)
• then I open / create a database with the open method (app.open())
• then I create a table with the table method
• then I insert 3 rows of data with the row method
• then I commit the changes, otherwise they won’t be saved
• then I make a search into the journal table for data ordered by the values in diff column
• finally I close the database

If you look into the methods, you will see how I made it work with sqlite3