Author name: Prasanna

Python Data Persistence – ORM – Relationships

In the case of raw SQL, we establish relationships among tables using the FOREIGN KEY constraint. This section describes how relationships are built between tables and mapped classes.

In the previous chapter, our mydb.SQLite database contained an Invoices table that had a relationship with the ‘Products’ and ‘Customers’ table. These relationships were established with foreign keys. We shall now declare Invoice class (that maps Invoices table) and relate it to Product class and Customer class with the help of ForeignKey ( ) function imposed on ProductID and CustID columns in it. This is very similar to the definition of a table in raw SQL.

Example

from sqlalchemy import ForeignKey 
class Invoice(base): 
__tablename__='Invoices' 
‘InvID=Column(Integer, primary_key=True) 
CustID=Column(Integer, ForeignKey(1 Customers. CustID')) 
ProductID=Column(Integer, ForeignKey('Products. ProductID')) 
quantity=Column(Integer)

However, this will not establish a relationship amongst classes. SQLAlchemy’s ORM provides a relationship ( ) function for this purpose. In the Invoice class, ‘prod’ is a relationship property that sets uplink with the Product class, and ‘ c s t ’ attribute is a relationship that establishes a relation between Invoice and Customer class.

prod=relationship("Customer", back_ populates="Invoices") 
cst=relationship("Product", back_ populates="Invoices")

The ‘ back_populates ‘ parameter indicates that ‘prod’ and ‘c.s/’ properties have to be placed on the related mapped classes (Product and Customer respectively) that will handle this relationship in the other direction. The backward relationship is established by the following directives:

Example

Product.Invoices=relationship('Invoice', order_ by=Invoice.InvID, back_populates='cst') 
Customer.Invoices=relationship('Invoice 1, order_ by=Invoice.InvID, back_populates='prod1)

The complete code of the Invoice class is given below. Add it to my classes. py script and recreate the metadata schema by executing create_ all (engine) function.

Example

from sqlalchemy import ForeignKey 
from sqlalchemy.orm import relationship 
class Invoice(base): 
__tablename__='Invoices' 
InvID=Column(Integer, primary_key=True) 
CustID=Column(Integer, ForeignKey('Customers. CustID1)) 
ProductID=Column(Integer, ForeignKey('Products. ProductID')) 
prod=relationship("Customer", back_ populates="Invoices") 
cst=relationship("Product", back_ populates="Invoices") 
quantity=Column(Integer) 
Product.Invoices=relationship('Invoice' , order_ by=Invoice.InvID, back_populates='cst') 
Customer.Invoices=relationship('Invoice' , order_ by=Invoice.InvID, backj?opulates='prod')

The structure of the newly created Invoices table will be echoed on the command terminal:

Example

PRAGMA table_info("Invoices") 
( ) 
CREATE TABLE "Invoices" ( 
"InvID" INTEGER NOT NULL, 
"CustID" INTEGER, 
"ProductID" INTEGER, quantity INTEGER, 
PRIMARY KEY ("InvID"), 
FOREIGN KEY("CustID") REFERENCES "Customers" ("CustID"), 
FOREIGN KEY("ProductID") REFERENCES "Products" ("ProductID") 
)
 ( ) 
COMMIT

Using the Session object, we can now add data in this table as follows:

Example

from sqlalchemy.orm import sessionmaker 
Session = sessionmaker(bind=engine) 
sessionobj = Session() 
il=Invoice(InvID=l, CustID=l, ProductID=l, quantity=2) 
sessionobj.add(il) 
sessionobj.commit()

Likewise, you can add the rest of the records as given in sample data in the previous chapter.

Python Data Persistence – Querying related tables (ORM)

The query object we used earlier in this chapter can act on more than one mapped class. By equating relating columns in two tables using filter ( ) function we can simulate implicit join affected by WHERE clause in SQL syntax.

The snippet gives the blow, we display the name of the product and its price of ProductID in the Invoices table. The filter ( ) establishes join on the basis of equal values of ProductID in Invoices and Products tables.

Example

from sqlalchemy import Column, Integer, String 
from sqlalchemy import create_engine, and_, or_ 
from myclasses import Product,base, Customer, 
Invoice 
engine = create_engine('sqlite:///mydb.sqlite1, echo=True) 
base.metadata.create_all(engine) 
from sqlalchemy.orm import sessionmaker 
Session = sessionmaker(bind=engine) 
sessionobj = Session() 
q=sessionobj.query(Invoice,Product) 
for i,p in q.filter(Product.ProductID==Invoice. 
ProductID).all():
print (i.InvID, p.name, p.price, i.quantity)

The equivalent SQL expression emitted by SQLAlchemy will be echoed as follows:

Example

SELECT "Invoices"."InvID" AS "Invoices_InvID", "Invoices"."CustID" AS "Invoices_CustID",
"Invoices"."ProductID" AS "Invoices_ProductID",
"Invoices".quantity AS "Invoices_quantity",
"Products"."ProductID" AS "Products_ProductID", "Products".name AS 
"Products_name", "Products".price AS "Products_price"
FROM "Invoices", "Products"
WHERE "Products"."ProductID" =
"Invoices"."ProductID"

The output of the above script:

1 Laptop 25000 2
2 TV 40000 1
3 Mobile 15000 3
4 Laptop 25000 6
5 Printer 9000 3
6 TV 40000 5
7 Laptop 25000 4
8 Router 2000 10
9 Printer 9000 2
10 Scanner 5000 3

To join the ‘Invoices’ table with the ‘Customers’ table and display the name of the corresponding Customer as well, add another condition in the filter – equating their CustID columns). Change the looping statement as follows:

Example

print ("InvID,Customer,Product,Price,Quantity") 
for i,p,c in q.filter \ 
(and_(Product.ProductID==Invoice.ProductID, \ 
Customer.CustID==Invoice.CustID)).all( ): 
print ( ' { } , { } , { } , { } ' . format(i.InvID, 
c.name,p.name, p.price, i.quantity))

Set the echo parameter to False and run the script to obtain the following result:

InvID,Customer,Product,Price,Quantity
1 , Ravikumar , Laptop , 25000
2 , John , TV , 40000
3 , Divya , Mobile , 15000
4 , Nair , Laptop , 25000
5 , John , Printer , 9000
6 , Patel , TV , 40000
7 , Patel , Laptop , 25000 
8 , Shah , Router , 2000
9 , Irfan , Printer , 9000 
10 , Nitin , Scanner , 5000

As mentioned at the beginning of this chapter, SQLAlchemy defines schema-specific SQL Expression Language which is at the core of the domain-centric ORM model. The functionality of Expression Language is closer to raw SQL than ORM which offers a layer of abstraction over it. In the following section, a brief overview of SQLAlchemy’s Expression Language is covered.

Python Data Persistence – Core – Inserting Records

Next, is how to insert a record in this table? For this purpose, use the insert ( ) construct on the table. It will produce a template INSERT query.

Example

>>> ins=Products.insert()
>>> str(ins)
'INSERT INTO "Products" ("ProductID", name, "Price")
VALUES (:ProductID, :name, :Price)'

We need to put values in the placeholder parameters and submit the ins object to our database engine for execution.

Example

ins.values(name="Laptop",Price=25000) 
con=engine.connect( )
con.execute(ins)

Selecting data from table is also straightforward. There is a select() function that constructs a new Select object.

Example

>>> s=Products.select()
>>> str(s)
'SELECT "Products"."ProductID", "Products".name,
"Products"."Price" \nFROM "Products"'

Provide this Select object to execute the () function. It now returns a result set from which one (fetch one) or all records (fetchall) can be fetched.

Example

>>> result=con.execute(s)
>>> for row in result:
... print (row)

Python Data Persistence – MongoDB – Update Document

Predictably, there is an update ( ) method available to the collection object. Just as in SQL UPDATE, the $set operator assigns updated value to a specified key. Its primary usage is, as below:

Example

db.collection.update({"key":"value"}, 
{$set:{"key":"newvalue"}})

For example, the following statement changes the price of ‘TV’ to 50000.

> db.products.update({"Name":"TV"}, 
{$set:{"price":50000}}) 
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

The WriteResult () confirms the modification. You can also use Boolean operators in the update criteria. To perform updates on multiple documents, use the update many ( ) method. The following command uses the $inc operator to increment the price by 500 for all products with ProductID greater than 3.

> db.products.updateMany({"ProductID":{$gt:3}}, 
{$inc:{"price":5 0 0}}) 
{ "acknowledged" : true, "matchedCount" : 2, "modifiedCount" : 2 }

Python Data Persistence – Core – Updating Records

SQLalchemy’s core API defines the update ( ) function which lets the value of one or more columns in one or more rows be modified.

table.update( ).where(condition).values(Col=newval)

For example, to update the price of the TV to 45000, use

qry=Products.update().where(Products.c.name=="TV"). 
values(name=45000) 
con.execute(qry)

Similarly, to delete a certain record from the table, use the following statement:

qry=Products.delete().where(Products.c.name='TV') con.execute(qry)

At the outset, there appears to be some overlap among the usage patterns of the ORM and the Expression Language. However, the similarity is rather superficial. ORM approach is from the perspective of a user-defined domain model. SQL Expression Language looks at it from the perspective of literal schema and SQL expression representations.

While an application may use either approach exclusively, sometimes, in advanced scenarios, it may have to make occasional usage of the Expression Language directly in an otherwise ORM-oriented application where specific database interactions are required.

SQLAlchemy library is extensively used in Python-based web frameworks such as Flask and bottle. There are Flask-SQLAlchemy and Bottle_ SQLAlchemy extensions specifically written for them. Other popular ORM libraries are SQLObject and Django ORM.

Python Data Persistence – SQLAlchemy Core

We have to use the same process (as used in ORM) to connect to the database i.e. using create-engine () function that returns Engine object.

from sqlalchemy import create_engine
engine = create_engine('sqlite:///mydb.sqlite',
echo=True)

If you plan to use any other database dialect, ensure that you install its respective DB-API module.

engine = create_engine('mysql+pymydsql://root@ localhost/mydb')

In order to create tables in this database, first, we have to set up a MetaData object which stores table information and other scheme-related information.

from sqlalchemy import MetaData 
meta=MetaData( )

The Table class in sqlalchemy module needs this metadata object as one of the arguments to its constructor.

TableName=Table ("name", meta, Columnl, Column2, ...)

As we have used before, the Column object represents a column in the database table and needs its name, data type, and constraints (if any) to be specified.

Example

from sqlalchemy import create_engine, MetaData,
Table, Column, Integer, String
engine = create_eng'ine ('sqlite:///mydb. sqlite1 ,
echo=True)
meta=MetaData()
Products = Table('Products', meta,
Column('ProductID', Integer, primary_key=True),
Column('name', String), Column('Price', Integer), ) meta.create_all(engine)

The create_all ( ) function emits equivalent SQL query as follow:

Example

CREATE TABLE "Products" (
"ProductID" INTEGER NOT NULL, name VARCHAR,
"Price" INTEGER,
PRIMARY KEY ("ProductID")
)

Python Data Persistence – ORM – Update Data

Modifying attributes of an object is very easy in SQLAlchemy. First, you have to fetch the desired object, either by the primary key (using the get ( ) method) or by applying the proper filter. All you have to do is assign a new value to its attribute and commit the change.

Following code will fetch an object from the ‘Products’ table whose ProductJD=2 (Product name is TV and price is 40000 as per sample data)

Example

p=q.get(2) 
SELECT "Products"."ProductID" AS "Products_ ProductID", 
"Products".name AS "Products_name", 
"Products".price AS "Products_price" 
FROM "Products" 
WHERE "Products"."ProductID" =? 
2, )

Change the price to 45000 and commit the session.

p.price=45000 
sessionobj.commit( )

SQLAlchemy internally executes the following UPDATE statement:

UPDATE "Products" SET price=? 
WHERE "Products"."ProductID" = ? 
(45000, 2)
 COMMIT

Python Data Persistence – ORM – Filter Criteria

The query object has a filter ( ) method that implements the WHERE clause as used in the raw SQL SELECT statement. The argument to filter can be any Boolean expression. In the following snippet, the filter is ‘price>20000’.

rows=q.filter (Product .price>20000)

This will translate into corresponding SQL statements as under:

Example

SELECT "Products"."ProductID" AS "Products_ ProductID", "Products". 
name AS "Products_name", "Products".price AS "Products_price" 
FROM "Products" 
WHERE "Products".price >?
(20000, )

SQLAlchemy supports the use of wild cards for filter operations on string columns. The LIKE keyword in SQL is implemented by applying a ( ) filter. Products. name. like (‘ %er’) filters rows with product name ending with ‘er’>

rows=q.filter (Product .name. like (1 %er') )

In effect above statement is equivalent to the following SQL query:

Example

SELECT "Products"."ProductID" AS "Products_ ProductID", 
"Products".name AS "Products_name", "Products".price AS "Products_price" 
FROM "Products" 
WHERE "Products".name LIKE ? 
(' %er' ,)

As you will expect, the following output will be displayed:

name: Router price: 2000 
name: Scanner Price: 5000 
name: Printer price: 9000

The filter ( ) can have AND/OR conjunctions implemented by and_ ( ) and or_( ).

Following filter returns products with a price between 10000 and 30000

from sqlalchemy 
import and_ rows=q.filter(and_
(Product.price>10000, Product. price<30000) )

Here is the generated SQL:

Example

SELECT "Products"."ProductID" AS "Products_ ProductID", 
"Products".name AS "Products_name", "Products" .price AS "Products__price" 
FROM "Products" 
WHERE "Products".price > ? AND "Products".price < ? 
(10000, 30000)

The OR operation is performed by the following statement

Example

from sqlalchemy import or_ 
rows=q.filter(or_(Product.price>20000, Product.name. like (' %er' ) ) )

which is equivalent to the following SQL statement:

Example

SELECT "Products"."ProductID" AS "Products_ ProductID", 
"Products".name AS "Products_name", "Products".price AS "Products_price" 
FROM "Products", 
"Customers" WHERE "Products".price <=? 
OR "Customers".name LIKE? 
(5000, '%er')

Python Data Persistence – ORM – Querying

In order to fetch data from a database table, we need to obtain a query object. The query () method is defined in the Session class. It needs the mapped class as an argument.

q=sessionobj.query(Product)

The query object itself has access to various methods to fetch rows from the underlying table and return objects of the mapped class.

The query .get ( ) method accepts the primary key as an argument and returns the corresponding object. For example, the following statement returns object with ProductID=2 (ProductID being the primary key of Products table)

p=q . get(2)

Because the ‘echo’ parameter is set to True in the Engine constructor, the console shows the corresponding SQL statement generated by SQLAIchemy as below:

Example

BEGIN (implicit) 
SELECT "Products"."ProductID" AS 
"Products_ ProductID", "Products".name AS "Products_name", "Products".price AS "Products_price" 
FROM "Products" 
WHERE "ProductsProductID" = ? sqlalchemy.engine.base.Engine (2,)

Attributes of the object (p.name and p.price) can now be displayed. Still better, provide a__str__( ) method in Products class. Modify it in myclasses.py script as under:

Example

class Product(base):
__tablename ___= 'Products'

ProductID = Column(Integer, primary_key=True)
name = Column(String)
price = Column(Integer)
def__str__(self):
return 'name:{ } price: { } ' . format ( self . name , self . price )

The query, all ( ) method returns a list of all objects which can be traversed using a loop. Here is a fetchllrecs.py script:

Example

#fetchallrecs.py 
from sqlalchemy import Column, Integer, String 
from sqlalchemy import create_engine 
from myclasses import Product,base, Customers 
engine = create_engine('sqlite:///mydb.sqlite', echo=True) 
base.metadata.create_all(engine) 
from sqlalchemy.orm import sessionmaker 
Session = sessionmaker(bind=engine) 
sessionobj = Session ( ) 
q=sessionobj.query(Products) 
rows=q.all ( ) 
for row in rows: 
print (row)

Shown below is the output of this script along with shortened log in the console window (figure 9.4):

(SQLAlchemyEnv) E: \ SQLA1chemyEnv>python 
fetchallrecs.py PRAGMA table info( BEGIN (implicit) "Products") 
SELECT "Products". "ProductID" AS "Products_ 
ProductID", "Products".name AS 
"Products_name", "Products".price AS 
"Products_price" FROM "Products"
 ( ) 
 name: Laptop price: 25000 
 name: TV price: 40000 
 name: Router price: 2000 
 name: Scanner Price : 5000 
 name: Printer price : 9000 
 name: Mobile price: 15000

Python Data Persistence – Python – Cassandra

In this last chapter, we are going to deal with another important NOSQL database – Cassandra. Today some of the biggest IT giants (including FaceBook, Twitter, Cisco, and so on) use Cassandra because of its high scalability, consistency, and fault-tolerance. Cassandra is a distributed database from Apache Software Foundation. It is a wide column store database. A large amount of data is stored across many commodity servers which makes data highly available.

Cassandra Architecture

The fundamental unit of data storage is a node. A node is a single server in which data is stored in the form of the keyspace. For understanding, you can think of keyspace as a single database. Just as any server running a SQL engine can host multiple databases, a node can have many key spaces. Again, like in a SQL database, keyspace may have multiple column families which are similar to tables.

However, the architecture of Cassandra is logically as well as physically different from any SQL-oriented server (Oracle, MySQL, PostgreSQL, and so on). Cassandra is designed to be a foolproof database without a single point of failure. Hence, data in one node is replicated across a peer-to-peer network of nodes. The network is called a data center, and if required, multiple data centers are interconnected to form a cluster. Replication strategy and replication factor can be defined at the time of the creation of a keyspace. (figure 12.1) ‘

Each ‘write’ operation over a keyspace is stored in Commit Log, which acts as a crash-recovery system. After recording here, data is stored in a Mem-table. Mem-table is just a cache or buffer in the memory. Data from the mem-table is periodically flushed in SSTables, which are physical disk files on the node.

Cassandra’s data model too is entirely different from a typical relational database. It is often, described as a column store or column-oriented NOSQL database. A keyspace holds one or more column families, similar to the table in RDBMS. Each table (column family) is a collection of rows, each of which stores columns in an ordered manner. Column, therefore, is the basic unit of data in Cassandra. Each column is characterized by its name, value, and timestamp.

The difference between a SQL table and Cassandra’s table is that the latter is schema-free. You don’t need to define your column structure ahead of time. As a result, each row in a Cassandra table may have columns with different names and variable numbers.

Installation

The latest version of Cassandra is available for download athttp://cassandra. apache.org/download/. Community distributions of Cassandra (DDC) can be found at https://academy.datastax.com/planet-cassandra/cassandra. Code examples in this chapter are tested on DataStax distribution installed on Windows OS.

Just as any relational database uses SQL for performing operations on data in tables, Cassandra has its own query language CQL which stands for Cassandra Query Language. The DataStax distribution comes with a useful front-end IDE for CQL. All operations such as creating keyspace and table, running different queries, and so on can be done both visually as well as using text queries. The following diagram shows a view of DataStax DevCenter IDE.(figure 12.3)

CQL Shell

Cassandra installation also provides a shell inside which you can execute CQL queries. It is similar to> MySQL console, SQLite console, or Oracle’s SQL Plus terminal. (figure 12.4)

We shall first learn to perform basic CRUD operations with Cassandra from inside CQLSH and then use Python API for the purpose.

Inserting Rows

INSERT statement in CQL is exactly similar to one in SQL. However, the column list before the ‘VALUES’ clause is not optional as is the case in SQL. That is because, in Cassandra, the table may have a variable number of columns.

cq1sh:mykeyspace> insert into products (productID, name, price) 
values (1, 1 Laptop 1,25000);

Issue INSERT statement multiple numbers of times to populate ‘products’ table with sample data given in chapter 9. You can also import data from a CSV file using the copy command, as follows:

cq1sh:mykeyspace> copy products (productID, name, price)
                             . . . from ’pricelist.csv’ with 
the delimiter,’ and header=true;