python数据库操作实例_python数据库操作实例代码

学习python的数据库操作，之前需要安装SQLite和APSW，功能挺简单的，以后慢慢完善。

首先，创建一个cookbook.db3的数据库。

例子，python数据库操作的代码。 代码示例:

#!/usr/bin/env python

#

#site: www.#

import apsw

import string

import webbrowser

class Cookbook:

def __init__(self):

global connection

global cursor

self.totalcount = 0

connection = apsw.Connection("cookbook.db3")

print connection

cursor = connection.cursor()

print cursor

def PrintAllRecipes(self):

print '%s %s %s %s' %('ITEM'.ljust(5),'Name'.ljust(30),'Serves'.ljust(20),'Source'.ljust(30))

print '-------------------------------------------------'

sql = 'SELECT * FROM Recipes'

cntr = 0

for x in cursor.execute(sql):

cntr +=1

print '%s %s %s %s' %(str(x[0]).rjust(5),x[1].ljust(30),x[2].ljust(20),x[3].ljust(30))

print '----------------------------------------------'

self.totalcount = cntr

def SearchForRecipe(self):

print '--------------------------------------------------'

print ' Search in'

print '--------------------------------------------------'

print ' 1 - Recipe Name'

print ' 2 - Recipe Source'

print ' 3 - Ingredients'

print ' 0 - Exit'

searchin = raw_input('Enter Search Type -> ')

if searchin != '0':

if searchin == '1':

search = 'Recipe Name'

elif searchin == '2':

search = 'Recipe Source'

elif searchin == '3':

search = 'Ingredients'

parm = searchin

response = raw_input('Search for what in %s (blank to exit) -> ' % search)

if parm == '1':

sql = "SELECT pkID,name,source,serves FROM Recipes WHERE name like '%%%s%%'" %response

elif parm == '2':

sql = "SELECT pkID,name,source,serves FROM Recipes WHERE source like '%%%s%%'" %response

elif parm == '3':

sql = "SELECT r.pkID, r.name, r.serves, r.source, i.ingredients FROM Recipes r LEFT Join ingredients i on (r.pkID = i.recipeID) WHERE i.ingredients like '%%%s%%' GROUP BY r.pkID" %response

try:

if parm == '3':

print '%s %s %s %s %s ' %('Item'.ljust(5),'Name'.ljust(30),'serves'.ljust(20),'source'.ljust(30),'Ingredient'.ljust(30))

print '---------------------------------------'

else:

print '%s %s %s %s ' %('Item'.ljust(5),'Name'.ljust(30),'serves'.ljust(20),'source'.ljust(30))

print '---------------------------------------'

for x in cursor.execute(sql):

if parm == '3':

print '%s %s %s %s %s ' %(str(x[0]).rjust(5), x[1].ljust(30), x[2].ljust(20),x[3].ljust(30),x[4].ljust(30))

else:

print '%s %s %s %s ' %(str(x[0]).rjust(5),x[1].ljust(30),x[3].ljust(20),x[2].ljust(30))

except:

print 'an error occured'

print '---------------------------------------------------'

inkey = raw_input('Press a key')

def PrintSingleRecipe(self,which):

sql = 'SELECT * FROM Recipes WHERE pkID = %s' % str(which)

print '---------------------------------------------------'

for x in cursor.execute(sql):

recipeid = x[0]

print "Title: " + x[1]

print "Serves: " + x[2]

print "Source: " + x[3]

print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

sql = 'SELECT * FROM Ingredients WHERE RecipeID = %s' % recipeid

print 'Ingredient List:'

for x in cursor.execute(sql):

print x[1]

print ''

print 'Instructions:'

sql = 'SELECT * FROM Instructions WHERE RecipeID = %s' % recipeid

for x in cursor.execute(sql):

print x[1]

print '----------------------------------------------'

resp = raw_input('Press a key ->')

def DeleteRecipe(self,which):

resp = raw_input('Are You SURE you want to Delete this record? (Y/N) -> ')

if string.upper(resp) == 'Y':

sql = "DELETE FROM Recipes WHERE pkID = %s" % str(which)

cursor.execute(sql)

sql = "DELETE FROM Instructions WHERE recipeID = %s" % str(which)

cursor.execute(sql)

sql = "DELETE FROM Ingredients WHERE recipeID = %s" % str(which)

cursor.execute(sql)

print "Recipe information DELETED"

resp = raw_input('Press A Key -> ')

else :

print "Delete Aborted - Returning to menu"

def EnterNew(self):

ings = []

recipename = ''

recipesource = ''

recipeserves = ''

instructions = ''

lastid = 0

resp = raw_input('Enter Recipe Title (Blank line to exit) -> ')

if resp != '' :

if string.find(resp,"'"):

recipename = resp.replace("'","'")

else:

recipename = resp

print "RecipeName will be %s" %recipename

resp = raw_input('Enter Recipe Source -> ')

if string.find(resp,"'"):

recipesource = resp.replace("'","'")

else:

recipesource = resp

resp = raw_input('Enter number of servings -> ')

if string.find(resp,"'"):

recipeserves = resp.replace("'","'")

else:

recipeserves = resp

print 'Now we will enter the ingredient list.'

cont = True

while cont == True:

ing = raw_input('Enter Ingredient ("0" to exit) -> ')

if ing != '0':

ings.append(ing)

else:

cont = False

resp = raw_input('Enter Instructions -> ')

instructions = resp

print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

print "Here's what we have so far"

print "Title: %s" % recipename

print "Source: %s" % recipesource

print "Serves: %s" % recipeserves

print "Ingredients:"

for x in ings:

print x

print "Instructions: %s" % instructions

print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

resp = raw_input("OK to save? (Y/N) -> ")

if string.upper(resp) != 'N':

connection = apsw.Connection("cookbook.db3")

cursor = connection.cursor()

sql = 'INSERT INTO Recipes (name,serves,source) VALUES("%s","%s","%s")' %(recipename, recipeserves,recipesource)

cursor.execute(sql)

print recipename

print recipeserves

print recipesource

sql = "SELECT last_insert_rowid()"

cursor.execute(sql)

for x in cursor.execute(sql):

lastid = x[0]

print "last id = %s" %lastid

for x in ings:

sql = 'INSERT INTO Ingredients (recipeID,ingredients)VALUES (%s ,"%s")' %(lastid , x)

print x

cursor.execute(sql)

print "ingredients"

sql = 'INSERT INTO Instructions(recipeID,instructions) VALUES(%s,"%s")' %(lastid,instructions)

cursor.execute(sql)

print "instructions"

print "Done"

else:

print 'Save aborted'

def PrintOut(self,which):

fi = open('recipeprint.html', 'w')

sql = "SELECT * FROM Recipes WHERE pkID = %s" % which

for x in cursor.execute(sql):

RecipeName = x[1]

RecipeSource = x[3]

RecipeServings = x[2]

fi.write("

%s " % RecipeName)

fi.write("

Source: %s " % RecipeSource )

fi.write("

Servings: %s " % RecipeServings)

fi.write("

Ingredient List: ")

sql = 'SELECT * FROM Ingredients WHERE RecipeID = %s' % which

for x in cursor.execute(sql):

fi.write("

%s

" % x[1])

fi.write("

Instructions: ")

sql = 'SELECT * FROM Instructions WHERE RecipeID = %s ' % which

for x in cursor.execute(sql):

fi.write(x[1])

fi.close()

webbrowser.open('recipeprint.html')

print "Done"

def Menu():

cbk = Cookbook()

loop = True

while loop == True:

print '===================================================='

print '                                     RECIPE DATABASE'

print '===================================================='

print ' 1 - show all recipes'

print ' 2 - search for a recipe'

print ' 3 - show a recipe'

print ' 4 - delete a recipe'

print ' 5 - add a recipe'

print ' 6 - print a recipe'

print ' 0 - exit'

print '===================================================='

response = raw_input('enter a selection ->')

if response == '1':

cbk.PrintAllRecipes()

elif response == '2':

cbk.SearchForRecipe()

elif response == '3':

try:

res = int(raw_input('Select a Recipe -> '))

if res <= cbk.totalcount:

cbk.PrintSingleRecipe(res)

elif res == cbk.totalcount + 1:

print 'Back To Menu...'

else :

print 'Unrecognized command. Returning to menu.'

except ValueError:

print 'Not a number...back to menu.'

elif response == '4':

cbk.PrintAllRecipes()

print '0 - Return To Menu'

try:

res = int(raw_input('Select a Recipe to DELETE or 0 to exit -> '))

if res != 0:

cbk.DeleteRecipe(res)

elif res == '0':

print 'Back To Menu...'

else:

print 'Unrecognized command. Returning to menu.'

except ValueError:

print 'Not a number...back to menu.'

elif response == '5':

cbk.EnterNew()

elif response == '6':

cbk.PrintAllRecipes()

print '0 - Return To Menu'

try:

res = int(raw_input('Select a Recipe to PRINT or 0 to exit -> '))

if res != 0:

cbk.PrintOut(res)

elif res == '0':

print 'Back To Menu...'

else:

print 'Unrecoginzed command. Returning to menu.'

except ValueError:

print 'Not a number...back to menu.'

elif response == '0':

print 'goodbye'

loop = False

else:

print 'unrecognized command. try again'

Menu()