Author name: Prasanna

In this Page, We are Providing Introduction to Python – Executing Python Script. Students can visit for more Detail and Explanation of Python Handwritten Notes Pdf.

Introduction to Python – Executing Python Script

Executing Python script

As discussed in the previous section, the Python script can be executed using the F5 functional key, from Python’s IDE. It can also be executed using a command prompt by typing the following command:

$ python <filename>

On different platforms, the execution of Python scripts (apart from running from inside Python’s IDE) can be carried out as follows:

Linux

On Unix/Linux system, Python script can be made directly executable, like shell scripts, by including the following expression as the first line of the script (assuming that the interpreter is on the user’s PATH) and giving the file an executable mode.

#! /usr/bin/env python

The ‘# !’ must be the first two characters of the file. Note that the hash or pound character ‘#’ is used to start a comment in Python. The script can be given an executable mode/permission, using the chmod command:

$ chmod + x HelloWorld.py

Windows

On the Windows system, the Python installer automatically associates .py files with python.exe, so that double-click on a Python file will run it as a script. The extension can also be .pyw, in that case, the console window that normally appears is suppressed. At MS-DOS prompt, the Python script can be executed by going to the directory containing the script and just entering the script name (with extension).

In this Page, We are Providing Introduction to Python – Script Mode. Students can visit for more Detail and Explanation of Python Handwritten Notes Pdf.

Introduction to Python – Script Mode

Script mode

If the Python interpreter is closed and then invoked again, the definitions that were made (functions, variables, etc.) are lost. Therefore, to write a long program, the programmer should use a text editor to prepare the input for the interpreter and run it with that file as input instead. This is known as creating a “script”. Most of the examples in this book are discussed using interactive mode, but few scripts are also incorporated.

First program

This section will demonstrate to write a simple Python program, which prints “Hello World”. Type the following lines in an IDLE text editor and save it as “HelloWorld.py”.

#! /usr/bin/env python 
print('Hello world')

The first line is called “shebang line” or “hashbang line” (more information in the next section). The second line gives the output: “Hello World”. There are numerous ways to run a Python program. The simplest approach is to press the F5 functional key after saving the program in an IDLE text editor. The output is shown below:

>>>
Hello world

In this Page, We are Providing Introduction to Python – Interactive Mode. Students can visit for more Detail and Explanation of Python Handwritten Notes Pdf.

Introduction to Python – Interactive Mode

Interactive mode

One of Python’s most useful features is its interactive interpreter. It allows very fast testing of ideas without the overhead of creating test files, as is typical in most programming languages. However, the interpreter supplied with the standard Python distribution is somewhat limited for extended interactive use. IPython is a good choice for the comprehensive environment for interactive and exploratory computing.

To start interactive mode, launch Python with no arguments (possibly by selecting it from your computer’s main menu). It is a very powerful way to test out new ideas or inspect modules and packages.

Interactive mode prompts for the next command with the “primary prompt”, usually three greater-than signs (>>>); a continuation line is prompted with the “secondary prompt”, which is by default represented by three dots (…). The interpreter prints a welcome message stating its version number and some additional information before printing the first prompt:

$ python
Python 2.7 (#1, Feb 28 2010, 00:02:06)
Type "help", "copyright", "credits" or "license" for more information.
>>>

Continuation lines are needed when entering a multi-line statement. As an example, take a look at this if statement:

>>> the_world_is_flat = 1 
>>> if the_world_is_flat:
... print("Be careful not to fall off!")
...
Be careful not to fall off!

Invoking Python interpreter

In Unix/Linux platforms, the Python interpreter is usually installed at /usr/local/bin/python. It is possible to start interpreter by typing the following command (same command for MS Windows)

$ python

in the shell. Since the choice of the directory where the interpreter lives is an installation option, other places are possible (e.g/usr/local/python is a popular alternative location).

On Windows machines, the Python installation is available at path C:\Python27, though, this can be changed when running the installer. To add this directory to Path environmental variable, type the following command into the MS-DOS command prompt:

set path=%path%;C:\python27

Inputting the end-of-file character (Control-D on Unix, Control-Z on Windows) at the primary prompt causes the interpreter to exit. If that does not work, you can exit the interpreter by typing the following command:

>>> quit()

In this Page, We are Providing Introduction to Python – Object. Students can visit for more Detail and Explanation of Python Handwritten Notes Pdf.

Introduction to Python – Object

Introduction to Python Object

“Object” (also called “name”) is Python’s abstraction for data. All data in a Python program is represented by objects or by relations between objects. Every object has an identity, a type, and a value. An object’s identity never changes once it has been created; it can be thought of it as the object’s address in memory. The id () function returns an integer representing its identity (currently implemented as its address). An object’s type determines the operations that the object supports and also defines the possible values for objects of that type.

An object’s type is also unchangeable and the type () function returns an object’s type. The value of some objects can change. Objects whose value can change are said to be “mutable”; objects whose value is unchangeable once they are created are called “immutable”. In the example below, object a has identity 31082544, type int, and value 5.

>>> a=5
>>> id (a)
31082544 
>>> type (a) 
<type 'int'>

Some objects contain references to other objects; these are called “containers”. Examples of containers are tuples, lists, and dictionaries. The value of an immutable container object that contains a reference to a mutable object can change when the latter’s value is changed; however, the container is still considered immutable because the collection of objects it contains cannot be changed. So, immutability is not strictly the same as having an unchangeable value.

An object has an attribute(s), which are referenced using dotted expressions. For example, if an object ABC has an attribute PQ, then it would be referenced as ABC. PQ. In the following example, upper () is an attribute of var object.

>>> var= 'hello'
>>> var.upper()
'HELLO'

In the above example, upper () is a function on some object var, and this function is called “method”. More information on “method” is given in chapter 6.

We have compiled most frequently asked Python Interview Questions which will help you with different expertise levels.

Python Interview Questions on Array Sequence

Low Level Arrays

Let’s try to understand how information is stored in low-level computer architecture in order to understand how array sequences work. Here is a small brush-up on computer science basics on memory units.

We know that the smallest unit of data in a computer is a bit (0 or 1). 8 bits together make a byte. A byte has 8 binary digits. Characters such as alphabets, numbers, or symbols are stored in bytes. A computer system memory has a huge number of bytes and tracking of how information is stored in these bytes is done with the help of a memory address. Every byte has a unique memory address which makes tracking information easier.

The following diagram depicts a lower-level computer memory. It shows a small section of memory of individual bytes with consecutive addresses.

Computer system hardware is designed in such a manner that the main memory can easily access any byte in the system. The primary memory is located in the CPU itself and is known as RAM. Any byte irrespective of the address can be accessed easily. Every byte in memory can be stored or retrieved in constant time, hence its Big-O notation for the time complexity would be O(1).

There is a link between an identifier of a value and the memory address where it is stored, the programming language keeps a track of this association. So, a variable student_name may store name details for a student and class_ teacher would store the name of a class teacher. While programming, it is often required to keep a track of all related objects.

So, if you want to keep a track of scores in various subjects for a student, then it is a wise idea to group these values under one single name, assign each value an index and use the index to retrieve the desired value. This can be done with the help of Arrays. An array is nothing but a contiguous block of memory.

Python internally stores every Unicode character in 2 bytes. So, if you want to store a 5-letter word (let’s say ‘state’) in python, this is how it would get stored in memory:

Since each Unicode character occupies 2 bytes, the word STATE is stored in 10 consecutive bytes in the memory. So, this is a case of an array of 5 characters. Every location of an array is referred to as a cell. Every array element is numbered and its position is called index. In other words, the index describes the location of an element.

Table

Every cell of an array must utilize the same number of bytes.

The actual address of the 1^st element of the array is called the Base Address. Let’s say the name of the array mentioned above is my_array[ ]. The Base address of my_array[ ] is 1826. If we have this information it is easy to calculate the address of any element in the array.

Address of my array [index] = Base Address +( Storage size in bytes of one element in the array) * index.

Therefore, Address of my array[3] = 1826 + 2*3
= 1826 + 6
= 182C
After that, slight information on how things work at a lower level lets’s get back to the higher level of programming where the programmer is only concerned with the elements and index of the array.

Referential Array

We know that in an array, every cell must occupy the same number of bytes. Suppose we have to save string values for the food menu. The names can be of different lengths. In this case, we can try to save enough space considering the longest possible name that we can think of but that does not seem to be a wise thing to do like a lot of space is wasted in the process and you never know there may be a name longer than the value that we have catered for. A smarter solution, in this case, would be to use an array of object references.

In this case, every element of the array is actually a reference to an object. The benefit of this is that every object which is of string value can be of different length but the addresses will occupy the same number of cells. This helps in maintaining the constant time factor of order O(1).

In Python, lists are referential in nature. They store pointers to addresses in the memory. Every memory address requires a space of 64-bits which is fixed.

Question 1.
You have a list integer list having integer values. What happens when you give the following command?
integer_list[1] + = 7
Answer:
In this case, the value of the integer at index 1 does not change rather we end up referring to space in the memory that stores the new value i.e. the sum of integer_list[1]+7.

Question 2.
State whether True or False:
A single list instance may include multiple references to the same object as elements of the list.
Answer:
True

Question 3.
Can a single object be an element of two or more lists?
Answer:
Yes

Question 4.
What happens when you compute a slice of the list?
Answer:
When you compute a slice of the list, a new list instance is created. This new list actually contains references to the same elements that were in the parent list.
For example:

>>> my_list = [1, 2,8,9, "cat", "bat", 18]
>>> slice_list = my_list[2:6]
>>> slice_list
[8, 9, 'cat' , 'bat' ]
>>>

This is shown in the following diagram:

Question 5.
Suppose we change the value of the element at index 1 of the slice just to 18 (preceding diagram). How will you represent this in a diagram?
Answer:
When we say sliceJist[1]=18, we actually are changing the reference that earlier pointed to 9 to another reference that points to value 18. The actual integer object is not changed, only the reference is shifted from one location to the other.

Deep Copy and Shallow Copy in Python

Python has a module named “copy” that allows deep copy or shallow copy mutable objects.

Assignment statements can be used to create a binding between a target and an object, however, they cannot be used for copying purposes.

Deep Copy

The copy module of python defines a deepcopy( ) function which allows the object to be copied into another object. Any changes made to the new object will not be reflected in the original object.

In the case of shallow copy, a reference of the object is copied into another object as a result of which changes made in the copy will be reflected in the parent copy. This is shown in the following code:

It is important to note here that shallow and deep copying functions should be used when dealing with objects that contain other objects (lists or class instances), A shallow copy will create a compound object and insert into it, the references the way they exist in the original object. A deep copy on the other hand creates a new compound and recursively inserts copies of the objects the way they exist in the original list.

Question 6.
What would be the result of the following statement? my list = [7]* 10?
Answer:
It would create a list named my list as follows:
[7, 7, 7, 7, 7, 7, 7, 7, 7, 7]

All the 10 cells of the list my_list, refers to the same element which in this case is 10.

Question 7.
Have a look at the following piece of code:

>>> a = [1,2,3,4,5,6,7]
>>> b = a
>>>b [ 0 ] = 8
>>> b
[8, 2, 3, 4, 5, 6, 7]
>>> a
[8, 2, 3, 4, 5, 6, 7]
>>>

Here, we have used the assignment operator still on making changes to ‘b’ changes are reflected in ‘a’. Why?
Answer:
When you use an assignment operator you are just establishing a relationship between an object and the target. You are merely setting a reference to the variable. There are two solutions to this:

(I)

>>> a
[8,2,3, 4, 5, 6, 7]
>>>a = [ 1,2 3, 4,5 ,6, 7]
>>> b = a [:]
>>>b[0] = 9
>>> b
[9, 2, 3, 4, 5, 6, 7]
>>> a
[1, 2, 3, 4, 5, 6, 7]
>>>

(II)

>>> a = [ 1,2 3, 4,5 6, 7]
>>> b = list (a)
>>>b
[1,2, 3, 4, 5, 6, 7]
>>>b [0] = 9
>>> b
[9, 2, 3, 4, 5, 6, 7]
>>> a
[1, 2, 3, 4, 5, 6, 7]
>>>

Question 8.
Take a look at the following piece of code:

>>> import copy
>>> a = [1 9 9 4 5, 5]
>>> b = copy.copy(a)
>>> b
[l, 2, 3, 4, 5, 6]
>>>b [2] = 9
>>> b
[1.2, 9, 4, 5, 6]
>>> a
[1.2, 3, 4, 5, 6]
>>>

‘b’ is a shallow copy of ‘a’ however when we make changes to ‘b’ it is not reflected in ‘a’, why? How can this be resolved?
Answer:
List ‘a’ is a mutable object (list) that consists of immutable objects (integer).
A shallow copy would work with the list containing mutable objects.
You can use b=a to get the desired result.

Question 9.
Look at the following code:

>>> my_list = [["apples", "banana"], ["Rose", "Lotus"], ["Rice", "Wheat"]]
>>> copy_list = list (my_list)
>>> copy_list[2][0]="cereals"

What would happen to the content of my_list? Does it change or remains the same?
Answer:
Content of my_list will change:

>>> my list [['apples', 'banana'] , [ 'Rose', 'Lotus'],
['cereals', 'Wheat']]
>>>

Question 10.
Look at the following code:

>>> my_list = [ ["apples", "banana"], ["Rose", "Lotus"], ["Rice", "Wheat"]]
>>> copy_list = my_list.copy( )
>>> copy_list[2][0]="cereals"

What would happen to the content of my_list? Does it change or remains the same?
Answer:
Content of my_list would change:

>>> my_list
[ [ 'apples' , 'banana1' ] , [ 'Rose' , 'Lotus' ] ,
[ 'cereals', 'Wheat']]
>>>

Question 11.
When the base address of immutable objects is copied it is called_________________.
Answer:
Shallow copy

Question 12.
What happens when a nested list undergoes deep copy?
Answer:
When we create a deep copy of an object, copies of nested objects in the original object are recursively added to the new object. Thus, a deep copy will create a completely independent copy of not only of the object but also of its nested objects.

Question 13.
What happens when a nested list undergoes shallow copy?
Answer:
A shallow copy just copies references of nested objects therefore the copies of objects are not created.

Dynamic Arrays

As the name suggests dynamic array is a contiguous block of memory that grows dynamically when new data is inserted. It has the ability to adjust its size automatically as and when there is a requirement, as a result of which, we need not specify the size of the array at the time of allocation and later we can use it to store as many elements as we want.

When a new element is inserted in the array, if there is space then the element is added at the end else a new array is created that is double in size of the current array, so elements are moved from the old array to the new array and the old array is deleted in order to create some free memory space. The new element is then added at the end of the expanded array.

Let’s try to execute a small piece of code. This example is executed on 32-bit machine architecture. The result can be different from that of 64-bit but the logic remains the same.
For a 32-bit system 32-bits (i.e 4 bytes) are used to hold a memory address. So, now let’s try and understand how this works:
When we created a blank list structure, it occupies 36 bytes in size. Look at the code given as follows:

import sys
my_dynamic_list =[ ]
print("length = ",len(my_dynamic_list) , ".", "size in bytes = ", sys.getsizeof(my_dynamic_list),".")

Here we have imported the sys module so that we can make use of getsizeof( ) function to find the size, the list occupies in the memory.
The output is as follows:
Length = 0.
Size in bytes = 36 .
Now, suppose we have a list of only one element, let’s see how much size it occupies in the memory.

import sys
my_dynamic_list =[1]
print("length = ",len(my_dynamic_list),".", "size in bytes = ", sys.getsizeof(my_dynamic_list),".")
length = 1 . size in bytes = 40 .

This 36 byte is just the requirement of the list data structure on 32- bit architecture.
If the list has one element that means it contains one reference to memory and in a 32-bit system architecture memory, the address occupies 4 bytes. Therefore, the size of the list with one element is 36+4 = 40 bytes.
Now, let’s see what happens when we append to an empty list.

import sys
my_dynamic_list =[ ]
value = 0
for i in range(20):
print("i = ",i,".", "                                  Length of my_
dynamic_list = ",len(my_dynamic_list),".", " size in bytes = ", sys.getsizeof(my_dynamic_ list),".")
my_dynamic_list.append(value)
value +=1

Output
i = 0 .    Length of mydynamic list = 0 .      size in bytes =36
i = 1 .    Length of my_dynamic_list = 1 .    size in bytes = 52
i = 2 .    Length of my_dynamic list = 2 .    size in bytes = 52
i = 3 .    Length of my dynamic list = 3 .    size in bytes = 52
i = 4.     Length of my dynamic list = 4 .    size in bytes = 52
i= 5.      Length of my dynamic list = 5 .    size in bytes = 68
i = 6.     Length of my_dynamic_list = 6 .   size in bytes = 68
i = 7.     Length of my_dynamic_list = 7 .   size in bytes = 68
i = 8 .    Length of my_dynamic_list = 8 .   size in bytes = 68

i = 9.     Length of my_dynamic_list = 9 .   size in bytes = 100
i = 10.   Length of my dynamic list =10.    size in bytes = 100
i = 11 .  Length of my dynamic list =11.    size in bytes = 100
i = 12 .  Length of my_dynamic_list = 12 . size in bytes = 100
i = 13 .  Length of my dynamic list =13.    size in bytes = 100
i = 14.   Length of my_dynamic_list = 14 . size in bytes = 100
i = 15 .  Length of my dynamic list =15.    size in bytes = 100
i = 16.   Length of my dynamic list = 16.   size in bytes = 100
i = 17.   Length of my_dynamic_list =17.   size in bytes = 136
i = 18 .  Length of my dynamic list =18.    size in bytes = 136
i = 19.  Length of my_dynamic_list = 19.   size in bytes = 136

Now, lets have a look at how things worked here:

When you call an append( ) function for list, resizing takes place as per list_resize( ) function defined in Objects/listobject.c file in Python. The job of this function is to allocate cells proportional to the list size thereby making space for additional growth.
The growth pattern is : 0,4,8,16,25,35,46,58,72,88,

Amortization

Let’s suppose that there is a man called Andrew, who wants to start his own car repair shop and has a small garage. His business starts and he gets his first customer however, the garage has space only to keep one car. So, he can only have one car in his garage.

Seeing a car in his garage, another person wants to give his car to him. Andrew, for the ease of his business, wants to keep all cars in one place. So, in order to keep two cars, he must look for space to keep two cars, move the old car to the new space and also move the new car to the new space and see how it works.

So, basically, he has to:

1. Buy new space
2. Sell the old space

Let’s say, this process takes one unit of time. Now, he also has to:

1. Move the old car to a new location
2. Move the new car to a new location Moving each car takes one unit of time.

Andrew is new in the business. He does not know how his business would expand, also what is the right size for the garage. So, he comes up with the idea that if there is space in his garage then he would simply add the new car to space, and when the space is full he will get a new space twice as big as the present one and then move all cars there and get rid of old space. So, the moment he gets his new car, it’s time to buy a new space twice the old space and get rid of the old space.

Now, when the fourth car arrives Andrew need not worry. He has space for the car.

And now again when he gets the fifth car he will have to buy a space that is double the size of the space that he currently has and sell the old space.

So, let’s now take a look at the time complexity: Let’s analyze how much does it take to add a car to Andrew’s garage where there is n number of cars in the garage.

Here is what we have to see:

1. If there is space available, Andrew just has to move one car into a new space and that takes only one unit of time. This action is independent of the n (number of cars in the garage). Moving a car in a garage that has space is constant time i.e. O(1).

2. When there in spot and a new car arrives, Andrew has to do the following:

• Buy a new space that takes 1 unit of time.
• Move all cars into new space one by one and then move the new car into free space. Moving every car takes one unit of time.

So, if there were n cars already in the old garage, plus one car then that would take n+1 time units to move the car.
So, the total time taken in step two is 1+n+l and in Big O notation this would mean O(n) as the constant does not matter.

On the look of it, one may think this is too much of a task but every business plan should be correctly analyzed. Andrew will buy a new garage only if the space that he has, gets all filled up. On spreading out the cost over a period of time, one will realize that it takes a good amount of time only when the space is full but in a scenario where there is space, the addition of cars does not take much time.

Now keeping this example in mind we try to understand the amortization of dynamic arrays. We have already learned that in a dynamic array when the array is full and a new value has to be added, the contents of the array are moved on to the new array that is double in size, and then the space occupied by the old array is released.

It may seem like the task of replacing the old array with a new one is likely to slow down the system. When the array is full, appending a new element may require O(n) time. However, once the new array has been created we can add new elements to the array in constant time O(1) till it has to be replaced again. We will now see that with the help of amortization analysis how this strategy is actually quite efficient.

As per the graph above, when there are two elements then on calling append, the array will have to double in size, same after 4th and 8th element. So, at 2, 4, 8, 16… append will be O(n) and for the rest of the cases, it will be 0(1).
The steps involved are as follows:

1. When the array is full, allocate memory for the new array and the size of the new array should typically be twice the size of the old array.
2. All contents from the old array should be copied to the new array.
3. The space occupied by the old array should be released.

The analysis would be as follows:

Python Data Persistence – RDBMS Concepts

The previous chapter discussed various tools offered by Python for data persistence. While the built-in file object can perform basic read/write operations with a disk file, other built-in modules such as pickle and shelve enable storage and retrieval of serialized data to/from disk files. We also explored Python libraries that handle well-known data storage formats like CSV, JSON, and XML.

Drawbacks of Flat File

However, files created using the above libraries are flat. They are hardly useful when it comes to real-time, random access, and in-place updates. Also, files are largely unstructured. Although CSV files do have a field header, the comma-delimited nature of data makes it very difficult to modify the contents of a certain field in a particular row. The only alternative that remains, is to read the file in a Python object such as a dictionary, manipulate its contents, and rewrite it after truncating the file. This approach is not feasible especially for large files as it may become time-consuming and cumbersome.

Even if we keep this issue of in-place modification of files aside for a while, there is another problem of providing concurrent r/w access to multiple applications. This may be required in the client-server environment. None of the persistence libraries of Python have built-in support for the asynchronous handling of files. If required, we have to rely upon the locking features of the operating system itself.

Another problem that may arise is that of data redundancy and inconsistency. This arises primarily out of the unstructured nature of data files. The term ‘redundancy’ refers to the repetition of the same data more than one time while describing the collection of records in a file. The first row of a typical CSV file defines the column headings, often called fields and subsequent rows are records.
Following table 7.1 shows a ‘pricelistxsv’ represented in the form of a table. Popular word processors (MS Word, OpenOffice Writer) and spreadsheet programs (MS Excel, OpenOffice Calc) have this feature of converting text delimited by comma or any other character to a table.

As we can see, data items such as customer’s name, product’s name, and price are appearing repeatedly in the rows. This can lead to two issues: One, a manual error such as spelling or maintaining correct upper/lower case can creep up. Secondly, a change in the value of a certain data item needs to reflect at its all occurrences, failing which may lead to a discrepancy. For example, if the price of a TV goes up to 45000, the price and total columns in invoice numbers 2 and 6 should be updated. Otherwise, there will be inconsistency in the further processing of data. These problems can be overcome by using a relational database.

SQLite installation

Installation of SQLite is simple and straightforward. It doesn’t need any elaborate installation. The entire application is a self-contained executable ‘sqlite3.exe’. The official website of SQLite, (https://sqIite.org/download.html) provides pre-compiled binaries for various operating system platforms containing the command line shell bundled with other utilities. All you have to do is download a zip archive of SQLite command-line tools, unzip to a suitable location and invoke sqlite3.exe from DOS prompt by putting the name of the database you want to open.

If already existing, the SqLite3 database engine will connect to it; otherwise, a new database will be created. If the name is omitted, an in-memory transient database will open. Let us ask SQLite to open a new mydatabase.sqlite3.

E:\SQLite>sqlite3 mydatabase.sqlite3 
SQLite version 3.25.1 2018-09-18 20:20:44 
Enter ".help" for usage hints. 
sqlite>

In the command window, a SQLite prompt appears before which any SQL query can be executed. In addition, there “dot commands” (beginning with a dot “.”) typically used to change the output format of queries or to execute certain prepackaged query statements.
An existing database can also be opened using the .open command.

E:\SQLite>sqlite3
SQLite version 3.25.1 2018-09-18 20:20:44 
Enter ".help" for usage hints.
Connected to a transient in-memory database. 
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open test.db

The first step is to create a table in the database. As mentioned above, we need to define its structure specifying the name of the column and its data type.

SQUte Data Types

ANSI SQL defines generic data types, which are implemented by various RDBMS products with a few variations on their own. Most of the SQL database engines (Oracle, MySQL, SQL Server, etc.) use static typing. SQLite, on the other hand, uses a more general dynamic type system. Each value stored in SQLite database (or manipulated by the database engine) has one of the following storage classes:

• NULL
• INTEGER
• REAL
• TEXT
• BLOB

A storage class is more general than a datatype. These storage classes are mapped to standard SQL data types. For example, INTEGER in SQLite has a type affinity with all integer types such as int, smallint, bigint, tinyint, etc. Similarly REAL in SQLite has a type affinity with float and double data type. Standard SQL data types such as varchar, char, char, etc. are equivalent to TEXT in SQLite.
SQL as a language consists of many declarative statements that perform various operations on databases and tables. These statements are popularly called queries. CREATE TABLE query defines table structure using the above data types.

CREATE TABLE

This statement is used to create a new table, specifying the following details:

• Name of the new table
• Names of columns (fields) in the desired table
• Type, width, and the default value of each column.
• Optional constraints on columns (PRIMARY KEY, NOT NULL, FOREIGN KEY)

Example

CREATE TABLE table_name ( 
column 1 datatype [width] [default] [constraint], 
column2 , 
column3..., 
 ) ;

DELETE Statement

If you need to remove one or more records from a certain table, use the DELETE statement. The general syntax of the DELETE query is as under:

Example

DELETE FROM table_name WHERE [condition];

In most circumstances, the WHERE clause should be specified unless you intend to remove all records from the table. The following statement will remove those records from the Invoices table having Quantity>5.

sqlite> select customers.name, products. name, the quantity from invoices, customers, products
...> where invoices.productID=Products.ProductID ...> and invoices.CustID=Customers.CustID;
Name               Name          Quantity
---------           ---------        ---------
Ravikumar        Laptop              2
Divya                Mobile              3
Ravikumar        Mobile              1
John                 Printer               3
Patel                 Laptop             4
Irfan                 Printer              2
Nit in Scanner 3

ALTER TABLE statement

On many occasions, you may want to make changes in a table’s structure. This can be done by the ALTER TABLE statement. It is possible to change the name of a table or a column or add a new column to the table.

The following statement adds a new column in the ‘Customers’ table:

sqlite> alter table customers add column address text (20);
sqlite> .schema customers CREATE TABLE Customers (
CustID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT (20),
GSTIN TEXT (15), address text (20));

DROP TABLE Statement

This statement will remove the specified table from the database. If you try to drop a non-existing table, the SQLJte engine shows an error.
sqlite> drop table invoices; sqlite> drop table employees; Error: no such table: employees
When the ‘IF EXISTS’ option is used, the named table will be deleted only if exists and the statement will be ignored if it doesn’t exist.
sqlite> drop table if exists employees;

SQLiteStudio

SQLiteStudio is an open-source software from https://sqlitestudio.pl. It is portable, which means it can be directly run without having to install. It is powerful, fast, and yet very light. You can perform CRUD operations on a database using GUI as well as by writing SQL queries.

Download and unpack the zip archive of the latest version for Windows from the downloads page. Run SQLiteStudio.exe to launch the SqliteStudio. Its opening GUI appears as follows:(figure 7.3)

Currently attached databases appear as expandable nodes in the left column. Click any one to select and the ‘Tables’ sub-node shows tables in the selected database. On the right, there is a tabbed pane. The first active tab shows structure of the selected table and the second tab shows its data. The structure, as well as data, can be modified. Right-click on the Tables subnode on the left or use the Structure menu to add a new table. User-friendly buttons are provided in the Structure tab and data tab to insert/ modify column/row, commit, or rollback transactions.

This concludes the current chapter on RDBMS concepts with a focus on the SQLite database. As mentioned in the beginning, this is not a complete tutorial on SQLite but a quick hands-on experience of interacting with SQLite database to understand Python’s interaction with databases with DB-API that is the subject of the next chapter.

Python Data Persistence – Python – PyMongo

is NOSQL?

In today’s era of real-time web applications, NoSQL databases are becoming increasingly popular. The term NoSQL originally referred to “non SQL” or “non-relational”, but its supporters prefer to call it “Not only SQL” to indicate that SQL-like query language may be supported alongside.

NoSQL is touted as open-source, distributed, and horizontally scalable schema-free database architecture. NoSQL databases are more scalable and provide superior performance as compared to RDBMS. This primarily because it requires that schemas (table structure, relationships, etc.) be defined before you can add data. Some of the popular NoSQL databases extensively in use today include MongoDB, CouchDB, Cassandra, HBase, etc.

Several NoSQL products are available in the market. They are classified into four categories based on the data model used by them.

Key-Value store: Uses a hash table (also called a dictionary). Each item in the database is stored as a unique attribute name (or ‘key’), associated with its value. The key-value model is the simplest type of NoSQL database. Examples of key-value databases are Amazon SimpleDB, Oracle BDB, Riak, and Berkeley DB.

Column Oriented: This type of databases store and process very large amount of data distributed over multiple machines. Keys point to multiple columns. The columns are arranged by column family. Examples of column-oriented databases are Cassandra and HBase.

Document oriented: Database of this category is an advanced key-value store database. The semi-structured documents are stored in formats like JSON. Documents can contain many different key-value pairs, or key- array pairs, or even nested documents.

Graph Based: Databases of this type are used to store information about networks of data, such as social connections. A flexible graph model can scale across multiple machines. Graph stores include Neo4J and Giraph.
In this chapter, we shall get acquainted with a hugely popular document-oriented database, MongoDB, and how it can be interfaced with Python through the PyMongo module.

MongoDB

NoSQL databases typically have a huge amount of data. Hence, more often than not, the power of a single CPU is not enough when it comes to fetching data corresponding to a query. MongoDB uses a sharding technique that splits data sets across multiple instances. A large collection of data is split across multiple physical servers called ‘shards’, even though they behave as one collection. Query request from the application is routed to the appropriate shard and the result is served. Thus, MongoDB achieves horizontal scalability, (figure 11.1)

The Document is the heart of a MongoDB database. It is a collection of key-value pairs – similar to Python’s dictionary object. We can also think of it being similar to a single row in a table of SQL-based relational databases.

Collection in MongoDB is analogous to a table in the relational database. However, it doesn’t have a predefined schema. The Collection has a dynamic schema in the sense each document may of a variable number of k-v pairs not necessarily with the same keys in each document.

Each document is characterized by a special key called “_id” having a unique value, again similar to a primary key in the entity table of a relational database.

MongoDB server has a command-line interface from inside which different database operations can be performed.

MongoDB – Create Database

To display the current database in use, there’s a DB command. The default database in-use tests.

> db 
Test

With the ‘use’ command any other database is set as current. If the named database doesn’t exist, a new one is created.

> use mydb 
switched to db mydb

However, until you store data(such as collection or document) in it, is the database is not created. The following command inserts a document in the ‘products’ collection under the current database.

MongoDB – Insert Document

Appropriately, insertone ( ) method is available to a collection object in a database. A document is provided to it as a parameter.

> db.products.insertone({"ProductID":1, 
"Name":"Laptop", "Price":25000}) 
WriteResult({ "nlnserted" : 1 })

Result of above (for that matter any insert/update/delete operation) command returns WriteResult object. The insert () method inserts multiple documents if the argument is an array of documents. In that case, the result is BulkWriteResult object.

> var pricelist=[{'productID' :1, 'Name': 'Laptop' , 'price':25000}, 
. . . { 'productID' : 2, 'Name":'TV' , 'price':2000}, 
. . . { 'productID' : 3, 'Name":'Router' , 'price':2000}, 
. . . { 'productID' : 4, 'Name":'Scanner' , 'price':5000}, 
. . . { 'productID' : 5, 'Name":'Printer' , 'price':9000}, 
> db.products.insert(pricelist); 
BulkWriteResult ({
 "writeErrors" : [ ] , 
 "writeConcernErrors" : [ ] , 
 "nInserted" : 5 , 
 "nUpserted" : 0 , 
 "nMatched" : 0 , 
 "nModified" : 0 , 
 "nRemoved" : 0 , 
 "upserted" : [ ] 
 })

The insert () function inserts a single document or array whereas a single document is inserted with inserOne ( ) method and array whereas the insert_many () method is used with an array.

MongoDB – Delete Document

The remove () method deletes one or more documents from the collection based on the provided criteria. The following statement will result in the removal of a document pertaining to price>40000 (in our data it happens to be with name=’TV’).

> db.products.remove({"price":{$gt:40000}}) 
WriteResult({ "nRemoved" : 1 })

Run the find ( ) method in the shell to verify the removal.

Now that, we have attained some level of familiarity with MongoDB with the help of shell commands. Let us concentrate on our main objective – use MongoDB in Python.

PyMongo – Update Document

PyMongo offers two collection methods for the modification of data in one or more documents. They are update_one () and update_many () . Both require filter criteria and a new value of one or more keys. The update_one () updates only the first document that satisfies filter criteria. On the other hand, update__many () performs updates on all documents that satisfy the filter criteria.

collection.update_one (filter, newval)

Following Python script accepts the name of the product from the user and displays the current price. It is updated to the new price input by the user.

Example

> var pricelist=[{'productID' :1, 'Name': 'Laptop' , 'price':25000}, 
. . . { 'productID' : 2, 'Name":'TV' , 'price':2000}, 
. . . { 'productID' : 3, 'Name":'Router' , 'price':2000}, 
. . . { 'productID' : 4, 'Name":'Scanner' , 'price':5000}, 
. . . { 'productID' : 5, 'Name":'Printer' , 'price':9000}, 
> db.products.insert(pricelist); 
BulkWriteResult ({
 "writeErrors" : [ ] , 
 "writeConcernErrors" : [ ] , 
 "nInserted" : 5 , 
 "nUpserted" : 0 , 
 "nMatched" : 0 , 
 "nModified" : 0 , 
 "nRemoved" : 0 , 
 "upserted" : [ ] 
 })

Python Data Persistence – pyMongo – Querying Collection

PyMongo module defines find( ) method to be used with a collection object. It returns a cursor object which provides a list of all documents in the collection.

>>> products=db['products']
>>> docs=products .find ()
>>> list(docs)
[{'_id' : Objectld('5c8dec275405cl2e3402423c'),
'ProductID': 1, 'Name': 'Laptop', 'price': 25000},
{'_id': ObjectId('5c8dec275405cl2e3402423d'),
'ProductID': 2, 'Name': 'TV', 'price': 50000},
{'_id': Objectld('5c8dec275405cl2e3402423e'),
'ProductID': 3, 'Name': 'Router', 'price': 2000},
{'_id': Objectld('5c8dec275405cl2e3402423f'),
'ProductID': 4, 'Name': 'Scanner', 'price': 5000},
{'_id': Objectld('5c8dec275405cl2e34024240'),
'ProductID': 5, 'Name': 'Printer', 'price': 9000}]

This cursor object is an iterator that serves one document for every call of the next () method. Each document is a dictionary object of k-v pairs. The following code displays the name and GSTIN of all customers.

Example

#mongofind. py 
from pymongo import MongoClient 
client=MongoClient( ) 
db=client.newdb 
cust=db['customers'] 
docs=cust. find ( ) 
while True:               
 try:                          
         doc=docs.next()                          
         print (doc['Name'], doc['GSTIN'])              
         except Stoplteration:                           
         break 
 client.close ( )

Run the above script from the command prompt.

E : \python3 7 >python mongofind. py
Ravikumar 27AAJPL7103N1ZF
Patel 24ASDFG1234N1ZN
Nitin 27AABBC7895N1ZT
Nair 32MMAF8963N1ZK
Shah 24BADEF2002N1ZB
Khurana 07KABCS1002N1ZV
Irfan 05IIAAV5103N1ZA
Kiran 12PPSDF22431ZC
Divya 15ABCDE1101N1ZA
John 29AAEEC4258E1ZK

You can, of course, employ a regular ‘for’ loop to traverse the cursor object to obtain one document at a time.

Example

for doc in docs: 
      print (doc['Name'] , doc[1GSTIN'])

The logical operators of MongoDB (described earlier in this chapter) are used to apply filter criteria for the find ( ) method. As an example, products with price>10000 are fetched with the following statement:

Example

>>> products=db['products'] 
>>> docs=products .find 
({'price :{ 1$gt ' :10000} } ) 
>>> for 
doc in docs: print (doc.get('Name'), 
doc.get('price')) Laptop 25000 TV 50000

Python Data Persistence – Python and Excel

Interactive spreadsheet applications have been in use for quite a along. Over the years, Microsoft Excel has emerged as the market leader in this software category. Early versions of MS Excel used a proprietary data representation format. After 2003, Microsoft has adopted Office Open XML (OOXML) file format for its later versions of MS Excel.

OOXML is an ECMA standard file format. This has led to the development of programming interfaces for Excel in many programming environments including Python. Out of many Python packages, openpyxl is the most popular and can read/write Excel files with the .xlsx extension. Pandas is a very popular Python library. It has various tools for performing the analysis of data stored in different file formats. Data from Excel sheet, CSV file, or SQL table can be imported into Pandas data frame object for processing.

In this chapter, first, we shall learn to use openpyxl to programmatically perform various operations on an Excel file such as copying a range, define, and copy formulas, insert images, create charts, and so on.

In the second part of this chapter, we get acquainted with Pandas library – specifically, how to read/write data from/to an Excel worksheet.
Before proceeding further, here is a brief recap of important terms used in the Excel spreadsheet. An Excel document is called a workbook and is saved as a .xlsx file. A workbook may have multiple worksheets. Each worksheet is a grid of a large number of cells, each of which can store one piece of data – either value or formula. Each cell in the grid is identified by its row and column number. Columns are identified by alphabets, A, B, C,…., Z, AA, AB, and so on. Rows are numbered starting from 1. (figure 10.1)

Excel With openpyxml

The openpyxl is an open-source package. Its installation is straightforward by using pip utility. It is recommended that you set a virtual environment first and then install openpyxl in it using the following command:

Example

E:\>cd excelenv 
E:\excelenv>scripts\activate
(excelenv) E:\excelenv>scripts\pip3 install openpyxl Collecting openpyxl
Using cached https://files.pythonhosted.org/packag- es/Sf/f8/a5d3a4ab669f99154f87ab531192dd84ac79aae62e- 
fab6G2bd2d82a72194/openpyxl-2.6.1.tar.gz Collecting jdcal (from openpyxl)
Using cached https://files.pythonhosted.org/packag- es/aO/38/dcf8353248Of25284f3ef13f8ed63e03c58a65c9d- 
3ba2a6a894ed94972 07/j deal-1.4-py2.py3-none-any.whl Collecting et_xmlfile (from openpyxl)
Using cached https://files.pythonhosted.org/ packages/22/28/a99c42aea746el8382ad9fb36f64c- 
lclf04216f41797f2fOf a567dall3 8 8/et_xmlfile -1.0.1.tar. gz
Installing collected packages: jdcal, et-xmlfile, openpyxl
Running setup.py install for et-xmlfile ... done Running setup.py install for openpyxl ... done 
Successfully installed et-xmlfile-1.0.1 jdcal-1.4 openpyxl-2.6.1

Read Cell Range to List

First of all, let us read back the data from A2:C7 in a Python list object by traversing the range with two nested loops. Each row is collected in a tuple and appended to a list.

Example

sheet1 = wb['PriceList'] 
pricelist= [ ] 
for row in range(1,7): prod= [ ]
for col in range(1,4):
val=sheetl.cell(column=col,
row=2+row).value
prod.append(val) 
pricelist.append(tuple(prod)) 
print (pricelist)

The result will be as shown below:

[(1, 'Laptop', 25000) , (2, 'TV' , 40000) , (3, 'Router', 2000) , (4, 'Printer', 9000)] 'Scanner', 5000) , (5, 'printer' , 9000)]

Merge and Center

To merge Al-Cl cells use the merge_cells ( ) method.

sheet1.merge_cells('A1:C1')

The openpyx1. styles module defines Alignment and Font classes. Apply ‘Center’ alignment to text in ‘AT

cell=sheetl[1A1']
cell.alignment=Alignment('horizontal center')

The Font object can be configured by defining attributes like name, size, color, and so on. Font constructor also accepts bold, italic, underline, and strike, as Boolean attributes (True/False).

cell.font=Font(name='Calibri',size=20,bold=True)

Insert Image

First, install the pillow library in your current Python environment. It is an open-source Python image library that provides support for opening, saving, and manipulating image files.

pip3 install pillow

Let us create a new worksheet in ‘example.xlsx’ in which we shall insert an image.

wb = load_workbook('example.xlsx') 
sheet2=wb.create_sheet(title="image")

Now import Image class from openpyxl. drawing. image module. Obtain the Image object from the image file to be inserted. Finally call add_image () method of the worksheet. This method needs image object and location, (figure 10.9)

Example

from openpyxl.drawing.image import Image
img = Image('openpyxl.jpg')
sheet2.add_image(img,'Al')
wb . save (filename= ' example . xlsx ' )

Define Formula

It is very easy to define a formula in a cell. Just assign string representation of cell formula as would appear in the formula bar of Excel. For example, if you want to set cell 8 to sum of cells between C3 to 7, assign it to ‘=SUM(C3:C7)’

Example

sheet1 ['B8'] ='SUM'
sheet1['C8']='SUM(C3:C7)'
sheet1['C9'] ='=AVERAGE(C3:C7)'

All above actions are collected in the following script. Run it from the command prompt and then open the workbook, (figure 10.4)

Example

#readworkbook.py
from openpyxl import load_workbook 
wb = load_workbook('test.xlsx') 
sheet1 = wb['PriceList']
#copy range to list 
pricelist= [ ] 
for row in range(1,7): 
prod= [ ]
for col in range(1,4):
val=sheetl.cell(column=col,
row=2+row).value
prod.append(val) 
pricelist.append(tuple(prod)) 
print (pricelist)
#merge and center
sheetl.merge_cells('A1:C1')#merge
cell=sheetl['A1']
cell.alignment=Alignment(horizontal'center1) #apply font
cell.font=Font(name='Calibri',size=20,bold=True)
#define formula
sheetl['b8']='SUM'
sheetl['C8']='=SUM(C3:C7)'
sheetl['C9']='=AVERAGE(C3:C7)'

Copy Formula

One of the important features of Excel software is the ability to copy a cell formula either with the relative or absolute address to other cells or ranges. In the above worksheet, we calculate the difference of each price and average and store it in Column D. Accordingly formula for D4 should be C4-C9. It is to be copied for range D5:D9

The openpyxml. formula module defines Translator class having translate_formula ( ) function that copies formula at original cell (D4) to the required range.

Example

from openpyxl.formula.translate import 
Translator#copy formula 
sheet1['D2']='DIFF' 
sheet1['D3']= ' =C$9-C3' 
sheet1['D4'] = Translator("=C$9-C3", origin="D3") . translate_formula("D4") 
for row in range(4,8): 
coor=sheetl.cell(column=4, row=row). 
coordinate#copy formula to range 
sheet1.cell(column=4, row=row). 
value=Translator("=C$9-C3", origin="D3"). \ 

translate formula(coor)

Using the translate_formula, we can also copy a range of cells to other location. Following snippet copies range A2:D2 which is the table’s heading row to A10:D10 cell range.

Example

sheet1['A10'] = '=A2' 
for col in range(1,4): 
coor=sheet1.cell(column=col,row=3).coordinate 
coor1=sheet1.cell(column=col, row=10).coordinate 
print (coor,coor1) 
sheet1.cell(column=col, row=10). 
value=Translator("=A2", origin="A10"). \ 

translate formula(coor1)

After copying the formula and range, (figure 10.5) the worksheet appears as follows:

Python Data Persistence – class Keyword

Apart from various built-in classes, the user can define a new class with customized attributes and methods. Just as a built-in class, each user-defined class is also a subclass of the Object class. The keyword ‘class’ starts the definition of a new class.

Example

>>> #user defined class
. . .
>>> class MyClass:
. . . pass
. . .
>>> type(MyClass)
<class 'type'>
>>> MyClass.__bases__
(<class 'object'>,)
>>> issubclass(MyClass, object)
True

A user-defined name is mentioned just beside the class keyword and is followed by a symbol to initiate an indented block containing its attributes and methods. In this case, the class body contains just one statement pass which is a no-operation keyword.
Above code clearly indicates that the user-defined class is subclassed from the object class. It means that any Python class, built-in or user-defined is either directly or indirectly inherited from the parent object class. To underline this relationship explicitly, the MyClass can very well be defined as follows:

Example

>>> class MyClass(object):
. . . pass
. . .
>>> MyClass.__bases__
(<class 'object'>,)

Now we can have objects of this class. The library function dict() returns an empty dictionary object. Similarly, MyClass( ) would return an empty object because our new class doesn’t have any attributes defined in it.

Example

>>> obj1=MyClass( )
>>> obj2=MyClass( )

It is veiy easy to add attributes to this newly declared object. To add ‘myname’ attribute to obj 1, give the following statement:

>>> obj1.myname='Ashok'

There is also a built-in setattr ( ) function for this purpose.

>>> setattr(obj1, 'myage',21)

The second argument for this function may be an existing attribute or a new one. The third argument is the value to be assigned. You can even assign a function to an attribute as shown below:

Example

>>> def about me(obj):
. . . print ( ' My name is { } and I am { } years old ' . format ( obj,myname,obj.myage ) )
. . .
>>> setattr(MyClass, 'about', aboutme)
>>> obj1.about( )
My name is Ashok and I am 21 years old

So you have been able to add new attributes to an individual instance or class. Even from outside the class! If you have honed your object-oriented programming skills by learning Java or C++, this might send shivers down your spine! On the face of it, this seems to be a violation of the very idea of class which is a template definition of an object’s attributes. Don’t worry; we will soon make a Python class work as per OOP guidelines.