Working With Database

Database driven applications account for a large part of all applications ever developed. And they will definitely in the future as well. Most of them are business applications. Companies work with large amount of data and they naturally need software for that. Well, you know, we live in a era called information revolution after all.

Database is a structured collection of data that is stored in a computer. A computer program, that manages and queries a database is calles a Database Management System (DBMS). Some thirty years ago, DBMS were available only in the research laboratories of giant companies like IBM. Later on, they began to spread. But they were very expensive. These days, we can found DBMS everywhere. On the web, on our personal computers, in various mobile devices or portables. We can have many different databeses for little or no money that would cost thousands of dollars in the past. We live in interesting times.

There are various database models. The most significant database model is the relational database model (RDBMS). The data is divided into tables. Among these tables we define relations. We all have heard about various database management systems. There are several well known commercial DBMS as well as open source ones.

Commercial RDBMS Oracle Sybase MS SQL Access

Opensource RDBMS MySQL PostgreSQL Firebird SQLite

Python programming language has modules for all above RDBMS.

SQLite

Starting from Python 2.5.x series, an SQLite library is included in the python language. SQLite is a small embeddable library. This means that programmers can integrate the libraty inside their applications. No server is needed to work with SQLite. Therefore SQLite is also called a zero-configuration SQL database engine.

SQLite has the following features.

• works with transactions
• no administration needed
• small code footprint, less than 250 KB
• simple to use and fast
• single file database structure
• supports databases up to 2 tebibytes (2⁴¹ bytes) in size

SQLite supports these data types:

• TEXT
• INTEGER
• FLOAT
• BLOB
• NULL

Before we start working with SQLite, we define some important terms. A database query is a search for information from a database. A query is written in SQL language. Structured Query Language (SQL) is a computer language used to create, retrieve, update and delete data from the database. It was developed by the IBM corporation. SQL language has three subsets.

• DML
• DDL
• DCL

The DML (Data Manipulation Language) is used to add, update and delete data. SQLite understands insert, update and delete sql commands. The DDL (Data Definition Language) is used to define new tables and records. SQLite has create, drop, alter sql commands from this group. The DCL (Data Control Language) is used to set privileges for database users. SQLite does not have this subset.

A cursor is a database object used to traverse the results of a SQL query. A transaction is a unit of operation with a database management system. It can contain one or more queries. Transactions are used to ensure the integrity od data in a database. If everything is ok, transactions are commited. If one or more queries fails, transactions are rolled back. Databases that support transactions are called transactional databases. An SQLite database is a transactional database. An SQL result set is a set of rows and metadata about the query from a database. It is a set of records that results from running a query. A single unit of structured data within a database table is called a record or a row.

Below are the SQlite Phython API

import sqlite3 as sqlite
conn=sqlite.connect(':memory:')
cur=conn.cursor( )
cur.execute("create table neighbours(name text, age numeric, remark text)")
cur.execute("insert into neighbours values('sandy', 7, 'stubborn')")
#cur.commit( )
cur.execute("insert into neighbours values('jane', 18, 'beautiful')")
cur.execute ("insert into neighbours values('mark', 28, 'lazy')")
cur.execute("insert into neighbours values('steven', 34, 'friendly')")
cur.execute("insert into neighbours values('alice', 17, 'slick')")
cur.execute("insert into neighbours values('tom', 25, 'clever')")
cur.execute("insert into neighbours values('jack', 89, 'wise')")
cur.execute("insert into neighbours values('lucy', 18, 'cute')")
cur.execute("SELECT * FROM neighbours")
print cur.fetchall( )
cur.execute("SELECT name FROM neighbours")
print cur.fetchall( )
cur.execute("SELECT age FROM neighbours WHERE name='lucy'")
print cur.fetchall( )
#UPDATE
cur.execute("UPDATE neighbours set age=29 WHERE name='lucy'")
conn.commit( )
cur.execute("SELECT age FROM neighbours WHERE name='lucy'")
print cur.fetchone( )
#CREATE ANOTHER TABLE
cur.execute('create table relatives(name text, age numeric)')