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

GoogleTalk on Python

Getting with Python Module

Introduction and Installation

5 minutes python

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

Find and delete string in text file

import sys
# input a original file
inp = open("december.txt","r")
#write to a new file
outp = open("newDecember.txt","w")
#declare global
lines = inp.readlines()
for line in lines:
#find the string
if(line.find("asfgsfhhshas")!=-1):
## print line
#write to a new file
sys.stdout.write(line)
outp.write(line)

inp = open("december.txt","r")
badf = open("badDecember.txt","w")
goodf = open("goodDecember.txt",'w')
lines = inp.readlines()
for line in lines:
if line.find("asfgsfhhshas") != -1:
sys.stdout.write(line)
badf.write(line)
else:
goodf.write(line)

badf.close()
goodf.close()
inp.close()

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

Executing a Python Source Code File

Note:

filename means you must replace the text filename with the name of the file including extension that you are using.
C:\Python23 is an example; replace C:\Python23 with the actual drive letter and directory where your Python is installed.

Command Line

• Open a Windows Console.
• Navigate to the drive and working directory of your choice.
• Enter one of the following commands to start the Python interpreter:
  • C:\Python23\python filename
    (The directory C:\Python23 is not in your PATH environment variable)
  • python filename
    (You have set your PATH variable such that the directory C:\Python23 is in your path)

Windows Explorer and File Associations

If standard Python file extensions like .py are associated with Python (the default for a standard Windows installation) then you can simply double-click a Python source file in Windows Explorer to run the script. There are 2 problems with this method:

• The output will display briefly in a console window, then the window will immediately close. You need to remember to put a "pause" input prompt at the end of your script to keep the window open, usually something like the following (See Input & Output below):

raw_input("Press ENTER to continue...")

• All Python scripts can easily be executed with a double-click by anyone curious about what the script does, including your unfinished and untested scripts. There are better ways (at least in my opinion) to make scripts executable:
  • Convert them to batch files with embedded Python script.
  • Move the script to a different folder and create a Windows shortcut to the script.

Python Interpreter

Using Command Prompt

The windows command prompt can be accessed via START/RUN/CMD

The phrase "Enter the command ..." means type the command at the prompt and press the Enter key.
C:\Python25 is an example; replace C:\Python25 with the actual drive letter and directory where your Python is installed.

• Open a Windows Console (a.k.a.. "DOS Window"; "Command Prompt Window")
• Navigate to the drive and working directory of your choice.
  See Command Line User Interface > Basic Navigation: www.mhuffman.com/notes/dos/dos.htm#NAV
• Enter one of the following commands to start the Python interpreter:
  • C:\Python25\python
    (The directory C:\Python25 is not in your PATH environment variable)

  • python
    (You have set your PATH variable such that the directory C:\Python25 is in your path)

• At the >>> prompt enter Python statements and expressions.
• If you see ... at the prompt instead of >>> it means Python is waiting for you to complete your statement. This happens when you are typing a compound statement (such as while or if).
• Enter Ctrl-Z to quit (Control key + the 'Z' key

Python.... what it is ?

Python is an interpreted language, even though there is a compile step in the translation from ASCII text source code to binary machine language. However, because the source code is interpreted the compile step occurs each time you execute a Python program, and no compiled machine-readable object file is created.

Python is a command line application; on Windows operating systems it runs in a Windows Console (a.k.a. Command Prompt window or DOS window). Your command to run Python must either include the full path to the Python interpreter OR you must include the full path to the Python interpreter in your PATH environment variable

variable

first_string = “This is a string”second_string = “This is another string”>>> first_number = 4
second_number = 5>
print “The first variables are %s, %s, %d, %d” % (first_string, second_string,first_number, second_number)The first variables are This is a string, This is another string, 4, 5

test! yes it works

minact, rest, thresh, decay, maxact = -0.2, -0.1, 0.0, 0.1, 1.0
alpha, gamma, estr = 0.1, 0.1, 0.4

units = []
pools = []
unitbyname = {}

class Unit(object):
__slots__ = ['name', 'pool', 'extinp', 'activation', 'output', 'exciters', 'newact']
def __init__(self, name, pool):
self.name = name
self.pool = pool
self.reset()
self.exciters = []
unitbyname[name] = self
def reset(self):
self.setext(0.0)
self._setactivation()
def setext(self, weight=1.0):
self.extinp = weight
def _setactivation(self, val=rest):
self.activation = val
self.output = max(thresh, val)
def addexciter(self, aunit):
self.exciters.append(aunit)
def remove(self, aunit):
self.exciters.remove(aunit)
def computenewact(self):
ai = self.activation
plus = sum(exciter.output for exciter in self.exciters)
minus = self.pool.sum - self.output
netinput = alpha*plus - gamma*minus + estr*self.extinp
if netinput > 0:
ai = (maxact-ai)*netinput - decay*(ai-rest) + ai
else:
ai = (ai-minact)*netinput - decay*(ai-rest) + ai
self.newact = max(min(ai, maxact), minact)
def commitnewact(self):
self._setactivation(self.newact)

class Pool(object):
__slots__ = ['sum', 'members']
def __init__(self):
self.sum = 0.0
self.members = set()
def addmember(self, member):
self.members.add(member)
def updatesum(self):
self.sum = sum(member.output for member in self.members)
def display(self):
result = sorted(((unit.activation, unit.name) for unit in self.members), reverse=True)
for i, (act, unitbyname) in enumerate(result):
print '%s: %.2f\t' % (unitbyname, act),
if i % 4 == 3: print
print '\n'

def load(filename):
"""Load in a database and interpret it as a network

First column must be unique keys which define the instance units.
Each column is a pool (names, gangs, ages, etc).
Every row is mutually excitory.
"""
units[:] = []
pools[:] = []
for line in open(filename):
relatedunits = line.split()
if not len(relatedunits): continue
key = len(units)
for poolnum, name in enumerate(relatedunits):
if poolnum >= len(pools):
pools.append(Pool())
pool = pools[poolnum]
if name in unitbyname:
unit = unitbyname[name]
else:
unit = Unit(name, pool)
units.append(unit)
pool.addmember(unit)
if poolnum > 0:
units[key].addexciter(unit)
unit.addexciter(units[key])

def reset():
for unit in units:
unit.reset()

def depair(i, j):
unitbyname[i].remove(unitbyname[j])
unitbyname[j].remove(unitbyname[i])

def touch(itemstr, weight=1.0):
for name in itemstr.split():
unitbyname[name].setext(weight)

def run(times=100):
"""Run n-cycles and display result"""
for i in xrange(times):
for pool in pools:
pool.updatesum()
for unit in units:
unit.computenewact()
for unit in units:
unit.commitnewact()
print '-' * 20
for pool in pools:
pool.display()

if __name__ == '__main__' or 1:
load('jets.txt')
touch('Ken', weight=0.8)
run()

reset()
touch('Sharks 20 jh sing burglar')
run()

reset()
touch('Lance')
depair('Lance','burglar')
run()

SampleFile = """
Art Jets 40 jh sing pusher
Al Jets 30 jh mar burglar
Sam Jets 20 col sing bookie
Clyde Jets 40 jh sing bookie
Mike Jets 30 jh sing bookie
Jim Jets 20 jh div burglar
Greg Jets 20 hs mar pusher
John Jets 20 jh mar burglar
Doug Jets 30 hs sing bookie
Lance Jets 20 jh mar burglar
George Jets 20 jh div burglar
Pete Jets 20 hs sing bookie
Fred Jets 20 hs sing pusher
Gene Jets 20 col sing pusher
Ralph Jets 30 jh sing pusher

Phil Sharks 30 col mar pusher
Ike Sharks 30 jh sing bookie
Nick Sharks 30 hs sing pusher
Don Sharks 30 col mar burglar
Ned Sharks 30 col mar bookie
Karl Sharks 40 hs mar bookie
Ken Sharks 20 hs sing burglar
Earl Sharks 40 hs mar burglar
Rick Sharks 30 hs div burglar
Ol Sharks 30 col mar pusher
Neal Sharks 30 hs sing bookie
Dave Sharks 30 hs div pusher
"""