Updating the database entry

Now, the program we are making has so much code, that from now on the session does not start with a picture of the code. I will however explain it line by line, as before. and there will be a file you can download with the code in at the end of this session.

Mission:
In this session we're going to update the database. We have a database with one record (ID 1), Lorem ipsum is not really what we want there, now is it? We need to update the information there, hence we need to rewrite the program from just showing the record to invoking an input window with the text in it, so we can change it.

Importing:

import PySimpleGUI as sg
import mariadb
import sys
import re

In addition to PySimpleGUI, mariadb and sys, we also ned re to massage the text, so the database can receive it. It's not straight forward, for some of the characters are used by the database and/or the program to start and end statements and more, The plugin re helps with searching and replacing using regex search «language». I urge you to also look here for regex in python.

Regex is not a part of this session. It's quite advanced and quite hard to learn, but it is the best tool for matching text out there, so we'll touch into it from time to time. This is a part where you must search the net to find the explanation that suits you best. I do use regex, but so seldom that I forget everything between uses, so I must always use the reference sheet to do what I want.

Opening the database:

# Connect to MariaDB Platform
try:
    conn = mariadb.connect(
        user="root",
        password="",
        host="127.0.0.1",
        port=3306,
        database="testdb"

                          )
except mariadb.Error as e:
    print(f"Error connecting to MariaDB Platform: {e}")
    sys.exit(1)

This is not changed from the last session. You open the database the same way to write to it as to read from it.

Fetcing the record:

# Get Cursor
cur = conn.cursor()

cur.execute("SELECT Texts FROM testme WHERE ID=1 LIMIT 1")

We get the cursor and execute the query to fetch the record from the database to put in our editor window.

Making an odd empty variable:

# Empty variable, that needed to be defined in order to use it as an empty variable in an equation
TextsShow =  ""

If you are going to ad text to an equation to add text to already existing text, you end up with adding an empty variable first. This empty variable is OK, but you have to define it as a variable before you do that. Now that's handled!

Converting the array from the database to a string:

for (Texts) in cur:
  
    # Making it show correctly (with all the newlines when I read from the database.
    for  r in range(len(Texts)):
        TextsShow = TextsShow  + (Texts[(r)])  + '\n'

When getting the information from the database it comes as an array of lines. So this routine converts it to a single string, with every line ended with \n, the newline character. This way it'll come downwards as you wrote it.

This is also where the empty variable is used. First time with TextShow  = TextShow + (Texts[(r)])  '\n' the red TextShow would not be defined if I hadn't done so manually.
Let's have a look on this statement.

TextShow This is the variable we are filling with text, line by line.
TextShow This is best explained as the variable from last round, there to add another line.
(Texts[(r)]) This variable contains line «r» of the text, that's getting added to the string.
'\n'  The newline character gets added at the end of every line, empty ones too.

So now we have the information nicely put into a string with multiple lines, that the machine can read as such. smiley

Creating a column:

# Now, I just add the formatted variable to a column and put a Multiline there to contain the text
column1 =   [
                [sg.Multiline((f'{TextsShow}'), key='-editinput-', size=(50, 20), background_color='#ffffff', do_not_clear=True)],
            ]

Introducing the Multiline Element.
First here, we add the text we'd like to already be there when we start the program, so we insert the TextShow -string that we've just created.
Adding a key, makes it possible to use that key to reference this text later, as you will be shown further down in «Events».
The size is set to 50 columns by 20 lines.
Background color is set to white.
I have set do_not_clear to True, since I want the content to remain after «Update» is pressed.

Setting theme:

# Setting the theme for the program. Read more at https://www.nalle.no/node/34
sg.theme('Default1')   # Add a touch of color

This is already explained, as shown in comment.

Creating the layout:

# All the stuff inside your window.    
layout = [ 
            [sg.Column(column1)], # no need to set the column scrollable as Multiline is by deafult.
              [sg.Button('Update'),
              sg.Button('Quit')]
        ]

In the layout, we fist add column1 and then, the buttons.
Notice that the square brackets encloses both the buttons. That's to make them appear side by side in the window.

Window creation:

# Create the Window
# The window also get the icon I want. The file is found in the same directory as the application.
window = sg.Window('Edit text in DB', layout, icon='NSB-logo.ico', resizable=False, margins=(0, 0))

In the window I first write in what I want to have in the program line on the top of the window.
Then the layout is added.
In the same directory as I have the .py file for this app, i placed the NSB-logo.ico. I made that from a .png -file I had. It was done here. This place can also convert .jpg and .svg. Check the Convert at the menu on this page.
Yet, I do not want this application to be resizeable, so I set it to False. We'll make it resizable in a later session, but that involves a lot of running checks for window size and resetting the size of the Multiline and column - abit advanced so I've chosen to leave that for a later session.
As I want to let the Multiline to use as much of the window as possible I set the margins to 0, 0.

Events:

# While loop to process «events»
while True:
    event, values = window.read()
    if event == sg.WIN_CLOSED or event == 'Quit':  # if user closes window
        break
    if event == 'Update':
        # Getting the right text by «key»
        input_text = values['-editinput-']
        input_text = re.escape(input_text)
        input_text = input_text.replace("'", "''" ) # That tough ' character
       
        # Update the database with the new text
        query = f"UPDATE testme SET Texts = '{input_text}' WHERE ID = 1"
        cur.execute(query)
        #To commit the changes
        conn.commit() 

The events are twofold:

  1. Handle «Quit» and the red X at the top right.
  2. Handle «Update» and actually updating the database.

1.
We simply use an if -statement to say that if they are clicked, break (exit the routine) and that will invoke window.close()

2.
This part is divided into two parts. One to make the machine handle the characters you write correctly, and one to update the database.

2a.
input_text = values['-editinput-']
We are now using the key I made in the Multiline to get the text string and add it into a variable, input_text

input_text = re.escape(input_text)
If you use one of the characters that either python or MariaDB think of as reserved characters, it has to be «escaped» by adding a «\» in front of it.
Then the machine will understand that these are literal characters and will not be handled as end of record for instance.

input_text = input_text.replace("'", "''" ) # That tough ' character
For some reason, the «'» is handled not by adding a «\» to make it literal, but it wants a double character, like «''», so we replace it.
If we don't we'll not be able to run it. We'll just get an error message and the program will exit.

After doing these things, the database will store it correctly, so there's no need to remove «\»s and simplify «''»s. It's just to read it in as is.

2b.
query = f"UPDATE testme SET Texts = '{input_text}' WHERE ID = 1"
The updata query uses the input_text in curly brackets as string/text to update the database cell.

cur.execute(query)
After we have defined the query, we execute it.

#To commit the changes
conn.commit()
Lastly, we commit the changes and the database finally gets updated.

Now, you return to the program as it is - with the edited text still there. No need to ask the database. We could, but it takes time, and we'd like a as snappy program as possible!

Window close:

window.close()

And, we're done!

My window looks like this now, updated via the program:

If you got a window with these functions, you've done something right.

In the next session we will add a header column to the database, so we can add/select/delete a record. It's a biggie with some error checking and quite a few new tricks and techniques.

The Code File.
 

I wish you a better day then any you can remember that you have ever had!
 

« First -- Previous -- Next -- Last »