Database Manager : SQLiteSpy

SQLiteSpy is a fast and compact GUI database manager for SQLite. It reads SQLite3 files and executes SQL against them. Its graphical user interface makes it very easy to explore, analyze, and manipulate SQLite3 databases.

readmore

A simple SQLite Database with GUI, easy to manage, create and save SQL. Able to retrieve 500,000 rows within 5 minutes.

SQLite- Fetch

Fetch is a SQlite function, it writes in fetch( ). The purpose of this function is to

Return a result record of the query

For example:

1. fetchall( ) - return all as a tuple

for row in cur.fetchall( ):
print "name",row[0], "address",row[1]

2. fetchone( ) - return first row

for row in cur.fetchone( ):
print "............................

3. fetchmany( size) - return row according to size

for row in cur.fetchmany(200)
print "%s | %s |%s" %row

How To Create VIEW

In Database, VIEW is a virtual table / derived TABLE.The CREATE VIEW command assigns a name to a pre-packaged SELECT statement. Once the view is created, it can be used in the FROM clause of another SELECT in place of a table name.

sql-command ::= CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] [database-name.] view-name AS select-statement

On the other hand VIEW is a TABLE from MASTER TABLE in DATABASE

import sqlite3 as sqlite
#Connect to MASTER TABLE
conn=sqlite.connect('abcd.db')
cur=conn.cursor()
#execute SQL
cur.execute('CREATE VIEW new AS SELECT name,date FROM people')
for row in cur:
## print '---------------'
## print 'name:',row[0]
## print 'date:',row[1]
cur.close()
#Execute SQL from same Database but on different TABLE
conn=sqlite.connect('abcd.db')
cur.execute('SELECT * FROM new')
for row in cur:
print '--------------------'
print 'name:',row[0]
print 'date',row[1]
print '--------------------'

The output

-----------------------------
name: John
date: 21/3/2008
-----------------------------

How to print database into a table using pysqlite

from pysqlite2 import dbapi2 as sqlite

FIELD_MAX_WIDTH = 20
TABLE_NAME = 'people'
SELECT = 'select * from %s order by age, name_last' % TABLE_NAME

con = sqlite.connect("mydb")

cur = con.cursor()
cur.execute(SELECT)

# Print a header.
for fieldDesc in cur.description:
print fieldDesc[0].ljust(FIELD_MAX_WIDTH) ,
print # Finish the header with a newline.
print '-' * 78

# For each row, print the value of each field left-justified within
# the maximum possible width of that field.
fieldIndices = range(len(cur.description))
for row in cur:
for fieldIndex in fieldIndices:
fieldValue = str(row[fieldIndex])
print fieldValue.ljust(FIELD_MAX_WIDTH) ,

print # Finish the row with a newline.

The output is

name_last            age
------------------------------------------------------------------------------
Putin                51
Lebed                53
Zhirinovsky          57
Yeltsin              72

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)')