Easy DB Help File

This is the Help "file" for the Python Application whose main page is: *Python Easy DataBase Project - OpenCircuits . You can also get a lot of information about how the program works by looking at the GUI and its explanation: Easy DB GUI Images.

Application Features

• Easy DB GUI Images.
• Supports comma separated values ( csv ) output of data to text files.
• Saves data to a sql lite database that you can use any way you wish.
• Highly configurable.
• Some of the terminology used here is covered in Basic Database Terminology

Download and Install

There is really no install program. Currently the application is intended for those who have at least a little familiarity with Python coding and can just insert the downloaded code into their development environment and run it. Code at Easy DB code For most of you it will not run due to dependency problems. You will need to add them. Pretty much read the error messages and install the missing code. I use Anaconda Spyder so my preferred tool is conda. If conda does not work or you do not use it use pip.

Much of the application is configurable through the parameter.py file, but the default may not give you an application that runs, including a default database.

How To:...

• Some stuff is pretty obvious from the GUI, take a look at it as well as reading the material here Easy DB GUI Images.
• Install: see section above.
• Debug: see section below.
• Edit the parameter file: *Change Parameters: Use the application button <Edit Parms> ( after configuring for your editor ) or any text editor suitable for Python ( no tabs ) on the file parameters.py. See also: The section below, Configuration Files For Python and SmartPlug GUI Images
• Edit the log file: This is a standard python logging file. Useful for debugging and general tracking of the application. You can set the level of the logging using the parameter file.
• Backup the database: Just make a copy of the database using your file manager. It is all in one file, but you can make multiple databases.

• Quick Restart of the application: As perhaps after a parameter change. Use the <Restart> button. See also: SmartPlug GUI Images

• Switch databases: Use browse control on GUI. Please do not change while db operations are active.

• Get Help: Press the <Help> button. Depending on how it is setup in parameters.py you may see this page.
• Work with text in message area: See buttons to the left of the area.

• Create a database?
• Create a table?
• Select some data from a table?
• Sort my data?
• Choose a data format for output
• Backup a database? Close the system, then use your file manager to make a copy of the database file.
• export data to a spreadsheet
• delete some data
• how do I know if there has been an error
• how to I find and fix errors
• How do I install the software.
• I created a new table and inserted data, but I would like to redo it to fix issues what should I do.
• how do I drop a table
• edit records
• How do i fix a field name if the system complains.
• How do I make up a valid column name.

Why

• Why are the spaces in my column names missing? Spaces in column names are usually not allowed in SQL so the system changes n consecutive spaces to an under score.
• What happened to the spaces at the beginning and end of my data? The system considers them mistakes, and removes them.
• My data contained a # and something odd happened.
• Why is there a column called ROW ID? It is built into sqllite, generally ignore it.
• Why are all of the column names in lower case? The system does not like upper case column names so they are automatically changed to lower case.
• What happens to blank input lines? They are ignored by the system.
• What happens to comment lines? They are ignored by the system.

What if

• I define a table that already exists ( option to ) drop and recreate.

How to Use the Parameter File

• For general information see: Configuration Files For Python.
• For detailed information read the file parameters.py.

You need a text editor suitable for .py files to manage the parameter file ( parameters.py ) This includes many text editors. I particularity like:

• notepad++
• geany

You can also use the editor that comes in many python development environments, the simplest of which may be Idle. But there are many many others. If you are reading this you probably have some experience.

Once you configure an editor in parameters.py you can edit from the <Edit Parms> in the GUI ( see below )

When editing there are couple of gotchas to watch out for.

• Python cares about capitalization, use the capitalization indicated in the default files and the example code.
• Python also cares a lot about how lines are indented. Do not change the indentation from the sample files, and always indent using spaces ( not tabs. most text editors will use spaces automatically for .py files, even if you use the tab key )

Editing of the parameters.py file

• Edit so the program finds your devices.
  • See above in Download and Install
• Enable your favorite editor to work with the application.
  • self.ex_editor = r"D:\apps\Notepad++\notepad++.exe" # use r" or the backslashes will not work, or you can use forward slashes instead they may be wrong but they work.
• Lots of other settings:
  • Read the comments in parameters.py and change as desired. If a setting does not work let me know.
• If you mess up.
  • Re download and get back to the original file.

A Scenario/Use Case

The Goal

Lets start a new database of dogs names, their breed, and typical weight, then run a report of all the names in alphabetic order, then a similar report to a comma separated file, and finally delete all the records where the dogs name is Spike.

Make a New Database

Now you need a database to keep this data ( and a place for the table named something like dogs ). You can use any database that is hanging around and does not already have a table called dogs ( use the Database File: <Browse...> button to choose one or make a new one.

Lets do it the hard way and make a new one. You could type in the name you want for the file but that can be tricky especially getting the path right, so lets pick one using the Database: file <Browse...> button, then edit the name. For example: I browse to:

    D:/Russ/0000/python00/python3/_projects/easy_db/people/people_database.db

which must exist ( since your browse to it ) and edit to

    D:/Russ/0000/python00/python3/_projects/easy_db/people/dog_database.db
       

note: the old file will be erased, lets warn the user and get approval ?? not sure we make it yet, we should the file must not already exist ( remember we are making a new db ) but the path to it must you may use your file manager to make new folders ( directories ) if you wish.

Prepare Some Data

Start the system, then press the button in the form area <Make Generic Input> this will make a generic input form, a template for defining a database table and its data. The system will use your systems text editor to open the file.

Save the file with a new name so it will not get overwritten the next time <Make Generic Input> is run.

Now we will edit the file for your new set of data. Note that the file is full of comments ( lines starting with # )

• The purpose of the file is still insert, so do not change that
• The name of the table should be changed to something like dogs ( generic_table_name => dogs )
• Now we come to the column or field names, change them for the dogs, something like:

       ( name_first:    => dog_name:
         name_middle:   => breed:
         name_last:     => typical_weight: )

• There are repeated records ( rows ) for this, get rid of the excess and make a bunch of sections so you have one section for each of the dogs you plan to enter. ( or make the sections as you go ).

• Enter your data, only one line per item of data.

• When you are done your file should look like the generic file, but now specialized for dogs.

• File it away ( remember its name )

Now for the input file <Browse...> to the file you just made.

       you can use the <Edit File> button to look at the file again if you wish.
       The press the <Define Table> button.  If all went well you should get confirming
       messages in the message area at the bottom of the window.
       

At this point you have a database, a table ( dogs ) but no data.

       Lets make sure the data file seems ok.  Press the <Check File> button.
       You should get messages saying the data is ok
       

Now lets add the data. Press <Insert File> button. Again messages should show that the data was inserted.

How can we check that the data made it into the table? Just select all the rows out again.

       Choose the output format: csv for comma separated values.
       Select the table from the drop down ( dogs ) Then press <Select All>
       Your text editor should open with the output.

In Depth

This revisits some material already covered but in more depth. Perhaps in a, or many, separate pages but here for now.

Table Creation

Table and Column Name Rules

Much the same rules apply to both table and column names:

• Use lower case.
• No spaces in names, do not start with a number, generally avoid non alphebetic characterss ( except _ )

• Names do not contain spaces tabs or weird characters

• the "_" character may be used in place of a space to make a name that would otherwise be two_words.
• Certain words are used by the sql language and are reserved for that, you cannot use them for table or column

names, some will be caught by the system ( more will be added as we trip across them ). They include: like, where, select delete.....

if you do not follow the guidelines then

• the system may change your name to meet the guidelines ( excess spaces removed, spaces changed to underscore, case shifted to lower case.... ,
• you may get a helpful error message
• you may get odd unexpected results perhaps with an unhelpful error message or none at all.

   it takes a common form for lines in the systems files. These are of the

Output/Reporting

Generally there are three aspects to output:

• What table and what columns from that table are used.
• What data is selected.
• What is the order of the data.
• What format is used for the data.

These are covered below.

Output Formats

When running a query ( you could call it a report ) there are a variety of formats to choose between ( not including which columns.... which is part of Output Content ). When you run a query the system tries to open the resulting file ( output is always to file, if you need printed output print the file ) in the approiapate viewer, often your text editor.

This list of formats may expand as I think of new options but as of now:

Table

Table is an ASCII sort of spreadsheet, each record is on a single line, | characters separate the columns. Here is an example for the dogs scenario:

#---------- file output from EasyDB Ver9:  2019_12_01.01
# sql =  SELECT ROWID, breed, dog_name, typical_weight FROM dogs  ORDER BY ROWID ASC
# select built from = 
purpose:export
use_table: dogs
ROWID | breed | dog_n | typic
------------------------
1     | Poodl | Spike | 25   
2     | Wolf  | Mike  | 80   
3     | Sprin | Penny | 33   
4     | Mut   | Woof  | 200  
:======== eof footer ============

This is a nice concise form of output as long as the output does not get wider than your printer or screen. If you really want the data in a spreadsheet then use CSV output which is easy to import into most spreadsheets.

Input

Input format makes the output look just like a cleaned up version of the input that would be necessary to input the data. One major difference is that the purpose is listed as export. This stops you from using it as input by mistake, you have to change the purpose to insert.

#---------- file output from EasyDB Ver9:  2019_12_01.01
# sql =  SELECT ROWID, breed, dog_name, typical_weight FROM dogs  ORDER BY ROWID ASC
purpose:export
use_table: dogs
:====================
ROWID:1
breed:Poodle
dog_name:Spike
typical_weight:25
:====================
ROWID:2
breed:Wolf
dog_name:Mike
typical_weight:80
:====================
ROWID:3
breed:Springer
dog_name:Penny
typical_weight:33
:====================
ROWID:4
breed:Mut
dog_name:Woof
typical_weight:200
:==================== eof footer ============

PyLog

Here all the output is sent to the Python log file. The data will be mixed in with other logged information.

 2019-12-02 20:36:05,467 - App - DEBUG - sql =    SELECT ROWID, breed, dog_name, typical_weight FROM dogs  ORDER BY ROWID ASC
2019-12-02 20:36:05,468 - App - DEBUG - write_header()  self.col_names  ['ROWID', 'breed', 'dog_name', 'typical_weight']
2019-12-02 20:36:05,468 - App - Notice - #---------- SelectLogWriter output from EasyDB Ver9:  2019_12_01.01
 self.table_info.sql =  SELECT ROWID, breed, dog_name, typical_weight FROM dogs  ORDER BY ROWID ASC
use_table:dogs
2019-12-02 20:36:05,469 - App - Notice - ============--------RowObject--------=====================
RowObject __str__()
edit_dict: {'ROWID': [1, None], 'breed': ['Poodle', None], 'dog_name': ['Spike', None], 'typical_weight': ['25', None]}
2019-12-02 20:36:05,469 - App - Notice - ============--------RowObject--------=====================
RowObject __str__()
edit_dict: {'ROWID': [2, None], 'breed': ['Wolf', None], 'dog_name': ['Mike', None], 'typical_weight': ['80', None]}
2019-12-02 20:36:05,469 - App - Notice - ============--------RowObject--------=====================
RowObject __str__()
edit_dict: {'ROWID': [3, None], 'breed': ['Springer', None], 'dog_name': ['Penny', None], 'typical_weight': ['33', None]}
2019-12-02 20:36:05,469 - App - Notice - ============--------RowObject--------=====================
RowObject __str__()
edit_dict: {'ROWID': [4, None], 'breed': ['Mut', None], 'dog_name': ['Woof', None], 'typical_weight': ['200', None]}
2019-12-02 20:36:05,469 - App - Notice - None
2019-12-02 20:36:05,469 - App - DEBUG - change_table_name =>>dogs<<
2019-12-02 20:36:05,473 - App - DEBUG - change_last_output_file_name easy_db.py_log
2019-12-02 20:36:05,487 - App - DEBUG - <<<<< Select complete <<<<<

CSV

ROWID	breed	dog_name	typical_weight
1	Poodle	Spike	25
2	Wolf	Mike	80
3	Springer	Penny	33
4	Mut	Woof	200

HTML

An HTML version of a table, much nicer to view than the ASCII version.

The GUI

see: Easy DB GUI Images

Debugging

There are several application outputs that may be useful for debugging.

• Watch the GUI
• Check the Python console.
• Look at the python log file ( use the GUI button <Edit Log> ( specify your editor in parameters.py first ) or use your editor on the default name of the log file ...\easy_db.py_log.

Most issues will probably be missing libraries, parameters.py issues, or just bugs in my code ( email me ).

Basic Database Terminology

Links

• Python Easy DataBase Project main page for this project
• see categories below as well