Threaded Mode | Linear Mode

codecaine · (This post was last modified: 01-16-2010 11:31 AM by codecaine.)

This uses the universal sqlite database which is opensource and can be used on most programming languages. http://www.activestate.com/activepython/ this is a nice python version that already have win32api modules, sqlite, and other modules.

Code:

import sqlite3

import os;

import sys;

#############################################

# This program was coded by Jerome Scott II #

#############################################

def add_record():

    # Reopen the database and read out all the rows

    db = sqlite3.connect('temp.db')

    #input for a new record in the database

    name = raw_input("name: ")

    age = int(raw_input("age: "))

    #check if input

    if name.strip() == '':

        print 'invalid record entered'

        sys.exit(1)

    #sql string to insert in the database

    sqlStr = "INSERT into identity VALUES(\'" + name + "\', " + str(age) + ")"

    #execute sql command

    db.execute(sqlStr)

    #commit to changes

    db.commit()

    #close database

    db.close()

def sql_select():

    # Reopen the database and read out all the rows

    db = sqlite3.connect('temp.db')

    #input for a new record in the database

    sqlStr = raw_input("SQL SELECT: ")

    #check if the word select is in string

    if not sqlStr.startswith('SELECT'):

        print 'invalid select sql command must start with SELECT in all caps'

        sys.exit(1)

    #execute sql command

    print db.execute(sqlStr).fetchall()

    #execute sql command

    db.execute(sqlStr)

    #commit to changes

    db.commit()

    #close database

    db.close()

def sql_delete():

    # Reopen the database and read out all the rows

    db = sqlite3.connect('temp.db')

    #input for a new record in the database

    sqlStr = raw_input("SQL DELETE: ")

    #check if the word select is in string

    if not sqlStr.startswith('DELETE'):

        print 'Invalid delete command. The String must start with DELETE in all caps'

        sys.exit(1)

    #execute sql command

    print 'Delete: ' + str(db.execute(sqlStr).rowcount) + ' records'

    #execute sql command

    db.execute(sqlStr)

    #commit to changes

    db.commit()

    #close database

    db.close()

def menu():

    while True:

        print '1. Add to database'

        print '2. Delete from database'

        print '3. Select from database'

        print '4. Exit program'

        choice = int(raw_input('Enter choice 1-4: '))

        if choice == 1:

            add_record()

            print 'record added'

        elif choice == 2:

            sql_delete()

        elif choice == 3:

            sql_select()

        elif choice == 4:

            sys.exit(0)

        else:

            print '\nError: invalid options entered'

if not os.path.exists('temp.db'):

    # Create a database

    db = sqlite3.connect('temp.db')

    db.execute("CREATE TABLE IF NOT EXISTS identity(name text, age int)")

    db.commit()

menu();

codecaine · (This post was last modified: 01-16-2010 11:34 AM by codecaine.)

Table name is identity and column names are name & age
Here is a example of usage:
Option 1. pretty much does everything for you.
Option 2. You use a sql command to delete what you want. Here are some examples:
#This will delete all records that ages are greater then 10
DELETE FROM identity WHERE age > 10

#This delete any records that have the name mike
DELETE FROM identity WHERE name = 'jerome'

Option 3.
#print all records in the database
SELECT * FROM identity

#print all records that age is greater then 10
SELECT * FROM identity WHERE age > 10

#print all record names
SELECT name FROM identity

#print all records that have the name mike
SELECT * FROM identity WHERE name = 'mike'

option 4. explains itself.

Go here to see how to do delete and select sql commands
http://www.1keydata.com/sql/sql-commands.html

***drdebcol*** · 01-16-2010, 08:59 PM

Very good. I suppose that is in sqlite3 library. Python is so fast in this segment !

codecaine · 01-17-2010, 01:24 AM

yea I forgot that sqlite3 is a standard module in python also Smile