Tuesday, February 19, 2019

Keys and constraints

Constraints are the rules enforced on a data columns on table which are used to limit the type of data that can go into a table. Constraints ensures the accuracy and reliability of the data in the database.

Constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table.

Following are commonly used constraints available in SQLite.

NOT NULL Constraint − Ensures that a column cannot have NULL value.

DEFAULT Constraint − Provides a default value for a column when none is specified.

UNIQUE Constraint − Ensures that all values in a column are different.

PRIMARY Key − Uniquely identifies each row/record in a database table.

CHECK Constraint − Ensures that all values in a column satisfies certain conditions.

1. NOT NULL Constraint

By default, a column can hold NULL values. If you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL is now not allowed for that column.

A NULL is not the same as no data, rather, it represents unknown data.

Example

For example, the following SQLite statement creates a new table called EMPLOYEE and adds five columns, three of which, ID and NAME and AGE, specifies not to accept NULLs.

CREATE TABLE EMPLOYEE (
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

2. DEFAULT Constraint

The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value.

Example

For example, the following SQLite statement creates a new table called EMPLOYEE  and adds five columns. Here, SALARY column is set to 8000.00 by default, thus in case INSERT INTO statement does not provide a value for this column, then by default, this column would be set to 8000.00.

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 8000.00
);

3. UNIQUE Constraint

The UNIQUE Constraint prevents two records from having identical values in a particular column. In the COMPANY table, for example, you might want to prevent two or more people from having an identical age.

Example

For example, the following SQLite statement creates a new table called EMPLOYEE and adds five columns. Here, AGE column is set to UNIQUE, so that you cannot have two records with the same age −

CREATE TABLE EMPLOYEE (
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 8000.00

);


4. CHECK Constraint

CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn't entered into the table.

Example

For example, the following SQLite creates a new table called EMPLOYEE and adds five columns. Here, we add a CHECK with SALARY column, so that you cannot have any SALARY Zero.

CREATE TABLE EMPLOYEE (
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);



5. PRIMARY KEY Constraint

A primary key is a column or group of columns used to identify the uniqueness of rows in a table. Each table has one and only one primary key.

SQLite allows you to define primary key in two ways:

First, if the primary key consists of one column, you use the PRIMARY KEY column constraint to define the primary key as follows:

CREATE TABLE table_name(
   column_1 NOT NULL INTEGER PRIMARY KEY,
   ...
);


Second, in case primary key consists of more than 2 columns, you use the PRIMARY KEY table constraint to define the primary as the following statement.

CREATE TABLE table_name(
   column_1 NOT NULL INTEGER,
   column_2 NOT NULL INTEGER,
   ...
   PRIMARY KEY(column_1,column_2,...)
);

In SQL-standard, the primary key column must not contain NULL values. It means that the primary key column has an implicit NOT NULL constraint.

The following statement creates a table named employees with the emp_id column as the primary key.

CREATE TABLE employees(
 emp_id INTEGER PRIMARY KEY,
 emp_name text NOT NULL
);

We can use this query in our program as shown below:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('CREATE TABLE employees( emp_id INTEGER PRIMARY KEY, emp_name text NOT NULL)')

con.commit()

Since  the primary key of the employees table consists of one column, we define the primary key using PRIMARY KEY column constraint.

We can use the PRIMARY KEY table constraint to define the primary key that consists of one column. It works just fine as the following statement:


CREATE TABLE employees(
emp_id integer,
name text NOT NULL,
PRIMARY KEY (emp_id)
);

For table whose primary key consists of more than one column, it is mandatory to use PRIMARY KEY table constraint to define the primary key. See the example below:


CREATE TABLE regional_employees (
 region_id integer NOT NULL,
 emp_id integer NOT NULL,
 PRIMARY KEY (region_id, emp_id),
 FOREIGN KEY (region_id) REFERENCES employees(region_id)
            ON DELETE CASCADE ON UPDATE NO ACTION,
 FOREIGN KEY (emp_id) REFERENCES regional_employees (emp_id)
            ON DELETE CASCADE ON UPDATE NO ACTION
);

A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.

If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).

Here I am ending today's discussion. In the next post we'll focus on some advanced topics. Till we meet next keep practicing and learning Python as Python is easy to learn!


Share:

Monday, February 18, 2019

Combine Tables using Joins (SQLite3)

When designing a database we usually divide data between two or more tables to avoid duplication of data. To access information from such a DB we need a mechanism to pull the information between the tables and combine it. This is done through a Join.

A join combines information from two or more tables to create a new set of records, each of which can contain some or all of the information in the tables involved.

We will create a database pop.db which will contain two tables PopByRegion and PopByCountry so that we can use joins to combine the information they contain. The following code creates the required DB and the tables:

import sqlite3 as dbapi

con = dbapi.connect('pop.db')
cur = con.cursor()

cur.execute('CREATE TABLE PopByRegion(Region TEXT, Population INEGER)')

cur.execute('INSERT INTO PopByRegion VALUES("Central Africa", 330993)')
cur.execute('INSERT INTO PopByRegion VALUES("Southeastern Africa", 743112)')
cur.execute('INSERT INTO PopByRegion VALUES("Northern Africa", 1037463)')
cur.execute('INSERT INTO PopByRegion VALUES("Southern Asia", 2051941)')
cur.execute('INSERT INTO PopByRegion VALUES("Asia Pacific", 785468)')
cur.execute('INSERT INTO PopByRegion VALUES("Middle East", 687630)')
cur.execute('INSERT INTO PopByRegion VALUES("Eastern Asia", 1362955)')
cur.execute('INSERT INTO PopByRegion VALUES("South America", 593121)')
cur.execute('INSERT INTO PopByRegion VALUES("Eastern Europe", 223427)')
cur.execute('INSERT INTO PopByRegion VALUES("North America", 661157)')
cur.execute('INSERT INTO PopByRegion VALUES("Western Europe", 387933)')
cur.execute('INSERT INTO PopByRegion VALUES("Japan", 100562)')

con.commit()

Inserting data one row at a time like this requires a lot of typing. It is simpler to make a list of tuples to be inserted and write a loop that inserts the values from these tuples one by one using the placeholder notation. We will use this approach to create and populate the PopByCountry DB as shown in the following code:

import sqlite3 as dbapi

con = dbapi.connect('pop.db')
cur = con.cursor()

cur.execute('CREATE TABLE PopByCountry(Region TEXT, Country TEXT, Population INEGER)')

countries = [("Eastern Asia", "China", 1285238), ("Eastern Asia", "DPR Korea", 24056), ("Eastern Asia", "Hong Kong (China)", 8764), ("Eastern Asia", "Mongolia", 3407), ("Eastern Asia", "Republic of Korea", 41491), ("Eastern Asia", "Taiwan", 1433), ("North America", "Bahamas", 368), ("North America", "Canada", 40876), ("North America", "Greenland", 43), ("North America", "Mexico", 126875), ("North America", "United States", 493038)]
             
for c in countries:
    cur.execute('INSERT INTO PopByCountry VALUES (?, ?, ?)', (c[0], c[1], c[2]))

con.commit()

To check the tables in the DB use the following query:

SELECT name FROM sqlite_master WHERE type = "table" and you should see the following result:

name
PopByRegion
PopByCountry


Now that we have two tables in our database, we can use joins to combine the information they contain. We’ll begin with inner joins, which involve the following:

1. Constructing the cross product of the tables
2. Discarding rows that do not meet the selection criteria
3. Selecting columns from the remaining rows

First, all combinations of all rows in the tables are combined, which makes the cross product. Second, the selection criteria specified by WHERE is applied, and rows that don’t match are removed. Finally, the selected columns are kept, and all others are discarded.

Now let us get the names of the region and countries with projected populations greater than one million by using the inner join. The following query will create the required inner join :

SELECT PopByRegion.Region, PopByCountry.Country
FROM PopByRegion INNER JOIN PopByCountry
WHERE (PopByRegion.Region = PopByCountry.Region)
AND (PopByRegion.Population > 1000000)

1. We construct the cross product of the tables and combine every row of PopByRegion with every row of PopByCountry.

2. We discard rows that do not meet the selection criteria. The join’s WHERE clause specifies two of these: the region taken from PopByRegion must be the same as the region taken from PopByCountry, and the region’s population must be greater than one million.

3. At last,  we select  the  region  and  country  names  from  the  rows  that  have survived.

Let's use this query in the program which will print the names of the region and countries with projected populations greater than one million. See the code below:

import sqlite3 as dbapi

con = dbapi.connect('pop.db')

cur = con.cursor()

cur.execute('SELECT PopByRegion.Region, PopByCountry.Country FROM PopByRegion INNER JOIN PopByCountry WHERE (PopByRegion.Region = PopByCountry.Region)AND (PopByRegion.Population > 1000000)')
print(cur.fetchall())


The output of the program is shown below:

[('Eastern Asia', 'China'), ('Eastern Asia', 'DPR Korea'), ('Eastern Asia', 'Hon
g Kong (China)'), ('Eastern Asia', 'Mongolia'), ('Eastern Asia', 'Republic of Ko
rea'), ('Eastern Asia', 'Taiwan')]
------------------
(program exited with code: 0)

Press any key to continue . . .

If the query results contain duplicate values we can use the DISTINCT clause in the query to remove the duplicates. See the following query which find the regions where one country accounts for more than 10 percent of the region’s overall population:

SELECT PopByRegion.Region
FROM PopByRegion INNER JOIN PopByCountry
WHERE (PopByRegion.Region = PopByCountry.Region)
AND ((PopByCountry.Population * 1.0) / PopByRegion.Population > 0.10)

Let's use this query in the program which will print the  regions where one country accounts for more than 10 percent of the region’s overall population:

import sqlite3 as dbapi

con = dbapi.connect('pop.db')

cur = con.cursor()

cur.execute('SELECT PopByRegion.Region FROM PopByRegion INNER JOIN PopByCountry WHERE (PopByRegion.Region = PopByCountry.Region) AND ((PopByCountry.Population * 1.0) / PopByRegion.Population > 0.10)')

print(cur.fetchall())

The output of the program is shown below:

[('Eastern Asia',), ('North America',), ('North America',)]

------------------
(program exited with code: 0)

Press any key to continue . . .

As we can see output contains a duplicate value North America thus we'll use DISTINCT clause in the query and execute it again as shown below:

SELECT DISTINCT PopByRegion.Region
FROM PopByRegion INNER JOIN PopByCountry
WHERE (PopByRegion.Region = PopByCountry.Region)
AND ((PopByCountry.Population * 1.0) / PopByRegion.Population > 0.10)

We use multiplication and division in our WHERE condition to calculate the percentage of the region’s population by country as a floating-point number.Let's use this query in the program which will print the  regions where one country accounts for more than 10 percent of the region’s overall population:

import sqlite3 as dbapi

con = dbapi.connect('pop.db')

cur = con.cursor()

cur.execute('SELECT DISTINCT PopByRegion.Region FROM PopByRegion INNER JOIN PopByCountry WHERE (PopByRegion.Region = PopByCountry.Region) AND ((PopByCountry.Population * 1.0) / PopByRegion.Population > 0.10)')

print(cur.fetchall())

The output of the program is shown below which doesn't contain any duplicates:

[('Eastern Asia',), ('North America',)]
------------------
(program exited with code: 0)

Press any key to continue . . .

Here I am ending today's discussion. In the next post we'll focus on Keys and Constraints. Till we meet next keep practicing and learning Python as Python is easy to learn!








Share:

Sunday, February 17, 2019

Updating and Deleting operations (SQLite3)

Changes in DB are inevitable as data changes from time to time thus there are provisions to update the DB. The DB update is done using the UPDATE command as in the following query which updates the total employees in North region from 180 to 230 :

UPDATE Sales SET Total_employees = 230 WHERE Region = "NORTH"

The following program does the required update:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('UPDATE Sales SET Total_employees = 230 WHERE Region = "NORTH"')

cur.execute('select * from sales')

In case we need to delete records from the DB we use the DELETE clause as shown below:

DELETE FROM Sales WHERE Region = "NORTH"

The following program does the required delete:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('DELETE FROM Sales WHERE Region = "NORTH"')

cur.execute('select * from sales')

print(cur.fetchall())

To insert the record in to the DB use the INSERT clause as shown in the following program which insert the North region and the total_employees in the Sales table:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('INSERT INTO Sales VALUES ("NORTH",180)')

cur.execute('select * from sales')

print(cur.fetchall())

To remove an entire table from the DB use the DROP command as shown below:

DROP TABLE TableName

Thus to delete the Sales table we can use the following query:

DROP TABLE Sales

The following program deletes the Sales table from employees DB:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('DROP TABLE Sales')

Here I am ending today's discussion. In the next post we'll focus on combining Tables using Joins. Till we meet next keep practicing and learning Python as Python is easy to learn!


Share:

Thursday, February 14, 2019

Retrieving Data from database (SQLite3)

In the previous post we learned how to create and populate database. We created employees DB and a Sales table. Now we will learn how to retrieve data from DB. Data retrieval is also done using SQL statements and the syntax is as follows:

SELECT << ColumnName >>,..... FROM << TableName >>

where the TableName  is the name of the table to get the data from and the column names specify which columns to get values from. Thus to retrieve the names of Regions from the sales table we can say:

SELECT Region FROM sales

This query can be used in our program with the execute() as shown below:

cur.execute('SELECT Region FROM sales ')

The following program prints the names of all regions in the Sales table:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

regions = cur.execute('SELECT Region FROM sales ')

for region in regions:

print(region)

The output of the program is shown below:

('EAST',)
('WEST',)
('NORTH',)
('SOUTH',)
------------------
(program exited with code: 0)

Press any key to continue . . .

Instead of the appraoch shown in the above program we use the cursor's fetchone() and fetchall() methods to retrieve data from the DB. The fetchone() method returns each record as a tuple whose elements are in the order specified in the query. If there are no more records fetchone() method returns None.

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('SELECT Region FROM sales ')

print(cur.fetchone())

The output of the program is shown below:

('EAST',)

------------------
(program exited with code: 0)

Press any key to continue . . .


The fetchall() method returns all the data produced by a query that has not yet been fetched as a list of tuples. If we use only fetchall() method in the above program the output will be as shown below:

[('EAST',), ('WEST',), ('NORTH',), ('SOUTH',)]

------------------
(program exited with code: 0)

Press any key to continue . . .

If we use both fetchone() and fetchall() methods in the above program the output will be as shown below:

('EAST',)
[('WEST',), ('NORTH',), ('SOUTH',)]

------------------
(program exited with code: 0)

Press any key to continue . . .

Once all of the data produced by the query has been fetched, any subsequent calls on fetchone() and fetchall() methods return None and the empty list, respectively.  See the following program:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('SELECT Region FROM sales ')

print(cur.fetchone())

print(cur.fetchall())

print(cur.fetchone())

print(cur.fetchall())

Since we use both fetchone() and fetchall() methods in the above program twice the output will be as shown below:

('EAST',)
[('WEST',), ('NORTH',), ('SOUTH',)]
None
[]

------------------
(program exited with code: 0)

Press any key to continue . . .

A database stores records in whatever order it thinks is most efficient. To put the data in a particular order, we can either sort the list returned by fetchall() method or get the database to do the sorting for us by adding an ORDER BY clause to the query. Thus our query to get the names of Region in a sorted order would become:

SELECT Region FROM sales ORDER BY Region

Let's use this in our program to print the names of regions in a sorted order. See the following code:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('SELECT Region FROM sales ORDER BY Region ')

print(cur.fetchall())


The output of the program is shown below:

[('EAST',), ('NORTH',), ('SOUTH',), ('WEST',)]

------------------
(program exited with code: 0)

Press any key to continue . . .

In our query we can use more than one columns to retrieve their values, thus to get the values in both the columns in the Sales table in a sorted order we can use:

SELECT Region,total_employees FROM sales ORDER BY Region

The following program fetches the records from both the columns in the sales table and sort then according to their Region names:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('SELECT Region,total_employees FROM sales ORDER BY Region ')

print(cur.fetchall())

The output of the program is shown below:

[('EAST', 150), ('NORTH', 180), ('SOUTH', 300), ('WEST', 200)]

------------------
(program exited with code: 0)

Press any key to continue . . .

By changing the column name after the phrase ORDER BY, we can change the way the database sorts. See the following program:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('SELECT Region,total_employees FROM sales ORDER BY total_employees ')

print(cur.fetchall())

Here we are sorting based on total_employees column. The output of the program is shown below:

[('EAST', 150), ('NORTH', 180), ('WEST', 200), ('SOUTH', 300)]

------------------
(program exited with code: 0)

Press any key to continue . . .

It is also possible to specify whether we want values sorted in ascending (ASC) or descending (DESC) order as per requirement. In the above program we modify our query as follows to get the results in the descending order:

SELECT Region,total_employees FROM sales ORDER BY total_employees DESC

The output of the program is shown below:

[('SOUTH', 300), ('WEST', 200), ('NORTH', 180), ('EAST', 150)]

------------------
(program exited with code: 0)

Press any key to continue . . .

In case the number of columns are less we can use column names to fetch the data but in case if the table as more columns we can use the following query to fetch data from all the columns of the table:

SELECT * FROM <<tableName>> 

Thus to fetch the complete data in the Sales table we can use the following query:

SELECT * FROM Sales

The following program prints the complete data in the Sales table:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('SELECT * FROM Sales')

print(cur.fetchall())

The output of the program is shown below:

[('EAST', 150), ('WEST', 200), ('NORTH', 180), ('SOUTH', 300)]

------------------
(program exited with code: 0)

Press any key to continue . . .

Conditional queries

Sometimes it is required to fetch some specific data from the DB for eg. we need to find those regions where number of employees are more than 150. For such these kind of data retrieval we can use the WHERE clause and specify conditions that the rows we want must satisfy. We can use the following relational operators with the WHERE:

=      Equal to
!=     Not equal to
>      Greater than
<       Less than
>=    Greater than or equal to
<=     Less than or equal to


We can also use the AND, OR, and NOT operators. See the following program which prints the regions where number of employees are more than 150:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('SELECT Region FROM Sales WHERE total_employees > 150')

print(cur.fetchall())

The output of the program is shown below:

[('WEST',), ('NORTH',), ('SOUTH',)]

------------------
(program exited with code: 0)

Press any key to continue . . .

Here I am ending today's discussion. In the next post we'll focus on updating and deleting data from the database. Till we meet next keep practicing and learning Python as Python is easy to learn!




















Share:

Wednesday, February 13, 2019

Creating and Populating a DB using Sqlite3

A database is usually stored in a file or in a collection of files. These files aren’t formatted as plain text—if you open them in an editor, they will look like garbage, and any changes you make will probably corrupt the data and make the database unusable. Instead you must interact with the database in one of two ways:

• By typing commands into a database GUI, just as you type commands into a  Python  interpreter. This is good for simple tasks but not for writing applications of your own.


• By writing programs in Python (or some other language). These programs import a library that knows how to work with the kind of database you are using and use that library to create tables, insert records, and fetch the data you want. Your code can then format the results in a web page, calculate statistics, or do whatever else you like.


Our required table should have one column that contains the names of regions and another that contains the populations of regions, so each row of the table  must represent a region and its population. Lets creating our table:

1. import sqlite3

The first statement import sqlite3 is telling Python that we want to use sqlite3 

2. Establish connection to the DB

We now make a connection to our database by calling the database module’s connect method.

con = sqlite3.connect('employees.db')

This method takes one string as a parameter, which identifies the database to connect to. Since SQLite stores each entire database in a single file on disk, this is just the path to the file. Since the database population.db doesn’t exist, it will be created.

3. Get a cursor

cur = con.cursor()

This keeps track of where we are in the database so that if several programs are accessing the database at the same time, the database can keep track of who is trying to do what.

4. Create the database table

Here the SQL comes into effect which will be used to create our table and store the employees data in to it. The syntax to create a table is as follows:

CREATE TABLE «TableName»(«ColumnName» «Type», ...)

The TableName value will the name for our table to be created, ColumnName value will be provided by us and the Type which represents the types of the data in each of the table’s columns, can be chosen from the types the database supports. Some of the supported types are:

NULL             NoneType      Means “know nothing about it”
INTEGER      int                   Integers
REAL             float                8-byte floating-point numbers
TEXT             str                   Strings of characters
BLOB             bytes              Binary data


Now let's create a table Sales which will store region names as strings in the Region column and the working employees as integers in the Total_Employees column. The SQL statement to create the table is as follows:

CREATE TABLE Sales (Region TEXT, Total_Employees INTEGER)

In our program we'll put this SQL statement in a string and pass it as an argument to a Python method that will execute the SQL command:

cur.execute('CREATE TABLE Sales (Region TEXT, Total_Employees INTEGER)')

When we call the execute(), it returns the cursor object that it was called on. Since cur refers to that same cursor object, we don’t need to do anything with the value returned by execute.

 5. Populate the created table


After we create the Sales table, our next task is to insert data (record) into it which is again done using the SQL. The syntax to insert record in to a table is:

INSERT INTO «TableName» VALUES(«Value», ...)

Thus to insert data into the Sales table we will use:

INSERT INTO Sales VALUES("EAST", 150) 

In our program we'll put this SQL statement in a string and pass it as an argument to a Python method that will execute the SQL command:

cur.execute('INSERT INTO Sales VALUES("EAST", 150) ')
cur.execute('INSERT INTO sales VALUES("WEST",200 )')
cur.execute('INSERT INTO sales VALUES("NORTH",180 )')
cur.execute('INSERT INTO sales VALUES("SOUTH",300 )')


The records are inserted one at a time using the above method. We can do this insertion directly into the DB using the SQLite Manager. The number and type of values in the INSERT statements matches
the number and type of columns in the database table. If we try to insert a value of a different type than the one declared for the column, the library will try to convert it, just as it converts the integer 9 to a floating-point number when we do 1.1 + 9. For example, if we insert the integer 16 into a TEXT column, it will automatically be converted to "16"; similarly, if we insert a string into an INTEGER column, it is parsed to see whether it represents a number. If so, the number is inserted.


Usually if the number of values being inserted doesn’t match the number of columns in the table, the database reports an error and the data is not inserted. But, if we try to insert a value that cannot be converted to the correct type, such as the string “string” into an INTEGER field, SQLite will actually do it (though other databases will not). 



6. Saving the changes

After the data is inserted into the DB or any changes are made to the DB we must save the changes using the commit() as shown below:

con.commit()

If we fail to commit then our changes are not actually stored and are not visible to anyone else who is using the database at the same time.

The complete program to create and populate the employees DB is shown below:


import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('CREATE TABLE Sales (Region TEXT, Total_Employees INTEGER)')

cur.execute('INSERT INTO sales VALUES("EAST",150 )')
cur.execute('INSERT INTO sales VALUES("WEST",200 )')
cur.execute('INSERT INTO sales VALUES("NORTH",180 )')
cur.execute('INSERT INTO sales VALUES("SOUTH",300 )')

con.commit()
 

Here I am ending today's discussion.In the next post we'll focus on retrieving data from the database. Till we meet next keep practicing and learning Python as Python is easy to learn!



Share:

Tuesday, February 12, 2019

tkinter example - 16 (Layout Managers / Geometry Manager)

The tkinter module possess the following three layout managers:
  1. pack
  2. grid
  3. place
They serve various functions such as:
  1. arrange widgets on the screen
  2. register widgets with the underlying windowing system
  3. manage the display of widgets on the screen

Arranging widgets on the screen includes determining the size and position of components. Widgets can provide size and alignment information to geometry managers, but the geometry managers has always the final say on the positioning and sizing. The three layout managers should never be mixed in the same master window.

The pack() method

Instead of having to declare precisely where a widget should appear on the display screen, we can declare the positions of widgets with the pack command relative to each other as it takes care of the details. Though the pack command is easier to use, this layout managers is limited in its possibilities compared to the grid and place mangers. For simple applications it is definitely the manager of choice.

See the example below:

from tkinter import *

root = Tk()
root.title('Using pack()')

Label(root, text="Red Sun", bg="red", fg="white").pack()
Label(root, text="Green Grass", bg="green", fg="black").pack()
Label(root, text="Blue Sky", bg="blue", fg="white").pack()

mainloop()

The output of the program is shown below:



In the above program we have packed three labels into the parent widget "root". We used the pack() without any options which decides in what way to arrange the labels. As we can see, it has chosen to place the label widgets on top of each other and center them with each label has been given the size of the text.

To make all the widgets look uniform we can use the fill = X option as shown in the program below:

from tkinter import *

root = Tk()
root.title('Using pack()')

w = Label(root, text="Red Sun", bg="red", fg="white")
w.pack(fill=X)
w = Label(root, text="Green Grass", bg="green", fg="black")
w.pack(fill=X)
w = Label(root, text="Blue Sky", bg="blue", fg="white")
w.pack(fill=X)

mainloop()

The output of the program is shown below:



The pack() manager has padding options which are:

1. external padding in y direction
2. external padding in x direction
3. internal padding in x direction
4. internal padding y direction

Lets implement these padding in the following programs:

1. external padding in y direction

The following program shows implementation of  external padding in y direction or Vertical external padding:

from tkinter import *

root = Tk()
root.title('Vertical external padding')

w = Label(root, text="Red Sun", bg="red", fg="white")
w.pack(fill=X,pady=10)
w = Label(root, text="Green Grass", bg="green", fg="black")
w.pack(fill=X,pady=10)
w = Label(root, text="Blue Sky", bg="blue", fg="white")
w.pack(fill=X,pady=10)

mainloop()

The output of the program is shown below:




2. external padding in x direction

The following program shows implementation of  external padding in x direction or Horizontal external padding:

from tkinter import *

root = Tk()

root.title('Horizontal external padding')

w = Label(root, text="Red Sun", bg="red", fg="white")
w.pack(fill=X,padx=10)
w = Label(root, text="Green Grass", bg="green", fg="black")
w.pack(fill=X,padx=10)
w = Label(root, text="Blue Sky", bg="blue", fg="white")
w.pack(fill=X,padx=10)
mainloop()

The output of the program is shown below:




3. internal padding in y direction

The following program shows implementation of  internal padding in y direction or Vertical internal padding:

from tkinter import *

root = Tk()
root.title('Vertical external padding')

w = Label(root, text="Red Sun", bg="red", fg="white")
w.pack()
w = Label(root, text="Green Grass", bg="green", fg="black")
w.pack(ipadx=10)
w = Label(root, text="Blue Sky", bg="blue", fg="white")
w.pack(ipady=10)
mainloop()

The output of the program is shown below:




4. internal padding in x direction

The following program shows implementation of  internal padding in x direction or Horizontal internal padding:

from tkinter import *

root = Tk()
root.title('Vertical external padding')

w = Label(root, text="Red Sun", bg="red", fg="white")
w.pack()
w = Label(root, text="Green Grass", bg="green", fg="black")
w.pack(ipadx=10)
w = Label(root, text="Blue Sky", bg="blue", fg="white")
w.pack()
mainloop()

The output of the program is shown below:


If we want to place the three label side by side now and shorten the text slightly we can do in the following way: 

from tkinter import *

root = Tk()
root.title('Side by side')

w = Label(root, text="red", bg="red", fg="white")
w.pack(padx=5, pady=10, side=LEFT)
w = Label(root, text="green", bg="green", fg="black")
w.pack(padx=5, pady=20, side=LEFT)
w = Label(root, text="blue", bg="blue", fg="white")
w.pack(padx=5, pady=20, side=LEFT)

mainloop()

The output of the program is shown below:



If we change LEFT to RIGHT in the previous example, we get the colors in reverse order: 

from tkinter import *

root = Tk()
root.title('Side by side')

w = Label(root, text="red", bg="red", fg="white")
w.pack(padx=5, pady=10, side=RIGHT)
w = Label(root, text="green", bg="green", fg="black")
w.pack(padx=5, pady=20, side=RIGHT)
w = Label(root, text="blue", bg="blue", fg="white")
w.pack(padx=5, pady=20, side=RIGHT)

mainloop()

The output of the program is shown below:



The Place Geometry Manager

The Place geometry manager allows us to explicitly set the position and size of a window, either in absolute terms, or relative to another window. The place manager can be accessed through the place method and can be applied to all standard widgets.

We use the place geometry manager in the following example. In the following program we assign to every label a different color, which we randomly create using the randrange method of the random module. We calculate the brightness (grey value) of each color. If the brightness is less than 120, we set the foreground colour (fg) of the label to White otherwise to black, so that the text can be easier read. See the code below:

from tkinter import *
import random

root = Tk()
root.title('Place geometry manager')

root.geometry("170x200+30+30")
   
languages = ['Python','Perl','C++','Java','Tcl/Tk']
labels = range(5)
for i in range(5):
   ct = [random.randrange(256) for x in range(3)]
   brightness = int(round(0.299*ct[0] + 0.587*ct[1] + 0.114*ct[2]))
   ct_hex = "%02x%02x%02x" % tuple(ct)
   bg_colour = '#' + "".join(ct_hex)
   l = Label(root,
                text=languages[i],
                fg='White' if brightness < 120 else 'Black',
                bg=bg_colour)
   l.place(x = 20, y = 30 + i*30, width=120, height=25)

root.mainloop()


The output of the program is shown below:



The Grid Manager

The Grid geometry manager places the widgets in a 2-dimensional table, which consists of a number of rows and columns. The position of a widget is defined by a row and a column number. Widgets with the same column number and different row numbers will be above or below each other. Correspondingly, widgets with the same row number but different column numbers will be on the same "line" and will be beside of each other, i.e. to the left or the right.

Using the grid manager means that we create a widget, and use the grid method to tell the manager in which row and column to place them. The size of the grid doesn't have to be defined, because the manager automatically determines the best dimensions for the widgets used. See the code below:

from tkinter import *
import random

root = Tk()
root.title('Grid Manager')

colours = ['red','green','orange','white','yellow','blue']

r = 0
for c in colours:
    Label(text=c, relief=RIDGE,width=15).grid(row=r,column=0)
    Entry(bg=c, relief=SUNKEN,width=10).grid(row=r,column=1)
    r = r + 1
root.mainloop()


The output of the program is shown below:



Try to make some more programs to practice these Layout Managers. I'll end this post now and till we meet next, keep learning and practicing Python as Python is easy to learn!





Share: