Python

Python Data Persistence – Constraints

Constraints enforce restrictions on data that a column can contain. They help in maintaining the integrity and reliability of data in the table. Following clauses are used in the definition of one or more columns of a table to enforce constraints:

PRIMARY KEY: Only one column in a table can be defined to be a primary key. The value of this table will uniquely identify each row (a record) in the table. The primary key can be set to AUTOINCREMENT if its type is INTEGER. In that case, its value need not be manually filled.

NOT NULL: By default value for any column in a row can be left null. NOT NULL constraint ensures that while filling a new row in the table or updating an existing row, the contents of specified columns are not allowed to be null. In the above definition, to ensure that the ‘name’ column must have a certain value, NOT NULL constraint is applied to it.

FOREIGN KEY: This constraint is used to enforce the ‘exists’ relationship between two tables.
Let us create a Products table in ‘mydatabase’ that we created above. As shown in Figure 7.1, diagram, the ‘products’ table consists of ProductID, Name, and Price columns, with ProductlD as its primary key.

sqlite> CREATE TABLE Products (
. . . > ProductID INTEGER PRIMARY KEY
AUTOINCREMENT, 
...> Name TEXT (20),
...> Price INTEGER
. . . > ) ;

(Ensure that the SQL statement ends with a semi-colon. You may span one statement over multiple lines in the console)
We also create another ‘Customers’ table in the same database with CustlD and Name fields. The CustlD field should be defined as the primary key.

sqlite> CREATE TABLE Customers (
. . . > CustlD INTEGER PRIMARY KEY
AUTOINCREMENT, 
. . . > Name TEXT (20) ,
. . . > GSTIN TEXT (15)
. . . . > ) ;

Finally, we create another ‘Invoices’ table. As shown in the figure 7.1 diagram, this table has InvID as the primary key and two foreign key columns referring to ProductID in ‘Products’ table and CustlD in the ‘Customers’ table. The ‘Invoices’ table also contains the ‘price’ column.

sqlite> CREATE TABLE Invoices ( 
. . . > InvID INTEGER PRIMARY KEY
AUTOINCREMENT, 
. . . > CustlD INTEGER REFERENCES
Customers (CustlD), 
. . . > ProductID INTEGER REFERENCES
Products (ProductID), 
. . . > Quantity INTEGER (5)
. . . > ) ;

To confirm that our tables have been successfully created, use the .tables command:

sqlite> .tables
Customers Invoices Products

SQLite stores the schema of all databases in the SQL1TEJVIASTER table. We can fetch names of our databases and tables with the following command:

sqlite> SELECT * FROM s'qlite_master WHERE 
type='table';

To terminate the current session of SQLiteS activity use .quit command.

Python Data Persistence – json Module

JavaScript Object Notation (JSON) is an easy-to-use lightweight data-interchange format. It is a language-independent text format, supported by many programming languages. This format is used for data exchange between the web server and clients. Python’s j son module, being a part of Python’s standard distribution, provides serialization functionality similar to the pickle module.

Syntactically speaking, json module offers identical functions for serialization and de-serialization of Python objects. Module-level functions dumps () and loads () convert Python data to its serialized string representation and vice versa. The dump () function uses a file object to persistently store serialized objects, whereas load () function reconstructs original data from the file.
Notably, dumps () function uses additional argument sort_keys. Its default value is False, but if set to True, JSON representation of Python’s dictionary object holds keys in a sorted order.

Example

>>> import json
>>> data=[{'product':'TV1, 1 brand':'Sam¬sung ', 'price' : 25000 }, {1 product' : 'Computer', 'br
and':'Dell','price':40000},{'product':'Mo¬bile ', 'brand' :'Redmi', 'price' : 15000} ]
>>> JString=json.dumps(data, sort_keys=True) .
>>> JString
'[{"brand": "Samsung", "price": 25000, "product": "TV"}, {"brand": "Dell", "price": 40000, "product": "Computer"}, {"brand": "Redmi", "price": 15000, "product": "Mobile"}]'

The loads () function retrieves data in the original format.

Example

>>> string=json.loads(JString)
>>> string
[{'brand': 'Samsung', 'price': 25000, 'product':
'TV'}, {'brand': 'Dell', 'price': 40000, 'product': 'Computer'}, {'brand': 'Redmi', 'price': 15000,
'product': 'Mobile'}]

To store/retrieve JSONed data to/from a disk file, use dump () and load () functions respectively.

Example

>>> file=open ( ' j son. txt' , ' w' )
>>> j son. dump (data, file)
>>> file=open ( ' j son. txt' , ' r ' )
>>> data=j son. load (file)

Python’s built-in types are easily serialized in JSON format. Conversion is done, as per the corresponding table below:ftaZ>/e 6.1)

Python	JSON
Dict	Object
List , tuple	Array
Str	String
Int , float , int-& float-derived Enums	Number
True	True
False	False
None	Null

However, converting objects of a custom class is a little tricky. The json module defines JSONEndoder and JSONDecoder classes. We need to subclass them to perform encoding and decoding of objects of user-defined classes.

The following script defines a User class and an encoder class inherited from the JSONEncoder class. This subclass overrides the abstract default () method to return a serializable version of the User class which can further be encoded.

Example

import json
class User:
      def__init__(self,name, email, pw):
             self.name=name
             self.email=email
             self.pw=pw
     def__str__(self):
return ('Name: {} email: {} password: {}'. \ format(self.name, self.email, self.pw)
class UserEncoder(json.JSONEncoder):
   def default(self, z) :
       if isinstance(z, User):
           return (z.name, z.email, z.pw)
     else:
           super().default(self, z)
usdr1=User('Raj an','[email protected]','**')
encoder=UserEncoder( )
obj =encoder.encode(user1)
file=open (' j sonOO. txt' , ' w ' )
json.dump (obj , file)
file. close ()

To obtain the original object, we need to use a subclass of JSONDecoder. The subclass should have a method that is assigned a value of the object_hook parameter. This method will be internally called when an object is sought to be decoded.

Example

import j son
class UserDecoder(json.JSONDecoder):
         def__init__(self):
              j son.JSONDecoder.__init__
(self,object_hook=self.hook)
         def hook(self,obj):
               return diet(obj)
decoder=UserDecoder()
file=open (' j sonclass . txt' , ' r' )
retobj =j son. load (file)
print (decoder.decode(retobj))

The j son. tool module also has a command-line interface that validates data in string or file and delivers nice formatted output of JSON objects.
Assuming that the current working directory has ‘file.txt’ that contains text in JSON format, as below:

Example

{"name": "Rajan", "email": "[email protected]", "pw": "**"}

The following command produces pretty print output of the above string.

E: \python37>python -m j son. tool file.txt
{
"name": "Rajan",
"email": "rxa.com",

It accepts a -sort-keys command line option to display keys in ascending order.

E:\python3 7>python -m json.tool file.txt --sort-keys
{
"email": " rwa.com",
"name": "Raj an",
"pw" : " * * "
}

Python Data Persistence – xml Package

XML is another well-known data interchange format, used by a large number of applications. One of the main features of extensible Markup Language (XML) is that its format is both human-readable and human-readable. XML is widely used by applications of web services, office tools, and Service-Oriented Architectures (SOA).

Standard Python library’s xml package consists of modules for XML processing, as per different models. In this section, we discuss the ElementTree module that provides a simple and lightweight API for XML processing.
The XML document is arranged in a tree-like hierarchical format. The document tree comprises of elements. Each element is a single node in the tree and has an attribute enclosed in <> and </> tags. Each element may have one or more sub-elements following the same structure.

A typical XML document appears, as follows:

Example

<?xml version="l.0" encoding="iso-8859-l"?> 
<pricelist>
     <product>
              <name >TV</name >
              <brand>Samsung</brand>
              <price>25000</price>
   </product>
   <product>
             <name >Computer</name >
             <brand>Dell</brand>
             <price>40000</price>
   </product>
   <product>
            <name >Mobile </name >
            <brand>Redmi</brand>
            <price>15000</price>
   </product>
</pricelist>

The elementary module’s class structure also has Element and SubElement objects. Each Element has a tag and attrib which is a diet object. For the root element, attrib is an empty dictionary.

Example

>>> import xml.etree.ElementTree as xmlobj
>>> root=xmlobj.Element('PriceList')
>>> root.tag
'PriceList'
>>> root.attrib
{ }

Now, we can add one or more nodes, i.e., elements under root. Each Element object may have SubElements, each having an attribute and text property.

Let us set up the ‘product’ element and ‘name’, ‘brand’, and ‘price’ as its sub-elements.

Example

>>> product=xmlobj.Element('Product')
>>> nm=xmlobj.SubElement(product, 'name')
>>> nm.text='name'
>>> brand=xmlobj.SubElement(product, 'brand')
>>> nm.text='TV'
>>> brand.text='Samsung'
>>> price=xmlobj.SubElement(product, 'price')
>>> price.text='25000'

The root node has an append ( ) method to add this node to it.

Example

>>> root.append(product)

Construct a tree from this root object and write its contents to the XML file.

Example

>> > tree=xmlobj.ElementTree (root)
> > > file=open ( ' pricelist. xml ,'wb')
>>> tree. write (file)
>>> file . close ( )

The ‘pricelist.xmP should be visible in the current working directory. The following script writes a list of dictionary objects to the XML file:

Example

import xml.etree.ElementTree as xmlobj
root=xmlobj.Element(1PriceList')
pricelist=[{'name' :'TV', 'brand1 :'Sam¬sung ' , 'price' : '250001 } ,
{'name' :'Computer', 'brand' :1 Dell1, 'pri ce' : '400001 } ,
{'name' :1 Mobile', 'brand':'Red- mi 1 price':'150001}]
i = 0
for row in pricelist:
i = i + 1
print (i)
element=xmlobj.Element('Product'+str(i))
for k,v in row.items():
sub=xmlobj.SubElement(element, k)
sub.text=v
root.append(element)
tree=xmlobj.ElementTree(root)
file=open ( ' pricelist. xml' , 1 wb ' )
tree . write (file)
file . close ()

To parse the XML file, construct document tree giving its name as file parameter in ElementTree constructor.

Example

import xml.etree.ElementTree as xmlobj
tree = xmlobj .ElementTree (file='pricelist .xml' )

The getroot() method of tree object fetches root element and getchildren() returns a list of elements below it.

Example

root = tree.getroot( ) 
children = root.getchildren()

We can now construct a dictionary object corresponding to each subelement by iterating over the sub-element collection of each child node.

Example

for child in children:
product={ }
pairs = child.getchildren()
for pair in pairs:
product[pair.tag]=pair.text

Each dictionary is then appended to a list returning original list of dictionary objects. Complete code parsing XML file into a list of dictionaries is as follows:

Example

import xml.etree.ElementTree as xmlobj
tree = xmlobj .ElementTree (file='pricelist .xml' )
root = tree.getroot()
products= [ ]
children = root.getchildren() for child in children:
product={ }
pairs = child.getchildren()
for pair in pairs:
product[pair.tag]=pair.text products.append(product)
print (products)

Save the above script from ‘xmlreader.py’ and run it from the command line:

E:\python37 >python xmlreader.py [{'name': 'TV', 'brand': 'Samsung', 'price':
'250001 }, { 'name' : 'Computer' , 'brand' : 'Dell' ,
'price': '40000'}, {'name': 'Mobile', 'brand':
'Redmi', 'price': '15000'}]

Of other modules in xml package, xml. dom implements document object model of XML format and xm‘1. sax defines functionality to implement SAX model.

Python Data Persistence – plistlib Module

Lastly, we have a look at plist module that used to read and write ‘property list’ files (they usually have .plist’ extension). This type of file is mainly used by MAC OS X. These files are essentially XML documents, typically used to store and retrieves properties of an object.
The functionality of plastic module is more or less similar to other serialization libraries. It defines dumps () and loads () functions for the string representation of Python objects. The load () and dump () functions read and write plist disk files.
The following script stores a diet object to a plist file.

Example

import plistlib
proplist = {
"name" : "Ramesh",
"class":"XII",
"div":"B",
"marks" : {"phy":50, "che" 60, "maths":80}
}
fileName=open ('marks .plist' ' wb' )
plistlib . dump (proplist, fileName)
fileName . close ()

The load() function retrieves an identical dictionary object from the file.

Example

with open('marks.plist', 'rb') as fp:
p1 = plistlib.load(fp) 
print(p1)

Another important data persistence library in Python is the sqlite3 module. It deals with read/write operations on the SQLite relational database. Before we explore its functionality, let us get acquainted with RDBMS concepts and the basics of SQL, which is the next chapter.

Python Data Persistence – csv module

The Comma Separated Values (CSV) format is very widely used to import and export data in spreadsheets and RDBMS tables. The csv module, another built-in module in Python’s standard library, presents the functionality to easily convert Python’s sequence object in CSV format and write to a disk file. Conversely, data from CSV files is possible to be brought in Python namespace. The reader and writer classes are defined in this module that performs read/write operations on CSV files. In addition, this module also has DictReader and DxctWriter classes to work with Python’s dictionary objects.

The object of the writer class is obtained by the writer () constructor which needs a file object having ‘w’ mode enabled. An optional ‘dialect’ parameter is given to specify the implementation type of CSV protocol, which is by default ‘excel’ – the format preferred by MS Excel spreadsheet software. We are now in a position to write one or more rows to the file represented by the writer object.

Example

>>> import csv
>>> data=[('TV', 1 Samsung',25000) , ( 'Comput-
er1 'Dell40000),('Mobile', 'Redmi ',15000)]
> > > file=open (' pricelist .csv' , 'w' , newline='')
>>> obj =csv. writer (file)
>>> #write single row
>>> obj.writerow(data[0])
>>> #write multiple rows
> > > obj.writerows(data [1:])
> > > file . close ( )

Note that, open( ) function needs the newline=’ ‘ parameter to correctly interpret newlines inside quoted fields. The ‘pricelist.csv’ should be created in the current working directory. Its contents can be verified by opening in any text editor, as per your choice.
The reader object, on the other, hand returns an iterator object of rows in the CSV file. A simple for loop or next() function of an iterator can be used to traverse all rows.

Example

>>> file=open ('pricelist .csv' , ' r ' newline=' ' )
>>> obj =csv. reader (file)
>>> for row in obj:
print (row)
['TV', 'Samsung', 25000']
['Computer', 'Dell , '40000']
['Mobile', 'Redmi' '15000']
>>>

The csv module offers powerful DictWriter and DictReader classes that can deal with dictionary objects. DictWriter maps the sequence of dictionary objects to rows in the CSV file. As always, the DictWriter constructor needs a writable file object. It also needs a fieldnames parameter whose value has to be a list of fields. These fields will be written as a first row in the resultant CSV file. Let us convert a list of tuples, in the above example, to a list of diet objects and send it to csv format using the DictWriter object.

Example

>>> data=[{'product':'TV', 'brand1:1 Samsung ','price':25000}, {'product':'Computer','br and':'Dell','price':40000},{'product':'Mo¬bile ', 'brand' :'Redmi', 'price' :15000 } ]
>>> file=open (' pricelist. csv' , ' w' , newline= ' ')
>>> fields=data [0] .keys()
>>> obj =csv.DictWriter (file,fields)

The DictWriter’s write header () method uses fieldnames parameter to write header row in CSV file. Each row following the header contains the keys of each dictionary item.

Example

>>> obj.writeheader() 
>>> obj.writerows(data)
>>> 'file . close ()

The resulting ‘pricelist.csv’ will show data, as follows:

Example

product,brand,price 
TV,Samsung, 2 5000 
Computer,Dell ,4 0000 
Mobile,Redmi,15000

Reading rows in dictionary formation is easy as well. The DictReader object is obtained from the source CSV file. The object stores strings in the first row in field name attributes. A simple for loop can fetch subsequent rows. However, each row returns an OrderedDict object. Use diet () function to obtain a normal dictionary object out of each row.

Example

>>> file=open ('pricelist. csvr ,newline='')
>>> obj=csv.DictReader (file)
>>> obj .fieldnames
['product1, 'brand', 'price']
>>> for row in obj:
print (diet(row))
{'product': 'TV', 'brand': 'Samsung', price': '25000'}
{'product': 'Computer', 'brand' 'Dell ' , ' price' : '40000'}
{'product': 'Mobile', 'brand': Redmi' 'price': ' 15000'}

 

Python Data Persistence – dbm Modules

6.3 dbm Modules

These modules in Python’s built-in library provide a generic dictionary-like interface to different variants of DBM style databases. These databases use binary encoded string objects as key, as well as value. The dbm. gnu module is an interface to the DBM library version as implemented by the GNU project. On the other hand, dbm.ndbm module provides an interface to UNIX nbdm implementation. Another module, dbm. dumb is also present which is used as a fallback option in the event, other dbm implementations are not found. This requires no external dependencies but is slower than others.

Example

>>> import dbm
> > > db=dbm.open(1mydbm.db' , 'n' )
>>> db[1 title']=1 Introduction to Python'
>>> db['publisher 1] = 'BPB'
>>> db[1 year'] = '2 019 1
>>> db.close( )

As in the case of shelve database, user-specified database name carries ‘.dir’ postfix. The dbm object’s whichdb( ) function tells which implementation of dbm is available on the current Python installation.

Example

>>> dbm.whichdb('mydbm.db')
'dbm.dumb'

The open() function allows mode these flags: ‘c’ to create a new database with reading/write permission, ‘r’ opens the database in read-only mode, ‘w’ opens an existing database for writing, and ‘n’ flag always create a new empty database with read/write permissions.
The dbm object is a dictionary-like object, just like a shelf object. Hence, all dictionary operations can be performed. The following code opens ‘mydbm.db’ with ‘r’ flag and iterates over the collection of key-value pairs.

Example

> > > db=dbm.open('mydbm.db', 'r')
>>> for k,v in db.items():
print (k,v)
b'title' : b'Introduction to Python'
b'publisher' : b'BPB'
b'year' : b'2019'

 

Python Data Persistence – shelve Module

Serialization and persistence affected by functionality in this module depend on the pickle storage format, although it is meant to deal with a dictionary-like object only and not with other Python objects. The shelve module defines an all-important open( ) function that returns the ‘shelf object representing the underlying disk file in which the ‘pickled’ dictionary object is persistently stored.

Example

>>> import shelve
>>> obj =shelve.open('shelvetest1)

In addition to the filename, the open( ) function has two more optional parameters. One is ‘flag’ which is by default set to ‘c’ indicating that the file has read/write access. Other accepted values for flag parameters are ‘w’ (write-only), ‘r’ (read-only), and ‘n’ (new with reading/write access). The second optional parameter is ‘writeback’ whose default value is False. If this parameter is set to True, any modification made to the shelf object will be cached in the memory and will only be written to file on calling sync () or close ( ) methods, which might result in the process becoming slow.

Once a shelf object is declared, you can store key-value pair data to it. However, the shelf object accepts only a string as the key. Value can be any valid Python object.

Example

>>> obj ['name'] = 'Virat Kohli'
>>> obj ['age']=29
>>> obj ['teams']=['India', 'IndiaU19', 'RCB', 'Delhi']
>>> obj.close( )

In the current working directory, a file named ‘shelveset.dir’ will store the above data. Since the shelf is a dictionary-like object, it can invoke familiar methods of built-in diet class. Using the get () method, one can fetch a value associated with a certain key. Similarly, the update () method can be used to add/modify k-v pairs in shelf objects.

Example

>>> obj.get('name')
'Virat Kohli'
>>> dct = { '100s' :64, '50s' :69}
>>> obj.update(dct)
>>> diet(obj)
{'name': 'Virat Kohli', 'age': 29, 'teams':
['India', 'IndiaU19', 'RCB', 'Delhi'], '100s': 64,' 50s' : 69}

The shelf object also returns views of keys, values, and items,same as the built-in dictionary object.

Example

>>> keys=list(obj.keys())
>>> keys
['name', 'age', 'teams', '100s', '50s']
>>> values=list(obj.values() )
>>> values
['Virat Kohli', 29, ['India' , 'IndiaU19' 'RCB', 'Delhi’], 64, 69]
>>> items=list(obj.items())
>>> items
[('name', 'Virat Kohli'), (' age 1, 29), ( teams',
['India', ’IndiaU19', 'RCB', 'Delhi']), ' 100s ' , 64), ( ' 50s 1 , 69)]

 

Python Data Persistence – pickle Module

The serialization format used by the pickle module, which is a part of Python’s built-in module library, is very Python-specific. While this fact can work as an advantage that it doesn’t face any restrictions by certain external standards such as JSON format, the major disadvantage is that non-Python applications may not be able to reconstruct ‘pickled’ objects. Also, the pickle module is not considered secure when it comes to unpickling data received from an unauthenticated or untrusted source.

The pickle module defines the module-level dumps ( ) function to obtain a byte string ‘pickled’ representation of any Python object. Its counterpart function loads () reconstructs (‘unpickles’) the byte string identical Python object.

Following code snippet demonstrates the use of dumps () and loads () functions:

Example

>>> import pickle
>>> numbers=[10,20,30,40]
>>> pickledata=pickle.dumps(numbers)
>>> pickledata
b'\x80\x03]q\x00(K\nK\xl4K\xleK(e.'
>>> #unpickled data
. . .
>>> unpickledata=pickle.loads(pickledata)
>>> unpickledata
[10, 20, 30, 40]
>>>

There are dump ( ) and load () functions that respectively write pickled data persistently to a file-like object (which may be a disk file, a memory buffer object, or a network socket object) having binary and write ‘wb’ mode enabled, and reconstruct identical object from a file-like object having ‘rb’ permission.

Example

>>> #pickle to file
. . .
>>> import pickle
>>> numbers=[ 10 , 20 , 30 , 40 ]
>>> file=open ('numbers .dat' , ' wb ')
>>> pickle . dump (numbers, file)
>>> file . close ()
>>> #unpickle from file
. . .
>>> file=open {1 numbers . dat' , ' rb' )
>>> unpickledata=pickle . load (file)
>>> unpickledata
[10, 20, 30, 40]
>>>

Almost any type of Python object can be pickled. This includes built-in types, built-in, and user-defined functions, and objects of user-defined classes.

The pickle module also provides object-oriented API as a substitute for module-level dumps () /loads () and dump () /load () functions. The module has a pickier class whose object can invoke dump () or dumps () method to ‘pickle’ an object. Conversely, the unpicker class defines load () and loads () methods.

Following script has a person class whose objects are pickled in a file using pickier class. Original objects are obtained by the load () method of unpicker class.

Example

from pickle import Pickier, Unpickler
class User:
def__init__(self,name, email, pw):
self.name=name
self.email=email
self.pw=pw
def__str__(self):
return ('Name: { } email: { } password: {}'. \ format(self.name, self.email, self.pw))
user1=User('Raj an', '[email protected]', 'rajanl23')
user2=User('Sudheer', '[email protected]', 's 11')
print ('before pickling..')
print (user1)
print (user2)
file=open (' users . dat' , ' wb' )
Pickier (file) .dump (userl)
Pickier (file) .dump(user2)
file.close ()
file=open ( ' users . dat' , ' rb ' )
obj 1=Unpickler (file) . load ()
print ('unpickled objects')
print (obj1)
obj2=Unpickler (file) . load()
print (obj2)

Output:

E:\python37>python pick1-example.py before pickling.
Name: Rajan email: rl23w-gmail.com password: rajanl23 
Name: Sudheer email: s.lKwgmail.com password: s_ll unpickled objects
Name: Rajan email: rl2 3wgmail.com password: rajanl23 
Name: Sudheer email: s.llwgmail.com password: s_ll E:\python3 7 >

Python Data Persistence – SELECT Statement

This is one of the most frequently used SQL statements. The purpose of the SELECT statement is to fetch data from a database table and return it in the form of a result set. In its simplest form SELECT statement is used as follows:

Example

SELECT coll, col2, .., coin FROM table_name;

SQLite console displays data from the named table for all rows in specified columns. SQLite console offers two useful ‘dot’ commands for a neat and formatted output of the SELECT statement. The ‘.header on’ command will display the column names as the header of output. The ‘.mode column’ command will force the left alignment of data in columns.

sqlite> .header on
sqlite> .mode column
sqlite> select name as name, price from products;
name       Price
---------    --------
Laptop   25000
TV          40000
Router   2000
Scanner 5000
Printer   9000
Mobile  15000

You. can use wild card character to indicate all columns in the table.

sqlite> .header on 
sqlite> .mode column 
sqlite> select * from products;
ProductID   Name     Price
 ----------    -------   --------   
       1          Laptop    25000
       2           TV         40000
       3           Router   2000
       4          Scanner   5000
       5          Printer     9000
       6         Mobile     15000

The ORDER BY clause lists selected rows according to ascending order of data in the specified column. The following statement displays records in the Products table in ascending order of price.

sqlite> select * from products order by price;
ProductID          Name              Price
-----------          ---------         ---------     
      3                  Router             2000
      4                  Scanner           5000
      5                  Printer            9000
      6                  Mobile           15000
      1                  Laptop           25000
      2                  TV                  40000

To enforce descending order, attach ‘DESC’ to the ORDER BY clause.

sqlite> select * from products order by name desc;
ProductID             Name              Price
-----------             --------            --------      
       2                     TV                  40000
       4                     Scanner          5000
       3                     Router            2000
       5                     Printer            9000
       6                     Mobile           15000
       1                     Laptop           25000

You can apply the filter on the selection of rows by using the WHERE clause. The WHERE keyword is followed by a logical condition having logical operators (<, >, <=, >=, =, IN, LIKE, etc.). In the following example, only those rows will be selected for which value of the ‘price’ column is less than 10000.

sqlite> select * from products where price<10000;
ProductID            Name           Price
------------         ---------         --------      
      3                    Router          2000
      4                    Scanner        5000
      5                    Printer          9000

A big advantage of the relational model comes through when data from two related tables can be fetched. In our ‘Invoices’ table, we have ProductID as one of the columns that are a primary key of the ‘Products’ table. The following example uses the WHERE clause to join two tables – Invoices and Products – and fetch data from them in a single SELECT statement.

sqlite> select InvID, Products. name, Products.Price,
Quantity 
. . .> from invoices, , Products where invoices.
productID= Products.ProductID; 
InvID            Name           Price             Quantity
-------          --------       ----------         --------
 1                Laptop         25000                 2
  2                 TV               40000                 1
  3                Mobile         15000                 3
  4                Mobile         15000                 1
  5                Printer          9000                   3
  6                   TV              40000                5
  7                Laptop          25000                4
  8                Router          2000                  10
  9                Printer          9000                   2
 10               Scanner        5000                   3

It is also possible to generate a calculated column depending on some operation on other columns. Any column heading can also be given an alias name using AS keyword.
Following SELECT statement displays Total column which is Products. Price*Quantity. The column shows values of this expression is named AS Total.

sqlite > select InvID, Products.: name t Products.
Price, Quantity, Products.Price *Quantity as
Total 
> from invoices, Products where
invoices.productID=Products.ProductID;
InvID            Name            Price            Quantity           Total 
--------        ---------        ----------       -----------      --------
   1            Laptop             25000                2                  50000
   2               TV                 40000                1                  40000
   3            Mobile             15000                3                  45000
   4            Mobile             15000                1                  15000
   5            Printer              9000                 3                   27000
   6             TV                   40000               5                   200000
  7            Laptop              25000               4                   100000
  8            Router               2000                10                   20000
  9            Printer               9000                 2                   18000
 10           Scanner             5000                 3                   15000

 

Python Data Persistence – UPDATE Statement

It is possible to modify data of a certain field in a given table using the UPDATE statement. The usage syntax of the UPDATE query is as follows:

Example

UPDATE table_name SET coll=vall, col2=val2,.., colN=valN WHERE [expression] ;

Note that the WHERE clause is not mandatory when executing the UPDATE statement. However, you would normally want to modify only those records that satisfy ing a certain condition. If the WHERE clause is not specified, all records will be modified.

For example, the following statement changes the price of ‘Printer’ to 1000Q.

sqlite> update products set price=10000 where name='Printer';
sqlite> select * from products;
Product ID                Name                 Price
  ----------                 -----------          ------  
      1                         Laptop              25000
      2                          TV                    40000
      3                         Router               2000
      4                         Scanner             5000
      5                         Printer              10000
      6                         Mobile             15000

However, if you want to increase the price of each product by 10 percent, you don’t have to specify the WHERE clause.

sqlite> update products set price=price+price*10/100;
sqlite> select * from products;
ProductID             Name                Price
 ----------             ----------          -------  
      1                      Laptop              27500
      2                      TV                     44000
      3                      Router                2200
      4                     Scanner               5500
      5                     Printer                11000
     6                      Mobile                16500