Thread: [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= |
From: Oleg B. <ph...@ph...> - 2007-09-05 10:25:10
|
Hello. On Thu, Aug 30, 2007 at 10:52:38PM -0500, Kevin J. Rice wrote: > if not selectResults and self.sqlmeta._perConnection and prepName: > statement = "execute %s (%s)" % (prepName, self.id) > selectResults = self._connection.queryOne(statement) > > class Org(SQLObject): > class sqlmeta: > idName = 'OrgID' > table = 'Org' > getByIDPrepName = table + '_getByID' > > def getTransaction(tableList = []): > dsn = "host=127.0.0.1 dbname=dbrel203 user=krice password=chicken4lunch" > > con = PostgresConnection(dsn, debug=0) > for t in tableList: > prepName = None > try: > prepName = t.sqlmeta.getByIDPrepName > except AttributeError: > print "No getByIDPrepName defined." > assert prepName, "Must define a sqlmeta getByIDPrepName for each > table object passed into getTransaction." > dbNames = [col.dbName for col in t.sqlmeta.columnList] > prep = "prepare %s (int) as select %s from %s where (%s.%s) = $1;" % > \ > (t.sqlmeta.table + '_getByID', > ', '.join([x for x in dbNames]), > t.sqlmeta.table, > t.sqlmeta.table, > t.sqlmeta.idName) Now you need to generalize it: automatically generate getByIDPrepName, PREPARE and EXECUTE statement, so that a user would need only to call an existing API, not to create statements by hand every time (s)he needs it. And of course the new API must not cause troubles for backends that don't support PREPARE/EXECUTE. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |