How to build the pysqlite? Where to find the "sqlite3.h"?

[Shawn Milochik]

On Wed, Nov 5, 2008 at 8:52 PM, Kurda Yon <kurdayon at yahoo.com> wrote:
> By the where can I find a simle tutorial about the work with the
> "sqlite" from the Python?
> --
> http://mail.python.org/mailman/listinfo/python-list
>

Once you get the connection, you can pretty much just do whatever if
you know SQL. Here's some working code from one of my little projects:

#!/usr/bin/env python

from pysqlite2 import dbapi2 as sqlite

import time
#import sys

sqliteDatabase = "status.db"

connection = sqlite.connect(sqliteDatabase)
cursor = connection.cursor()
cursor.execute('CREATE TABLE IF NOT EXISTS auctions (auction_id
INTEGER PRIMARY KEY, numBids INTEGER, currentPrice INTEGER, timestamp
TEXT(50), itemTitle TEXT(100))')
connection.close()

local = time.localtime()
timeStamp = "%s-%02d-%02d_%02d:%02d" % (local[0], local[1], local[2],
local[3], local[4])

def recordExists(auctionID):

    connection = sqlite.connect(sqliteDatabase)
    cursor = connection.cursor()
    cursor.execute('SELECT COUNT(auction_id) FROM auctions WHERE
auction_ID = ' + auctionID.__str__())
    for row in cursor:
        return row[0]

    cursor.close()

    connection.close()

def retrieveAuctionInfo(auctionID):
    connection = sqlite.connect(sqliteDatabase)
    cursor = connection.cursor()
    cursor.execute('SELECT currentPrice, numBids    FROM auctions
WHERE auction_ID = ' + auctionID.__str__())
    for row in cursor:
        return [row[0],row[1]]

    cursor.close()

def insertAuction(auctionID, currentPrice, currentBids, itemTitle):

    itemTitle = itemTitle.replace('"', '""')

    connection = sqlite.connect(sqliteDatabase)
    cursor = connection.cursor()
    insertQuery = 'INSERT INTO auctions (auction_id, currentPrice,
numBids, timestamp, itemTitle) VALUES (%s, %s, %s, "%s", "%s")' %
(auctionID, currentPrice, currentBids, timeStamp, itemTitle)
    cursor.execute(insertQuery)
    connection.commit()

    cursor.close()

    connection.close()

def updateAuction(auctionID, currentPrice, currentBids):

    connection = sqlite.connect(sqliteDatabase)
    cursor = connection.cursor()
    updateQuery = 'UPDATE auctions SET currentPrice = %s, numBids =
%s, timestamp = "%s" WHERE auction_id = %s;' % (currentPrice,
currentBids, timeStamp,auctionID)
    cursor.execute(updateQuery)
    connection.commit()

    cursor.close()
    connection.close()

def getTotalAmount():

    connection = sqlite.connect(sqliteDatabase)
    cursor = connection.cursor()
    selectQuery = "SELECT SUM(currentPrice) FROM  auctions WHERE numBids > 0;"
    cursor.execute(selectQuery)
    for row in cursor:
        return float(row[0])

    cursor.close()

    connection.close()

def displayAuctions(sortBy="timestamp DESC"):

    connection = sqlite.connect(sqliteDatabase)
    cursor = connection.cursor()
    selectQuery =  "SELECT auction_id, numBids, currentPrice,
timestamp, itemTitle FROM  auctions WHERE numBids > 0 ORDER BY " +
sortBy + ";"
    cursor.execute(selectQuery)
    for row in cursor:
        auctionID, numBids, currentPrice, timestamp, itemTitle = row

        print "%25s $%6.2f (%2s bids) Last update: %16s" %
(itemTitle[:25], currentPrice, numBids, timestamp)

    cursor.close()

    print ""

    connection.close()

if __name__ == "__main__":
    displayAuctions("currentPrice DESC")
    displayAuctions()
    #displayAuctions("numBids DESC")
    print "$%3.2f" % getTotalAmount()