pysql.py

python web programming 部分

from dtuple import TupleDescriptor, DatabaseTuple
DT = TupleDescriptor((['FieldName'],['Description'],['Type'],['Size'],['Options']))
import string
import mx.DateTime
import pyLib

def SQLInsert(Form, TableName, Fields, iFlds, iVals, Prefix):
# Build SQL statement to add a database row
	PfLen = len(Prefix)
	ts = mx.DateTime.now().strftime("%m/%d/%y %H:%M:%S")
	if iFlds == "":
		Nm = "CrDT"
		Vl= "'%s'" % ts
	else:
		Nm = iFlds + ", CrDT"
		Vl = iVals + ", '" + ts + "'"
	for fld in Fields:
		f = DatabaseTuple(DT, fld)
		if f.Type not in ("REM", "KSS", "KNS"):
			if f.Size >= PfLen:
				if f.FieldName[:PfLen] == Prefix:	# Only required attributes
					Nm = Nm + ", " + f.FieldName
					if f.Type in ("T","P","M","KSA","HS"):	# Text Fields
						Vl = Vl + ", '" + pyLib.SQLesc(str(Form[f.FieldName].value)) + "'"
					elif f.Type == "Y":						# Y/N Fields
						# Note: "ON" & "OFF" may be Jet-dependent
						if ucase(str(Form[f.FieldName].value)) == "ON":
							v = ", 1"
						else:
							v = ", 0"
						vl = vl + v
					elif f.Type in ("N","KNA","HN"):		# Numeric fields
						Vl = Vl + ", " + str(Form[f.FieldName].value)
					else:
						vl = vl + ",NULL" # Perhaps a copout?
	return "INSERT INTO %s (%s) VALUES (%s)" % (TableName, Nm, Vl)

def SQLUpdate(Form, TableName, KeyNames, KeyVals, Fields, Prefix):
# Build SQL statement to update a database row
	PfLen = len(Prefix)
	KeyName=""
	ts = mx.DateTime.now().strftime("%m/%d/%y %H:%M:%S")
	Vl="MoDT=" + "'" + ts + "'"
	for fld in Fields:
		f = DatabaseTuple(DT, fld)
		# formval = Form[f.FieldName].value
		if f.Type[0] == "K":
			KeyName = f.FieldName
		if f.Type != "REM" and f.Type[0] != "K":
			if len(f.FieldName) >= PfLen:
				if f.FieldName[:PfLen] == Prefix:	# Only required attributes
					Vl = Vl + ", " + f.FieldName + "="
					if f.Type in ("T","P","M","KSA","HS"):	# Text Fields
						Vl = Vl + "'" + pyLib.SQLesc(Form[f.FieldName].value) + "'"
					elif f.Type in ("N","KNA","HN"):		# Numeric fields
						x = str(Form[f.FieldName].value)
						if x == "":
							x = "NULL"
						Vl = Vl + x
					elif f.Type == "Y":						# Y/N Fields
						# Note: "ON" & "OFF" may be Jet-dependent
						if string.ucase(str(Form[f.FieldName].value)) == "ON":
							v = "1"
						else:
							v="0"
						vl = vl + v
					else:
						vl = vl + "NULL" # Perhaps a copout?
	r = "UPDATE %s SET %s %s" % (TableName, Vl, SQLWhere(KeyNames, KeyVals))
	return r

def SQLDelete(Table,KeyNames,KeyVals):
	return "DELETE FROM " + Table + SQLWhere(KeyNames,KeyVals)

def SQLWhere(KeyNames,KeyVals):
	V = ""
	Prefix=" WHERE "
	for k in range(len(KeyNames)):
		if KeyNames[k][1] == "S":
			kv = "'" + KeyVals[k] + "'"
		else:
			kv = KeyVals[k]
		V = V + Prefix + KeyNames[k][0] + "=" + kv
		Prefix=" AND "
	return V