SQL based database management system

Over the last two week or so I have been working on the following areas:

Training in the areas of (postgresql, Python with (tkinter and psycopy2) – python modules ) using UDEMY, I have become a massive fan of the UDEMY system as there is a mass of IT courses available in almost every possible subjects and I am currently registered on three courses that relate to all the above areas.

At the start of this week, I found myself in the position of being able to have a fully working SQL data table editor and the framework for a fully functioning GUI application completed as you can see from the images here.

During this period of my Database system project, I have been doing my best to crack some of the harder eggs, i.e. the areas of the project I had the biggest questions about and I feel that I have done well at defining these areas in the form of questions and then getting full answers for them, along with the needed skills.

These areas and related question are as follows: management

1.. The methods of connecting a python program to an SQL database, i.e. how do you link your python code to a Database Schema that you have created on a postgresQL server or any type of SQL server?

2.. The best way of using SQL statements within a python-3 program, in order to (create, update, read, write and delete ) row of data within an SQL table?

3.. How do I use the Python language to provide the best (GUI) user interface to the above SQL database and table connection?

4.. During any Python application development, how do I make sure that I code with a relational database model in mind?

Clearly, there are many other questions that have come up but the above questions are the key ones!

Questions 1 and 2

For questions 1 and 2, the answers relate to the python psycopg2 module, this module includes functions that both provide methods for making the TCPIP port connection to a network-enabled SQL server, it also provides all the needed SQL API calls to this server once the connection has been made.

DVDrental database

As a side note here, the website Postgresql Tutorials includes a sample database ‘Dvd-rental’, clearly based on the kind of SQL database that a DVD rental company would have used for its daily operations.

I have downloaded this and restored it to my PostresQL server, I will use this database for this project as it has a great model for a relational database system. I have two posts in mind over the next week one relating to the setup of a PostgresQL server and another one on the definition of a Relational database management system (DBMS).

Below is the Graphic of this Dvd-Rental SQL database.

As you can see there are many relationships between tables here, you simply follow the connection lines between tables to see the connections between them.

Personally, I think that there may be one too many tables, i.e. using a table to store all the addresses within the system and giving these addresses an index ID is a bit on the heavy side! It does match the concepts behind a relational database (no repeated data in any location!) but adds another issue, these being the ease at which these addresses can be found later! i.e. when you allocate a new (customer, store or staff member) an address you would have to enter it into the system in such a way that it is allocated an ID number and then stored into a separate table. I can get this for the store table but not for the staff table, finding an address id or adding a new one for each staff member address could turn out to be a pain, to be honest, you need a search routine to locate a matching existing address and the likely hood that a staff members home address will be duplicated that often is not high!

Space-saving of storage is one possible reason, this is most likely the reason its included, however, I think to act as a strong test to a programmer who is developing an application to work with this sample database is more likely!

Actually, that’s a great reason so I will run with it!

Question 3

Moving on to the best method of coding python using a GUI system, the python module Tkinter is one of the most widely used Python modules to present the user with a modern GUI system. It includes all the lastest GUI features, such as (menus, frames, labels, entry fields, drop-down selection boxes, scroll bars and buttons + much more).

So I have been training myself via two UDEMY courses, to use these modules features. You can see some of the results so far in the images above, I feel that I have a full grip now of how to produce a modern GUI application using Python.

Question 4

This question related to coding an application so that any data (created, entered, updated or deleted) fits the philosophy of a fully relational database.

The answer to this related to using the (Tkinter(python2)/tkinter(python3)) module to its best effect, i.e. when your entering or changing data in a relational system your not dealing with a single data table but with all the data that relates to the primary table that you are processing.

If you are editing the Staff table you also have to open links to other tables that are related such as (store table and the address table). So you need to have all these tables open and presented to the user screen during the editing process. Just showing the ID’s of the store and the address data will not be enough!

In data-base applications such as MS-access or Libreoffice-base this requirment is handled by including a sub-form in the main form, showing the data taken from the related sub-tables, in the relationship.

When coding in Python clearly this is handled by opening and displaying all the related tables and columns and then following any changes made, updating any rows that need to be updated in the primary tables with – ID codes for example.

Clearly, some of the applications modules will only need to edit and update a single table, such as when you enter all the details for each city in the system above, yet other editing and data enter modules may be capable of adding data into more that one table at a time, this is not advisable however for more than one reason.

…..

So that’s my updates for this post, I feel the project is going very well 🙂

As a footnote, I have also been using applications such as MS-Access and Libreoffice-base in conjunction with Python code to help me when I want to check my logic along with when I need to view/enter any test data for the python code.

I will post on how these applications fit within an SQL server environment as both can be used with local none SQL-server based tables. I will also post on the structures I have created for queries and forms as this will help me to get a full grasp of the relationships between all tables.

I have also look in-depth at an application called Kexi and I like it a great deal as it offers a great yet simple designed interface but it has one basic problem, it cannot use SQL tables that have been created outside of itself (e.g. in Pgadmin 3/4 ), you can use the tables created with it, in Python or application such as (Access etc..) but not the other way around, so I will not use it in this project, as clearly the DVD-rental database cannot be opened in Kexi – so that’s the end of that – for the moment!

PS… I am using Chrome these days with the Grammarly plugin for WordPress posts, I do my best to proof read these post but I am not an author writing a book, so I just hope as well as my spelling and grammar, my written logic here working for you !!! 🙂