[SQLObject] Bind / Prepare Success (20% to 40% faster), Sample Code
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Kevin J. R. <kr...@te...> - 2007-08-31 04:15:19
|
# test multiple selects=0A= # Kevin J. Rice (http://justanyone.com)=0A= =0A= runWith =3D True=0A= lotsOfColumns =3D True=0A= =0A= import site, sys, os=0A= site.addsitedir(os.path.realpath(__file__))=0A= site.addsitedir(os.path.realpath('Lib/external'))=0A= from sqlobject import *=0A= from pprint import pprint, pformat=0A= from sqlobject.sqlbuilder import IN=0A= from sqlobject.col import popKey, pushKey=0A= import sys, traceback=0A= =0A= class SOTextCol(SOStringCol):=0A= def __init__(self, **kw):=0A= popKey(kw, 'length')=0A= popKey(kw, 'varchar')=0A= SOStringCol.__init__(self, **kw)=0A= =0A= class TextCol(Col):=0A= baseClass =3D SOTextCol=0A= =0A= def getTransaction(tableList =3D []):=0A= dsn =3D "host=3D127.0.0.1 dbname=3Ddbrel203 user=3Dkrice = password=3Dchicken4lunch" =0A= con =3D PostgresConnection(dsn, debug=3D0)=0A= for t in tableList:=0A= prepName =3D None=0A= try:=0A= prepName =3D t.sqlmeta.getByIDPrepName=0A= except AttributeError:=0A= print "*" * 80=0A= print "No getByIDPrepName defined."=0A= assert prepName, "Must define a sqlmeta getByIDPrepName for each = table object passed into getTransaction." =0A= dbNames =3D [col.dbName for col in t.sqlmeta.columnList]=0A= prep =3D "prepare %s (int) as select %s from %s where (%s.%s) = =3D $1;" % \=0A= (t.sqlmeta.table + '_getByID', =0A= ', '.join([x for x in dbNames]), =0A= t.sqlmeta.table, =0A= t.sqlmeta.table, =0A= t.sqlmeta.idName)=0A= print "Table: %s, prep=3D%s." % (t.__name__, prep)=0A= result =3D con.query(prep) =0A= print "Result: %s" % result=0A= =0A= return con=0A= =0A= class Org(SQLObject):=0A= class sqlmeta:=0A= idName =3D 'OrgID'=0A= table =3D 'Org'=0A= if runWith:=0A= getByIDPrepName =3D table + '_getByID'=0A= =0A= orgName =3D TextCol(dbName=3D'orgName', default=3DNone)=0A= users =3D MultipleJoin('OrgUser', joinColumn=3D'myOrgID') =0A= =0A= =0A= class OrgUser(SQLObject):=0A= class sqlmeta:=0A= idName =3D 'OrgUserID'=0A= table =3D 'OrgUser'=0A= if runWith:=0A= getByIDPrepName =3D table + '_getByID'=0A= =0A= firstName =3D TextCol(dbName=3D'firstName', default=3DNone)=0A= myOrgID =3D KeyCol(dbName=3D'myOrgID', default=3DNone, = foreignKey=3D"Org") =0A= =0A= if lotsOfColumns:=0A= fieldNum01 =3D StringCol( dbName=3D'fieldNum01', default=3D'')=0A= fieldNum02 =3D StringCol( dbName=3D'fieldNum02', default=3D'')=0A= fieldNum03 =3D StringCol( dbName=3D'fieldNum03', default=3D'')=0A= fieldNum04 =3D StringCol( dbName=3D'fieldNum04', default=3D'')=0A= fieldNum05 =3D StringCol( dbName=3D'fieldNum05', default=3D'')=0A= fieldNum06 =3D StringCol( dbName=3D'fieldNum06', default=3D'')=0A= fieldNum07 =3D StringCol( dbName=3D'fieldNum07', default=3D'')=0A= fieldNum08 =3D StringCol( dbName=3D'fieldNum08', default=3D'')=0A= fieldNum09 =3D StringCol( dbName=3D'fieldNum09', default=3D'')=0A= fieldNum10 =3D StringCol( dbName=3D'fieldNum10', default=3D'')=0A= fieldNum11 =3D StringCol( dbName=3D'fieldNum11', default=3D'')=0A= fieldNum12 =3D StringCol( dbName=3D'fieldNum12', default=3D'')=0A= fieldNum13 =3D StringCol( dbName=3D'fieldNum13', default=3D'')=0A= fieldNum14 =3D StringCol( dbName=3D'fieldNum14', default=3D'')=0A= fieldNum15 =3D StringCol( dbName=3D'fieldNum15', default=3D'')=0A= fieldNum16 =3D StringCol( dbName=3D'fieldNum16', default=3D'')=0A= fieldNum17 =3D StringCol( dbName=3D'fieldNum17', default=3D'')=0A= fieldNum18 =3D StringCol( dbName=3D'fieldNum18', default=3D'')=0A= fieldNum19 =3D StringCol( dbName=3D'fieldNum19', default=3D'')=0A= fieldNum20 =3D StringCol( dbName=3D'fieldNum20', default=3D'')=0A= fieldNum21 =3D StringCol( dbName=3D'fieldNum21', default=3D'')=0A= fieldNum22 =3D StringCol( dbName=3D'fieldNum22', default=3D'')=0A= fieldNum23 =3D StringCol( dbName=3D'fieldNum23', default=3D'')=0A= fieldNum24 =3D StringCol( dbName=3D'fieldNum24', default=3D'')=0A= fieldNum25 =3D StringCol( dbName=3D'fieldNum25', default=3D'')=0A= fieldNum26 =3D StringCol( dbName=3D'fieldNum26', default=3D'')=0A= fieldNum27 =3D StringCol( dbName=3D'fieldNum27', default=3D'')=0A= fieldNum28 =3D StringCol( dbName=3D'fieldNum28', default=3D'')=0A= fieldNum29 =3D StringCol( dbName=3D'fieldNum29', default=3D'')=0A= fieldNum30 =3D StringCol( dbName=3D'fieldNum30', default=3D'')=0A= fieldNum31 =3D StringCol( dbName=3D'fieldNum31', default=3D'')=0A= fieldNum32 =3D StringCol( dbName=3D'fieldNum32', default=3D'')=0A= fieldNum33 =3D StringCol( dbName=3D'fieldNum33', default=3D'')=0A= fieldNum34 =3D StringCol( dbName=3D'fieldNum34', default=3D'')=0A= fieldNum35 =3D StringCol( dbName=3D'fieldNum35', default=3D'')=0A= fieldNum36 =3D StringCol( dbName=3D'fieldNum36', default=3D'')=0A= fieldNum37 =3D StringCol( dbName=3D'fieldNum37', default=3D'')=0A= fieldNum38 =3D StringCol( dbName=3D'fieldNum38', default=3D'')=0A= fieldNum39 =3D StringCol( dbName=3D'fieldNum39', default=3D'')=0A= fieldNum40 =3D StringCol( dbName=3D'fieldNum40', default=3D'')=0A= fieldNum41 =3D StringCol( dbName=3D'fieldNum41', default=3D'')=0A= fieldNum42 =3D StringCol( dbName=3D'fieldNum42', default=3D'')=0A= fieldNum43 =3D StringCol( dbName=3D'fieldNum43', default=3D'')=0A= fieldNum44 =3D StringCol( dbName=3D'fieldNum44', default=3D'')=0A= fieldNum45 =3D StringCol( dbName=3D'fieldNum45', default=3D'')=0A= fieldNum46 =3D StringCol( dbName=3D'fieldNum46', default=3D'')=0A= fieldNum47 =3D StringCol( dbName=3D'fieldNum47', default=3D'')=0A= fieldNum48 =3D StringCol( dbName=3D'fieldNum48', default=3D'')=0A= fieldNum49 =3D StringCol( dbName=3D'fieldNum49', default=3D'')=0A= fieldNum50 =3D StringCol( dbName=3D'fieldNum50', default=3D'')=0A= =0A= =0A= t=3D getTransaction()=0A= try:=0A= Org.dropTable(connection=3Dt)=0A= OrgUser.dropTable(connection=3Dt)=0A= except:=0A= pass=0A= =0A= t=3D getTransaction()=0A= Org.createTable(connection=3Dt)=0A= OrgUser.createTable(connection=3Dt) =0A= =0A= userNameList1 =3D '''bill bob dave ed john steve luke mark john =0A= matt joseph randy andy wendy carrie will michelle rebecca lisa = mary'''.split()=0A= orgNameList1 =3D '''Exxon Mobil Chevron Texaco Phillips66 BP Amoco =0A= Aramco Lukoil Gazprom 3M UBS Warburg IBM JCPenny NYSE CPSE ABC'''.split()=0A= =0A= userNameList =3D range(1, 100)=0A= orgNameList =3D orgNameList1=0A= =0A= if runWith:=0A= t=3D getTransaction(tableList=3D[Org, OrgUser])=0A= else:=0A= t=3D getTransaction()=0A= =0A= #x =3D dieNow()=0A= i =3D 0 =0A= for oname in orgNameList:=0A= newOrg =3D Org(orgName=3Doname, connection=3Dt)=0A= for x in range(1, 150):=0A= OrgUser(firstName=3D"Name_%s" % x, myOrgID=3DnewOrg.id, = connection=3Dt)=0A= =0A= if runWith:=0A= t=3D getTransaction(tableList=3D[Org, OrgUser])=0A= else:=0A= t=3D getTransaction()=0A= =0A= from mx.DateTime import now, Date=0A= start =3D now()=0A= l =3D 0=0A= print "starting at: ", start=0A= for i in range(1, 30):=0A= #print "*" * 300=0A= if i % 10 =3D=3D 0:=0A= print "i=3D%s" %i=0A= for thisOrg in Org.select(IN(Org.q.orgName, orgNameList), = connection=3Dt):=0A= string =3D "Have Org: %s, userslist=3D%s." % (thisOrg.orgName, \=0A= pformat([x.firstName for x in thisOrg.users]))=0A= l +=3D len(string)=0A= end =3D now()=0A= print "ending at : %s elapsed =3D %s, l=3D%s" % (end, end - start, l)=0A= # SQL statement for the same thing: =0A= # select firstname, orgName from Org, OrgUser where Org.orgID =3D = OrgUser.myOrgID;=0A= |